FILTER function in Google Sheets

When you work with worksheets, sometimes you have to deal with a large amount of data. Searching for important information can be very boring and time-consuming.

That’s why Google Sheets offers a few filtering options. Apart from a tool to filter tables, there is also the FILTER function.

This function allows you to filter data based on single or multiple conditions.

Function Syntax

range – The data you want to filter.

condition1 – Row or column containing TRUE or FALSE values.

condition2 – [optional] Additional columns or rows containing boolean values

How to use the FILTER function

Let’s try a simple example of the filter function with a single condition. There is a list of random numbers from 1 to 100.

We are going to create a function that filters only values greater than 50.

Enter the following formula into cell B2:

After you press enter, the rest of the cells are filled with values meeting the condition.

It’s time to analyze how the function works.

Cell B2 contains the formula you’ve just entered. The rest of the cells (B3:B5) contain values.

Delete filtered rows in google sheets

You can’t delete these values unless you delete the formula. If you remove the cell with the formula, all the values will disappear.

You may want to delete only selected values, instead of all. To do it, select cells from B2 to B5, copy them (Ctrl + C) and paste as values (Ctrl + Shift + V).

Now, you can delete single values.

Examples for a single condition:

Condition	Formula
>= 60	=FILTER(A2:A11,A2:A11>=60)
Above average	=FILTER(A2:A11,A2:A11>AVERAGE(A2:A11))
Above median	=FILTER(A2:A11,A2:A11>MEDIAN(A2:A11))
Odd	=FILTER(A2:A11,ISODD(A2:A11))
Even	=FILTER(A2:A11, ISEVEN(A2:A11))

These are the results:

Multiple conditions in the filter function

With the filter function, you are not limited to a single condition.

Let’s create formulas for 2 and 3 conditions.

2 conditions

3 conditions

The formula with two conditions returns all numbers between 30 and 60, and the one with three conditions will return only odd numbers in this range.

Display formula in multiple columns

So far, the FILTER formula displayed data inside a single column, but if there is a need it can display data in more than one column.

This example is going to filter all people in working age (18 – 65).

What you have to do, is to select a range that contains multiple columns.

You can use this formula in cell C2:

Sort the filtered data

The result inside the table is not displayed in an ordered manner. If you want to sort the result, you can use the SORT function.

To sort the result, simply put the formula inside the SORT function:

The second argument is the column by which you want to sort the data. In our case: by name.

The TRUE value in the third argument means that the data should be sorted in ascending order.

In this very case, you can use SORT with a single argument.

If you want to sort by the working-age in the descending order, you can use this formula:

Values in columns, instead of rows

Usually, a list of values is displayed inside a single column. But occasionally, you can encounter that the data is displayed inside multiple columns and a single row.

This example illustrates that:

The FILTER function can also work with that. You just have to modify the ranges in the formula.

If there is no match

If no value meets the condition, Sheets will display the #N/A message.

Count filtered rows

If you need to count how many values are filtered, you can use the COUNT function.

take the FILTER formula as an argument for the COUNT function.

It will return a value inside a single column.

Filter unique values only

The FILTER function doesn’t remove duplicates. If you want to filter unique values, you can use the formula as a parameter of the UNIQUE function.

Using REGEX for specific filter conditions

The REGEXMATCH function is responsible for regular expressions in Google Sheets.

The following example contains a list of people. From this list, we are going to display only these names that have “ll” or “rr” in them:

Filter date range

If you want to use filtering with dates, you have to convert them to numbers. You can do this using the DATEVALUE function.

In this example, we are going to filter only dates that fall into the range between 1/1/2012 and 1/1/2018. Let’s use the following formula:

The DATEVALUE function converts a date to number, so in this case, we have a range between two numbers.

Four dates meet the condition.

If you format dates to number, you are going to get the following result: