Using FILTER Function in Google Sheets (explained with Examples)

Watch video – Using FILTER function in Google Sheets (examples)

Google Sheets has some useful functions that are not available in Excel (such as IMPORTRANGE, QUERY, IMPORTDATA, etc.)

One such extremely powerful one is the FILTER function.

FILTER function allows to instantly filter the source data and get the result.

What makes is so powerful is that it doesn’t need us to use any functionality (such as the filter options in Excel), and it updates automatically (as it’s a function).

In this detailed tutorial, I will introduce you to the FILTER function in Google Sheets and show you useful examples where it can be used.

FILTER Function in Google Sheets – Syntax

Below is the syntax of FILTER function in Google Sheets.

FILTER(rangecondition1[condition2, …])

  • range – this is the range from which you want the filtered data.
  • condition 1 – the condition based on which the data is filtered.
  • [condition2, …] – additional conditions based on which the data is filtered.

Note that only the data that meets all the specified conditions is returned as the result of the FILTER function.

Now let’s dive in and see some useful examples of using the FILTER function in Google Sheets.

For this tutorial, I have Zoomed In the Google Sheets to make it bigger and more clear. In your Google Sheets, you may find the size to be smaller.

Example 1 – Filter All Records of a Given Country

Suppose you have a dataset a shown below:

Using FILTER Function in Google Sheets - Example 1 Data set

Now from this dataset, if I want to quickly filter the records for ‘US’ only, I can use the following formula:

=FILTER($A$2:$C$10,$B$2:$B$10="US")

This would instantly give me all the records where the country is ‘US’.

FILTER formula Example 1 - Result

Note that in this case, I have extracted the filtered data in the same sheet. If you want, you can use this formula is other sheets.

Also, remember that

  • The result of the FILTER function is an array. This means that you can not delete a part of the result. You will have to delete the entire result.
  • If there is no match based on the conditions you have specified, it will return a #N/A error.
  • If any cell that is supposed to be filled by the result of the FILTER function is already filled, the formula will return a REF error (as shown below):Filter Function in Google Sheets - Example 1 - Error overwrite

Example 2 – Filter Records Based on Multiple Conditions

Suppose I have the following dataset and I want to get a list of all the records where the country is ‘US’ and the sale amount is greater than 10000.

Using FILTER Function in Google Sheets - Example 1 Data set

Below is the formula that will do this:

=FILTER($A$2:$C$10,$B$2:$B$10="US",$C$2:$C$10>10000)

Filter formula in Google Sheets - Example 2 result

As I mentioned above, when I specify more than one conditions, only the records that satisfy all the conditions are returned as the result of the FILTER formula.

Example 3 – Filtering Data Using Multiple Drop-down Lists

In the example shown above, I have hardcoded the condition in the formula itself.

For example, in the formula =FILTER($A$2:$C$10,$B$2:$B$10=”US”), I have manually entered the value US in the formula.

With the FILTER function, you can use cell references as well.

This makes the formula dynamic, as now when I change the value of the cell that has the condition, it will automatically update the result.

Something as shown below:

Filter Function in Google Sheets - using Cell References

Note that as soon as I change the country name in cell E2, the filtered data automatically update for that country.

I have used the following formula to get this done:

=FILTER($A$2:$C$10,$B$2:$B$10=E2) 

Now we can take it a level higher and create drop-downs of the conditions. For example, we can have a drop-down that has the country names in it and the user can select a country from the drop-down.

Something as shown below:

using drop down list to get the filtered data

This technique can be really useful if you’re creating dashboards and you want the user to have the flexibility to quickly filter data based on the selection.

Example 4 – Filter Every Even / Odd Row in the Dataset

With FILTER function, it’s extremely easy to extract all the even-numbered rows (or the odd-numbered rows) from a dataset. Without this function, it gets tedious and complex to get this done.

Suppose we have a dataset as shown below:

Using FILTER Function in Google Sheets - Example 1 Data set

Below is the formula that will filter all the even-numbered rows from this data in Google Sheets:

=FILTER($A$2:$C$10,MOD(ROW(A2:A10)-1,2)=0)

Filter Even Numbered Rows in Google Sheets

Note that here I use the MOD function and the ROW function to check whether a row is even-numbered or not. ROW function gives the row numbers of each row. MOD function then checks whether a row number is even or not (by checking the remainder when divided by 2).

Similarly, you can also filter the odd-numbered rows (below is the formula):

=FILTER($A$2:$C$10,MOD(ROW(A2:A10)-1,2)=1) 

Also, you can use the same technique to filter every Nth row from a dataset.

For example, if you want to get every 4th row from the dataset, here is the formula:

=FILTER($A$2:$C$10,MOD(ROW(A2:A10)-1,4)=0)

Example 5 – Filter All the Records that are Above/Below Average

Using FILTER function, you can use conditions such as the data points (or records) should be above or below average.

Suppose you have a dataset as shown below:

Using FILTER Function in Google Sheets - Example 1 Data set

Here is the formula that will give you all the records where the sale is above the average sales value in this dataset:

=FILTER($A$2:$C$10,$C$2:$C$10>AVERAGE($C$2:$C$10))

Similarly, you can use the below formula to get the records where sale value is below the average sales value in this dataset:

=FILTER($A$2:$C$10,$C$2:$C$10<AVERAGE($C$2:$C$10))

Another example where this construct of the formula can be useful if you’re a teacher and you want to get the list of all the students who scored above average (or are in the top 10%/20%).

You May Also Like the Following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X