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.
Using the FILTER function in Google Sheets allows to instantly filter the source data to find specific information.
What makes it so powerful is that it doesn’t need us to use any complex 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 some useful examples where it can be used.
This Article Covers:
How To FILTER in Google Sheets – the Syntax
Below is the syntax of FILTER function in Google Sheets.
FILTER(range, condition1, [condition2, …])
- range – this is the range of 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:
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’.
Note that in this case, I have extracted the filtered data in the same sheet. If you want, you can use this formula in 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 an #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):
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.
Below is the formula that will do this:
=FILTER($A$2:$C$10,$B$2:$B$10="US",$C$2:$C$10>10000)
As I mentioned above, when I specify more than one condition, 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:
Note that as soon as I change the country name in cell E2, the filtered data automatically updates for that country.
I have used the following formula to do this:
=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 menu.
Something as shown below:
Â
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 the 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 do this.
Suppose we have a dataset as shown below:
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)
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 the FILTER function, you can use conditions such as whether data points (or records) should be above or below average.
Suppose you have a dataset as shown below:
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%).
Conclusion
The FILTER Function Google Sheets offers is a fantastic little tool for specifying which data your spreadsheet calculates. If you found this function and its tutorial useful,
You May Also Like the Following Google Sheets Tutorials:
- The Ultimate Guide to Google Sheets VLOOKUP Function (with Examples).
- How to Use COUNTIF Function in Google Sheets.
- How to Search in Google Sheets and Highlight the Matching Data.
- Google Sheets Weekday Function – Explained with Examples.
- How to Create and Use Filter Views in Google Sheets.
- How to VLOOKUP in Another Sheet in Google Sheets
- How To Calculate Weighted Average In Google Sheets
Â
