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

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

• Filter by condition in Google Sheets
  • Cell is not empty
  • Cell is empty
  • Text contains
  • Text does not contain
  • Text starts with
  • Text ends with
  • Text is exactly
  • Date is, date is before, date is after
  • Filter numeric values
  • Custom formulas
• Filter Views - create, name, save, and delete

Filter by condition in Google Sheets

Let's get back to our original table and prepare it for filtering. 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 for filtering. If none of the existing conditions meets your needs, you're free to create your own one by choosing Custom formula from the list:

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 spacing ( ) or empty string (""), 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 question mark (?) replaces a single symbol:

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

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 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 numeric values

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

You can filter the data 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.

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

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 goods that contain the words "Milk" and "Dark" in their names. I need this formula:

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

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

So here it is, our filter, its options, its custom formulas.

But what if every employee requires 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.

Filter views - create, name, save, and delete

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 filter that we want to save for later, we go to Data > Filter views > Save as filter view.

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 its range, duplicate it, or delete it completely. To close filter view, click the Close icon at the upper right corner of the bar.

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

"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, 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, go to 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).

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.

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

You may also be interested in:

• Filter basics in Google Sheets
• Move, merge, hide, and freeze columns in a Google spreadsheet
• How to filter in Excel