Filter by condition in Google Sheets and work with filters in shared documents

Filtering huge tables helps focusing your attention on the most needed information. Today I'd like to discuss with you the ways of adding filters by condition and applying a few filters to your data at once. I will also explain why Google Sheets filter is so useful and important when you work within a shared document.

Filter by condition in Google Sheets

Let's get back to our original table and prepare to filter its rows and columns. If you don't know or don't remember how to do that, please check my previous blog post.

When filter icons are there on column headers, click the one that belongs to the column you want to work with and choose Filter by condition. Ann additional option field will appear, with the word "None" in it.

Click on it, and you'll see the list of all conditions available to filter in Google Sheets. If none of the existing conditions meets your needs, you're free to create your own one by choosing Custom formula from the list:
Conditions that are used to filter your data.

Let's look through them together, shall we?

Cell is not empty

If cells contain numeric and textual values, logical expressions, or any other data, including spaces ( ) or empty strings (""), the rows with those cells will be displayed.

You can get the same result using the following formula when selecting the Custom formula option:

=ISBLANK(B:B)=FALSE

Cell is empty

This option is completely opposite to a previous one. Only cells that don't have any contents in them will be displayed.

You can also use this formula:

=ISBLANK(B:B)=TRUE

Text contains

This option shows rows where cells contain specific symbols – numeric and textual. It doesn't matter whether these symbols are at the beginning, in the middle, or at the end of a cell.

You can use wildcard characters to find some specific symbols in different positions within a cell. Asterisk (*) is used to substitute any number of characters while a question mark (?) replaces a single symbol:
Filter by text if it contains symbols in various positions.

As you can see, you can achieve the same result by entering various filter conditions.

The following formula will also help:

=REGEXMATCH(D:D;"Dark")

Text does not contain

I believe you already understand that the conditions here can be the same as in the point above, but the result will be the opposite. The value you enter will be hidden from the table.

As for the custom formula, it can look as follows:

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

Text starts with

For this condition, enter the first symbols (one or more) of the value of interest. Wildcard characters don't work here.

Text ends with

Alternatively, enter the last characters of the entries you need to display. Wildcard characters also can't be used here.

Text is exactly

Here you need to enter exactly what you want to see, whether it's a number or text. Milk Chocolate, for example. Entries that contain something other than that will be hidden. Thus, you can't use wildcard characters here.

Note. Please keep in mind that letter case matters for this condition.

If I wanted to use a formula to search for all records that contain "Milk Chocolate" only, I would enter the following:

=D:D="Milk Chocolate"

Date is, date is before, date is after

These Google Sheets filters allow using dates as conditions. As a result, you'll see the rows that contain an exact date, the date before or after the needed one.

Default options are today, tomorrow, yesterday, in the past week, in the past month, in the past year. You can also indicate an exact date:
Filter values by an exact date.

Note. When you enter any date, make sure to type it in your regional settings format rather than its format in the table. You can read more about date and time formats here.

Filter numeric values

A group of filters for numeric values is very easy to comprehend.

You can filter data in Google Sheets by the following numeric 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 conditions require two numbers that indicate starting and ending points of the numeric interval.

Tip. You can use cell references as filtering conditions, considering that a cell you refer to contains a number.

Of course, the formula can be used for this option as well. I want to see the rows where numbers in column E are greater than the value in I3:

=E:E>$I$3

Hide values that are less than 50.

Note. If you change the number you refer to (50 in my case), the filtered range won't update automatically. Click the Filter button and then OK to do that manually.

Custom formulas

Each of the filtering options above can be replaced by custom formulas that return the same result.

But formulas are usually used in filters if the condition is too complex to cover it by default means.

For example, I want to see all the goods that contain the words "Milk" and "Dark" in their names. I need this formula:

=OR(REGEXMATCH(D:D,"Dark"),REGEXMATCH(D:D,"Milk"))

Create a custom formula to filter the data.
This is not the most advanced filter though. There's also Google Sheets FILTER function that allows creating more complex conditions. We'll talk about it one day.

This is it – the standard Google Sheets filter, its options, its custom formulas.

But what if every employee required seeing only his/her sales? In other words, they need to apply a few filters to one table without creating them again and again.

Filter views will deal with the problem.

Google Sheets Filter views – create, name, save, and delete

Google Sheets Filter views help saving filters for later in order to avoid recreating them again. Different filters created here can be used by different users without interfering with each other.

Since we already created a standard Google Sheets filter that we want to save for later, we click Data > Filter views > Save as filter view.
Save filter by using Filter views option.

You will see that additional black bar has appeared. If you click the Options icon on the right of the black bar, you'll see the options to rename your filter, update the filtered range, duplicate it, or delete it completely. To close filter view, click the Close icon at the upper right corner of the bar.
Rename, change, copy, and remove filters via Filter views.

You can access and apply saved filters anytime. I have only two filters: Filter 1 and Filter 2:
See and apply available filters in Filter views.

"Why all those filter views", you may ask.

One of the main advantages of Google Sheets is the possibility for several people to work with tables simultaneously. Now, imagine what may happen if different people wish to see different pieces of data.

As soon as one user applies a filter in his/her Google Sheets, other users will see the changes immediately, meaning the data they work with will become partially hidden.

To solve the problem, Filter Views option was created. It works on each user's side, so they could apply filters just for themselves without interfering with other's work.

To create a filter view, click Data > Filter views > Create new filter view. Then filter the data as you need, and name it by clicking the "Name" field (or use the Options icon to rename it).
Create, rename and delete new filter view.

All the changes are saved automatically upon closing Filter Views. If they are no longer needed, remove them by clicking Options > Delete on the black filter bar.

Tip. A way to share filters: if the spreadsheet owner permitted you to edit the file, all other users will be able to see and use filters created by you.
Note. If all you can do is 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.

Easy way to create advanced filter in Google Sheets (without formulas)

Filter in Google Sheets is one of the easiest features. Sadly, the number of conditions you can apply to one column at a time is scarcely enough to cover most of the tasks.

Custom formulas could provide a way out, but even they can be tricky to build correctly, especially for dates and time or with OR/AND logic.

Luckily, there’s a better solution – a special add-on called Multiple VLOOKUP Matches. It filters multiple rows and columns, each with lots of criteria applied. The add-on is user-friendly, so you won’t have to doubt your own actions. But even if you do, the tool won’t alter your source data at all – it will copy and paste the filtered range wherever you decide.

Once you install the add-on, you will find it under the Add-ons tab in Google Sheets. The first step you’ll see is the only one there is:
Filter data in Google Sheets using Multiple VLOOKUP Matches.

  1. Let’s use the add-on to filter my Google Sheets table of sales (A1:G69):
    Select the range you want to filter by condition.
  2. The columns I’m really interested in are Date, Region, Product, and Total Sales, so I choose only them as the ones to return:
    Pick the number of rows and columns you need as the result.
  3. Now it’s time to compose the conditions. Let’s try and get all sales of milk and hazelnut chocolate for December 2019:
    Define the conditions to search.
  4. 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:
    Preview the result and the formula.
  5. Select the upper leftmost cells for the future filtered range and hit either Paste result (to return found matches as values) or Insert formula (to insert a formula with its outcome):
    How to filter in Google Sheets with a special add-on.

If you'd like to get to know Multiple VLOOKUP Matches better, I encourage you to install it from the Google Workspace Marketplace or learn more about it on its special page.

As a pleasant bonus, the add-on will deliver you from learning that scary Google Sheets VLOOKUP function ;)

If you have any questions left or want to share your thoughts on filters in Google Sheets, feel free to leave a comment down below.

12 responses to "Filter by condition in Google Sheets and work with filters in shared documents"

  1. James Mapes says:

    I want to create a spreadsheet that references a column for a specific suffix, then have it take data from another cell in a row that contains said suffix and total it in another cell.ia that possible?

  2. Harinder Singh says:

    pehn di lann, assi khotte haan, assi kanjar haan.

  3. Howard E Maxwell says:

    the below link is shared publicly

    https://docs.google.com/spreadsheets/d/1hWGAaDTnR_08OcB950dPczHpjvuHyVpohghOQD8QTW4/edit?usp=sharing

    If someone could help me come up with a filter that shows any occurrence of the word Blue in either Column B OR Column C

    Thank You in advance.

  4. Greg says:

    Thank you. Your article helped me. Have a nice day and Happy New 2019 Year! :)

  5. Chris Beall says:

    Great explanation! I especially appreciate that you have shown equivalent custom formulas for the built-in criteria like 'Text contains', useful as a starting point for more complex criteria.
    One possible correction: you state "Note. The formula can refer only to the column it's applied to..." but it appears that has changed. Today I was able to apply this formula with success: =OR(A:A="horses",C:C="cat") referring to two different columns. This worked regardless of whether I attached this custom formula to column A or column C.
    This is a significant improvement for me, because it appears (to be tested...) that it will enable quite complex multi-column criteria, combined with the advantage that the filtered data can be edited, apparently not the case with results from QUERY( or FILTER(.

  6. John says:

    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!

    • 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.

  7. Tom says:

    In $D$ i have some filed witch text.
    Data table with filters I have from D31:D.
    I add filter in custom formula =REGEXMATCH(D31:D;$D$3)
    And it works.
    But when I change data in D3 the results in my Table will refresh.
    Eny idea how to refresh this data table?

  8. Max says:

    Hello! Is it possible to filter by range, I try so
    custom formula =ISNA(MATCH(B:B; Settings!D2:D200; 0))=FALSE
    but for some reason it doesn't work.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)