How to filter in Google Sheets by condition

by Alexander Trifuntov, updated on May 20, 2024

When analyzing data in Google Sheets, it's helpful to focus on specific parts of your dataset. Whether you're interested in certain values, colors, or dates, Google Sheets filters can assist you. In this article, I’ll guide you on how to set up conditions correctly while making sure you don't affect other users in shared spreadsheets, and share a few unique tips you won’t find elsewhere.

Google Sheets filter is a standard tool that lets you hide a part of your data that you don't need at the moment. This way you don't need to delete the data. You simply hide it temporarily and can display it back any time.

How to create a filter in Google Sheets
Build Google Sheets filter based on cell value
How to filter by color in Google Sheets
- Filter by one color
- Filter by multiple colors
Filter by condition in Google Sheets
Can Google Sheets filter by multiple conditions?
- Video: filter by multiple conditions, easy way
Can Google Sheets filter multiple columns?
How to turn off filter in spreadsheets
How to filter without affecting other users — Google Sheets filter views

How to create a filter in Google Sheets

1. Select the range

For Google Sheets to filter your whole table, just click a single cell within it and proceed to step 2 — turning the filter on. This will work like Ctrl+A: your entire used range (all cells with data before the first blank row & column) will be selected automatically.

But to filter only a certain part of your table, or to include completely blank rows & columns in your selection, you must first specify the range of interest. You can do that in one of the following ways:

Select entire columns (or rows). (If you're not sure what I mean, I explained how to do that in one of my previous articles.)
Select the range manually.

2. Turn Google Sheets filter on

Then either go to Data > Create a filter in the Google Sheets menu: Enable Google spreadsheet filter using the menu.

Or simply click the corresponding filter icon in Google Sheets toolbar:

Tip. I suggest you freeze the first row of your table: headers with filter buttons. This way you won't have to scroll all the way up to adjust filter settings.

Once you do this, each column header will have this kind of an upside-down pyramid icon: You can filter your table now.

It means you created a filter in Google Sheets successfully and are ready to play with it.

Build Google Sheets filter based on cell value

The most basic filter you can do is by value:

Pick the column you'd like to filter.
Click that filter icon next to its header.
Uncheck all those values you want to hide temporarily (click on each so checkmarks disappear):
Confirm with the OK button.

You'll see how values without checkmarks instantly disappear from the screen. They are still there, in your table, you just don't see them displayed anymore ?

You'll also notice that the filter icon has changed next to this column header: Different icon and missing row numbers indicate the active filter.

Filter Google Sheets by values for multiple columns

Similarly, Google Sheets lets you filter multiple columns based on cell values.

Let me show you and create an additional filter for the Product column. I'll keep only the info for Dark and Extra Dark Chocolate: How Google Sheets filters multiple columns by values.

As a result, there are even fewer displayed rows than before. The remaining ones meet all requirements from both columns: Filter icons next to column headers indicate that the filter is active for two columns.

How to filter by color in Google Sheets

If you use different font and fill colors to present all data in the best way possible, there's one invaluable option for you in Google Sheets — filter by color.

For example, I set up conditional formatting in my list of orders:

If the number of items is less than 50, the number (its font color) turns red.
If greater than or equal to 100, the fill color becomes green.

Let's try and filter this table by color in column E.

Filter by one color

I'll start with the standard Google Sheets tool that lets you filter by one fill or font color at a time.

Example 1. Filter by text color in Google Sheets

To filter data by font color and keep only red numbers, do this:

Create a filter.
Click the filter icon on the column of interest and pick the color you want to keep visible in your table. Other rows will be hidden: Filter by color > Text color > red:

As you can see, this filter by color fetches only those Google Sheets rows where numbers are red: Only numbers in red remain visible in the Qty. column.

Example 2. Filter by fill color in Google Sheets

Another way would be to filter by fill color in Google Sheets.

Let's keep only rows with the green background color. This time, select the following from the menu: Filter by color > Fill color > light green 3: Filter Google Sheets by light green fill color.

Now only rows where Qty cells are colored green remain: Have only rows with the light green fill color visible.

Filter by multiple colors in Google Sheets

As you may already know, the standard Google Sheets Filter by color tool has its limits. It doesn't allow filtering by both fill & text color, nor does it support filtering by several font or fill hues.

Luckily, our Function by Color add-on for Google Sheets fills this gap ?

Onboard this tool, you will find CELLCOLOR function. Paired with Google Sheets FILTER function and its ability to work with the OR logic, it will let you filter by multiple font colors, fill colors, and even combos of fill and font colors together.

Intrigued? ?

Learn about this possibility in another article devoted to the FILTER function.

Filter by condition in Google Sheets

Much like filtering by value or color, filter by condition lets you sift through your data — only with precision. You no longer need to manually tick off every entry from a list. Instead, you specify criteria such as text containing, starting with, or exactly matching certain phrases. Additionally, you can filter by dates, blanks, or numbers.

For starters, enable the filter.
Then click the filter icon on the column you want to work with.
Pick Filter by condition.
Click the None field.

You'll see all conditions available to filter in Google Sheets: See all filter conditions available in Google Sheets.

Filter blanks in Google Sheets

The first two options in the drop-down will show you all rows where cells in a certain column are missing data (Is empty): Filter blanks in the Region column in Google Sheets.

or, on the contrary, hide such rows from the display (Is not empty): Hide blanks and show only rows with data.

Tip. If blanks appear in your filter when they shouldn't, make sure these cells don't contain spaces ( ), empty strings (=""), or even text wrapped to the bottom after the line break ().

Filter by text: Partial matches

Example 1. Text contains

This option will filter those Google Sheets rows where cells contain specific numbers, parts of the words, etc.

You can even use special wildcard characters to create masks and look for some specific symbols in different positions within cells:

asterisk (*) is used for any amount of characters
a question mark (?) replaces a single symbol

Hence, you can get the same result by entering various wildcard char combos: Dark, D*k, D??k Same result, 3 different filtering options with wildcard characters.

Tip. If you're into formulas and regular expressions rather than simple patterns, here's a custom formula to filter by this condition in Google Sheets:

=REGEXMATCH(D:D,"Dark")

Example 2. Text does not contain

I believe you've already guessed that the conditions here can be the same as in Text contains above.

Yet, the result will be the opposite. The value you enter will be hidden from the view by this Google Sheets filter.

Tip. As for the custom formula to filter by this condition in Google Sheets, it will require regex. It will look as follows:

=REGEXMATCH(D:D,"Dark")=FALSE

Example 3. Text starts with

For this condition, enter the first character(s) (one or more) of the value of interest, e.g. Milk: Filter all Products that start with the word 'Milk'.

Note. Wildcard characters don't work with Text starts with.

Example 4. Text ends with

Alternatively, enter the last characters of the text you want to filter and fetch in Google Sheets.

Note. Wildcard characters are not allowed for Text ends with.

Find exact text matches

This one is similar to a filter based on cell value. Only instead of ticking off each value from the list, you directly enter what you want to see. White Chocolate, for example.

Note. Entries that contain something other than that won't be displayed. Thus, you can't use wildcard characters here.

Tip. If you want to filter your Google Sheets using a custom formula and look for all records that contain only White Chocolate, use the following:

=D:D="White Chocolate"

How to filter by date in Google Sheets

Google Sheets knows how to filter your tables by dates. There are three conditions based on which your spreadsheet fetches rows with:

exact date
date before the exact date
date after the exact date

Some other periods of time are available to ease your job of setting up the filter, such as today, tomorrow, yesterday, in the past week, in the past month, in the past year. Filter by date in Google Sheets when it's before another exact date.

Note. Make sure to enter dates in the format required by your spreadsheet regional settings rather than the looks in the table. You will learn more about date formats in this article.

Google Sheets filter with numbers

Your Google Sheets can filter numbers by the next conditions:

greater than
greater than or equal to
less than
less than or equal to
is equal to
is not equal to
is between
is not between

The last two (is (not) between) require two different numbers to indicate the starting and ending points of the desired interval.

For instance, let's fetch rows where Qty. numbers in column E are greater than or equal to 100. But this time, let's put our criteria value to G1 and use the cell reference as the condition:

=$G$1 Show only values that are 100 or greater.

Note. If you change the number you refer to (100 in my case), the displayed range won't update automatically. Click the Filter icon on your Google Sheets column and then OK to trigger the update.

Tip. The custom formula can be used for this kind of filter as well:

=E:E>$G$1

Custom formulas to filter by condition in Google Sheets

Each Google Sheets filter criteria I've mentioned so far can be replaced by custom formulas that will fetch the same result. (You will find formula examples in the corresponding article sections above.)

Yet, I believe formulas are a last resort: for when the condition is too complex for the default Google Sheets filter tool. Like Google Sheets filter by multiple conditions in one column (OR logic). For example, want to see all Milk and Dark goods? Use this custom formula:

=OR(REGEXMATCH(D:D,"Dark"),REGEXMATCH(D:D,"Milk")) Set up Google Sheets filter by multiple conditions using a custom formula.

This is not the most flexible filter though I believe. If you're interested in formulas, I advise you to check out Google Sheets FILTER function capable of so much more.

Or share your case in the comments section and we'll figure out a custom formula to filter just for you ;)

Can Google Sheets filter by multiple conditions?

If you mean in the same column — yes, Google Sheets can filter by multiple conditions but only using the Custom formula option from Filter by condition.

Such operators as AND and OR will help you list several criteria, just like in the example above.

If this is not enough or too inconvenient, there’s a better solution — a special add-on for Google Sheets called Filter and Extract Data.

It filters multiple rows and columns, each with lots of criteria applied.

Why try it? Well, the extension is user-friendly, so you won’t have to doubt your own actions. The tool won’t alter your source data at all — it will copy and paste the filtered range wherever you decide.

Tip. Feel free to jump below to the video about the tool right away.

Once you install the add-on, you will find it under the Extensions tab in Google Sheets: Spreadsheet menu where you start the extensions.

The first step you’ll see is the only one there is: Set up multiple conditions in the Filter and Extract add-on and see the preview right away.

I'll use the add-on to filter my Google Sheets table of sales: A1:F69
The columns I want to fetch are Date, Region, Product, and Total Sales.
Now it’s time to compose multiple conditions. I'll try and get all sales of milk and hazelnut chocolate for September 2024.
While you thread your criteria, the formula from the preview area at the bottom of the tool will modify itself accordingly. Click Preview result to peep at the found matches.
Select the upper leftmost cells to extract all matches there.
And hit either Paste result (to return all matches as values) or Insert formula (to insert a formula with its outcome):

If you'd like to get to know Filter and Extract Data better, install it for your Google Sheets:

Make sure to check out its help page to learn more about its possibilities.

Video: filter by multiple conditions, easy way

Filter and Extract Data is the best and the easiest way there is to filter your data in Google Sheets. Watch this demo video to learn all the benefits of owning the tool:

Can Google Sheets filter multiple columns?

Yes, Google Sheets can filter multiple columns simultaneously.

Whether it's by values or by condition, you just need to enable the criteria for each required column. For instance, filter all sales of:

all kinds of Dark chocolate: Text contains Dark
sold before October 1, 2024: Date is before exact date — 1/10/2024
with total sales of $200 and more: Greater than or equal to 200

Or use the add-on from just above to ease things out.

How to turn off filter in spreadsheets

Turn off filter by color

To remove any filtering by color, simply go to Filter by color > None, and all formerly filtered rows will appear anew: Turn off filtering by color In Google Sheets.

Turn off filter from specific columns

To reset the filter on a specific column, click the filter icon and:

if you're using Filter by values, hit Select all
if you're using Filter by condition, click the drop-down with the selected criteria and pick None

Then click the green button at the bottom of the menu saying OK.

Turn off filter completely

To turn off the filter completely and display all the rows again, go to the Data menu and pick Remove filter: Remove filter using the Google Sheets menu.

How to filter without affecting other users — Google Sheets filter views

One of the main advantages of Google Sheets is the possibility for several people to work with tables simultaneously.

And I bet you know how tweaking filters in Google Sheets can sometimes cause confusion or disrupt your team's workflow. It's like trying to find your own space in a busy café by rearranging furniture. But what if you could carve out your own cozy corner without disturbing anyone else?

That's where Google Sheets filter views come in! They're like your own private booth, letting you customize your view without bothering anyone else, and save it for later use even! Different collaborators can create their views in the same spreadsheet without interfering with each other.

So how do you filter without affecting other users exactly?

Option 1. Creating new filter views in Google Sheets

To create a Google Sheets filter view, click Data > Filter views > Create new filter view. Create filter view in spreadsheets.

Then filter your data by any required criteria and hit Save view: Filter your Google Sheets while the filter view is created and save for later use.

Name your filter view to easily recognize it among other views and click Save: Enter the name for your temporary filter view to save it.

Option 2. Saving existing filter as filter view

If you already have a particular filter set up in your Google Sheets table (as I do), go to Data > Save as filter view: Save data as filter view from the spreadsheet menu.

Then enter its name and click Save: A special pop-up window to enter the name of your future filter view.

An additional bar will appear above the rows with the following:

Your filter view name. You can quickly rename it by clicking on the current name and entering a new one.
The range in use, which you can update in the same way as the name.
Options icon also letting you update the range, rename the view, duplicate or delete it.
Exit icon to save & close any Google Sheets filter view.

You can switch between saved filters for this sheet via the spreadsheet menu: Data > Change view: My saved and enabled filter views in the Change view menu.

In both cases, all changes are saved automatically when you close the view.

But if you no longer need the views, remove them separately or all together via the spreadsheet menu: Data > View options > Delete view / Delete all views in tab: Options to delete the active or all views reside under the Data tab.

Note. All users with editing access to the file (including the spreadsheet owner) can see, use and delete all filter views created in this Google Sheets (yes, even the ones created by other users).

If you can only view the spreadsheet, you'll be able to create and apply filter views for yourself, but nothing will be saved upon closing the file. For that, you need permission to edit the spreadsheet.

That's how you filter Google Sheets by condition and work with filter views ? If you have any questions or you'd like to share some thoughts — let's discuss them in the comments section!

You may also be interested in

Products
Windows
Ultimate Suite for Excel Shared Email Templates for Outlook Mail Merge for Outlook
Mac Os
Shared Email Templates for Outlook Mail Merge for Outlook
Google Workspace
Add-ons for Google Sheets Add-ons for Gmail
Ultimate Suite
70+ professional tools for Microsoft Excel

This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays.

Merge multiple worksheets

Compare sheets for differences

Remove duplicates

Merge cells in Excel

Calculate dates and times

Combine sheets by column headers

Trim extra spaces

Count characters and words

Spell number

See all tools

Works for: Windows
Read more about Ultimate Suite
Shared Email Templates
Reply to routine emails faster than ever

Do not waste your time on composing repetitive emails from scratch in a tedious keystroke-by-keystroke way. Type your response just once, save it as a template and reuse whenever you want.

Quick replies for routine emails

Fully customizable templates

Gorgeous html-based designs

Dynamic templates with fillable fields

Comfortable template sharing

Custom-tailored bulk mailings

Company and team-wide signatures

Easy user and team management

Works for: Windows•macOS•Outlook Online
Read more about Shared Email Templates
Mail Merge
Ideal for newsletters, proposals, and greetings addressed to your personal contacts

Mail Merge is a time-saving approach to organizing your personal email events. It offers:

Customizable and adaptive mail merge templates

Personalized merge fields depending on the recipient or context

Dynamic attachments and images

Mailing lists based on Excel tables

"Send immediately" and "send later" scheduling

Works for: Windows•macOS•Outlook Online
Read more about Mail Merge
40+ tools to boost your Google Sheets
This smart package will ease many routine operations and solve complex tedious tasks in your spreadsheets.
Read more about Power Tools Try it for free

Add-ons for Google Sheets

Merge Sheets Function by Color Remove Duplicates Compare Sheets Substring Batch Toolkit Easy Mail Merge Advanced Find and Replace See all products

Add-ons for Gmail

Shared Email Templates & Signatures Easy Mail Merge
Downloads
- Current versions
- Archived versions
Documentation
Support
Blog
About us
Purchase

Latest comments

Ashwin
2024-08-27

what is the Custom Formula to exclude multiple text or values in single column
1. Sanepara
  2025-12-30
  
  You can use Filter by condition --> Custom Formula =NOT(REGEXMATCH(A2, "text-1|text-2|text-3"))
2. Natalia Sharashova (Ablebits Team)
  2024-08-27
  
  Hello Ashwin,
  
  Unfortunately, Google Sheets' built-in filter doesn't support complex exclusion logic directly via a custom formula. I'd recommend using the FILTER function or QUERY function instead.
JC Dill
2021-04-30

I'm having problems sharing a sheet that I want users to be able to filter/sort, but not edit (not change cell values). In your example above, you talk about having an employee filter to look at only *their* data in the sheet - this looks like my use case, where the manager enters the data and the employee can filter and see it, but not edit it. I seem to be missing some important step in how this all works because when I share the link with others, they can't access any filter views. Can you fill me in on what I'm missing here?
1. Natalia Sharashova (Ablebits Team)
  2021-05-03
  
  Hello JC,
  
  When you share files and give users permissions to only view or comment on data, they can create their own filter views via Data > Filter views > Create new temporary filter vew. This menu is always accessible in spreadsheets. However, the users won't be able to see other users' filter views. Each user can create, save and see only his/her filter views.
  Also, I'd recommend sharing the spreadsheet directly with users by adding their email addresses in the sharing settings.
John
2019-05-03

Filter by, "Text Contains", is there an escape for the wildcards '*' and '?' ? Please include, or say there isn't, and offer the regex =REGEXMATCH(D:D,"\?")

Thanks!
1. Natalia Sharashova (Ablebits Team)
  2019-05-08
  
  Thank you very much for your feedback, John. :)
  
  The formula wasn't mentioned intentionally since:
  1) it works with textual values only, meaning the users would have to wrap their numbers in the TEXT function and then with the one you suggested.
  2) wildcard characters cannot be used in your formula. It looks for the exact sequences you indicate in "". Thus "\?" won't find anything since we don't have this in our cells.
  3) the function is case-sensitive and won't find anything if I enter "d" for all these "Dark Chocolate" cells.
  4) all of the above make the formula too complicated to use, leaving the easiest option of wildcard characters.

Post a comment