How To Add Custom Filters In Google Sheets (Easy!)

Are you sick of scrolling through endless rows within your Google Sheets spreadsheet to find the data you actually need?

Well, now you don’t need to! 

Thanks to the built-in Google Sheets custom filters feature, you can easily sort, isolate, and highlight the data you’re looking for in just a few clicks.

These custom filters are the secret Google Sheets weapon you didn’t know you needed.

To add custom filters in Google Sheets:

1. Click and drag to select the cells to filter (to select all cells, click the blank square where the row numbers and column letters meet at the top of the sheet).
2. Select ‘Data’ in the top menu, then ‘Create a filter’ from the drop-down list, or select the ‘Create a filter’ icon in the formatting bar at the top of your sheet.
3. Click the three little lines on the right-hand side of highlighted column headers.
4. From the drop-down menu, select the filter type (color, conditions, values) and choose a rule that fits your data.
5. For example, if you were working with dates, select ‘Filter by condition’ and then select ‘Date is after, ‘Date is,’ or ‘Date is before’ and type in the relevant date.
6. Select ‘OK’.
7. Google Sheets will then apply the relevant filter only to the applied cells.
8. And that’s seriously all it takes! You’ve just created a custom filter in Google Sheets to make your life easier.
9. Adding a custom filter is a game-changer for those working with big data sets. Trust me, you’ll thank me later!

Check out my in-depth video guide on how to create custom filters inside my Sheetity business toolkits here.

In Google Sheets, the ‘Custom filter’ feature essentially only displays rows that meet specific criteria, whether it’s certain words, numbers, dates, or values.

Think of it as turning the spotlight on exactly what you need, without the unwanted noise of other data.

For example, let’s say you’ve got a year’s worth of sales data, but you only want to focus on last quarter’s numbers.

Instead of deleting or sorting everything manually, a custom filter lets you zero in on just that slice of data.

And the best part is that you don’t need to change or lose anything within your original sheet, and they don’t require any advanced spreadsheet skills or formula knowledge to set up!

Custom filters are especially helpful when working with large or cluttered spreadsheets as they keep your data clean, insights clear, and workflow efficient.

Now that you know how to add basic custom filters to your spreadsheet, you might be wondering…is it possible to add advanced filters?

And the answer is yes!

Basically, if you need to get super specific with your data, you can apply more than one filter rule at a time. 

Adding advanced custom filters to your data is helpful for businesses with larger datasets or those looking to dig into more specific insights.

For example, let’s say you're looking at a list of customer orders and want to find those from Australia and over $200.

Here’s how you can do it:

1. Click the ‘Filter’ icon within the column you want to filter.
2. Choose ‘Filter by condition’ and set your first rule, like ‘Country is exactly Australia.’
3. Click ‘Add another condition’ and add your second rule, such as ‘Price is greater than 200.’
4. Select ‘OK’ to apply the rule.

And that’s it! Now your sheet will only show orders from Australia that are over $200.

Additionally, if you’ve color-coded your data, you can filter by colors to visually organize your workflow and see what matters most at a glance.

To do this:

1. Click the ‘Filter’ icon.
2. Select ‘Filter by color’.
3. Choose ‘Fill color’ or ‘Text color’.

With these advanced filters, you’re not just organizing your data; you’re transforming your spreadsheet into a powerful, customized, and visual dashboard.

Another way to add a custom filter in Google Sheets is to activate a Filter view.

This essentially sorts or filters your spreadsheet on your terms without disrupting what your teammates see. 

The filter view is ideal for collaboration, quick sorting, or setting up saved filters, which you can always return to anytime.

Here’s how you can add a filter view to your Google Sheets spreadsheet:

Ensure your column has a clear header and your data is appropriately formatted.

Select the entire range of cells you want to work with.

Click ‘Data > Filter views > Create new filter view’ in the top menu.

You should see a green border around your data, which means you’re in your own filter zone (and safe from disturbing anyone else’s work).

Find the column you want to focus on and click the filter icon next to the header.

You can then choose to:

• Sort A-Z (Alphabetize your data in ascending order).
• Sort Z-A (Reverse alphabetize Z to A)
• Sort by fill color (Organize rows by background color).
• Sort by text color (Prioritize colored text if you've used formatting to mark important info).
• Filter by condition (Apply rules like ‘Text contains,’ ‘Date is before,’ or choose a ‘Custom formula’.
• Filter by values (Manually check/uncheck values to hide or show specific entries).
  

Basically, regular filters are a quick and straightforward way to change what everyone sees within your Google Sheets spreadsheet.

Filter views, on the other hand, is essentially a private workspace within your spreadsheet.

Filter views can create and save multiple filters, name and organize filter views, and let collaborators view or create their own filters without affecting yours.

Additionally, you can share specific filter views with others and sort data temporarily, even on sheets you can’t edit!

I suggest using a filter view instead of a custom filter for those sharing a sheet with others.

Are you having trouble adding custom filters to your Google Sheets spreadsheet?

Before you start filtering your data, you must ensure your Google Sheets spreadsheet is clean and consistent. Otherwise, your data won’t filter properly!

Here’s your quick pre-filter checklist:

1. Formatting: Whether it’s dates, numbers, or text, keep your formatting consistent across each column, as just one rogue cell can break the filter!
2. Clear headers: Make sure every column has a header. This helps Google Sheets know what’s what and keeps your filters aligned.
3. No duplicates: Remove any duplicate entries to avoid confusing overlaps.
4. Get rid of empty cells: Empty cells can also confuse filters! Either fill them or remove unnecessary rows to keep your filters working properly.

Still having issues? Here are some common filter errors and how you can fix them:

•  Issue 1: Filter isn’t working as expected.

Check your rules are correct (one wrong character, date format, or filter logic can throw everything off!).

Review the range: Make sure you filter the full dataset instead of a portion.

• Issue 2: Filter icon isn’t there.

Try to activate it again: Click ‘Data > Create a filter’ to bring the filter icon back.

Check access with the sheet creator: If you’re using a shared sheet, you might not have permission to use filters!

• Issue 3: Filter view won’t save.

Make sure your filter view doesn’t clash with an existing one.

Try to refresh: Reload the page to fix any glitches within your tab that might mess with your view.

Basically, the more time you spend organizing your data upfront, the fewer headaches you'll face when filtering!

Read more: How to find duplicates in Google Sheets?

Adding custom filters in Google Sheets gives you the power to focus on exactly the data you need without deleting, moving, or permanently altering anything.

While custom filters can be used for a handful of entries, the feature is particularly useful for teams dealing with hundreds of rows.

This is because custom filters can cut through the noise by only showing relevant information (like dates, names, prices, or statuses).

Additionally, applying these filters will save you time and help spot trends or issues faster by identifying specific data.

And the best part? Filters won’t change or delete your data! They just hide what you don’t need right now.

Custom filters essentially help you turn cluttered data into clear insights in just a few clicks!

Thanks for reading!