Watch video – Using FILTER function in Google Sheets (examples)
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.
This Tutorial Covers
Below is the syntax of FILTER function in Google Sheets.
FILTER(range, condition1, [condition2, …])
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.
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:
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 is other sheets.
Also, remember that
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:
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.
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 update for that country.
I have used the following formula to get this done:
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:
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.
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:
Below is the formula that will filter all the even-numbered rows from this data 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):
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:
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:
Here is the formula that will give you all the records where the sale is above the average sales value in this dataset:
Similarly, you can use the below formula to get the records where sale value is below the average sales value in this dataset:
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: