FILTER function, as the name suggests, will allow you to filter a dataset based on a condition (or multiple conditions).
For example, if you have a list of names with their state names and the sales value, you can use the FILTER function in Google Sheets to quickly get all the records/name one specific state (as shown below).
One benefit of using the FILTER function over the regular filter feature in Google Sheets is that FILTER function results are dynamic. In case you change anything in the original data, the resulting filtered data would automatically update.
This makes the Google Sheets FILTER function is a great choice when creating interactive reports or dashboards.
In this tutorial, I will show you how the FILTER function works in Google Sheets and also cover some useful examples to use it on day-to-day work.
So let’s get started by learning about the syntax of this function.
Google Sheets Filter Function Syntax
Below is the syntax of the FILTER function:
FILTER(range, condition1, [condition2, …]):
- range: This is the range of cells that you want to filter.
- condition1: This is the columns/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to be of the same size as that of the range
- [condition2]: This is an optional argument and can be the second condition for which you check in the formula. This again can be a column/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to be of the same size as that of the range.
When you use multiple conditions, those results that return true for both the conditions would be filtered.
In case the FILTER function can not find any result that matches the condition, it would return an #N/A error.
If you’re wondering how this works, go through a couple of examples (listed below) and it will become clear on how to use the FILTER function in Google Sheets.
Example 1 – Filtering Data based on a single condition
Suppose you have the dataset as shown below and you want to quickly filter all the records where the state name is Florida.
The below formula will do this:
The above formula takes the data range as the argument and the condition is B2:B11=“Florida”. This condition checks each cell in the range B2:B11 and if the value is equal to Florida, that record is filtered, else it’s not.
In this example, I have hard-coded the value, but you can also have this value in a cell and then refer to this cell. For example, if you have the text Florida in cell H1, you can also use the below formula:
A few things to know about the FILTER function.
The FILTER function in Google Sheets returns an array of values that spill over the adjacent cells (this is called a dynamic array). For this to work, you need to make sure that the adjacent cells (where the results would be placed) should be empty.
If any of the cell(s) is not empty, your formula will return a #REF! error. Google Sheets also tells you why it’s giving an error by showing a red triangle at the top-right of the cell and when you hover over it, it will show a message:
Array result was not expanded because it would overwrite data in F3
And as soon as you delete the filled cell that prevents the FILTER function to give the result, it will automatically fill the range with the result.
Also, the result of the FILTER formula is an array and you can change a part of the array. This means that you cant not change or delete one cell (or couple cells) in the result. You will have to delete the entire formula result. To delete the result, you can select the cell where you entered the formula and then hit the delete key.
Example 2 – Filter Data Based on Multiple Conditions (AND Condition)
You can also use the FILTER function to check for multiple conditions in such a way that it only returns those records where both the conditions are met.
For example, suppose you have the below data set and you want to filter all the records where the state is Florida and the sale value is more than 5000.
You can do this using the below formula;
The above formula checks for two conditions (where the state is Florida and sale value is more than 5000) and returns all the records that meet these criteria.
Similarly, if you want, you can have multiple conditions in the same FILTER formula.
Example 3 – Filter Records Based on Multiple Conditions (OR Condition)
In the above example, I have checked for two conditions and return results where both the conditions are TRUE.
You can also check for OR condition in the FILTER formula.
For example, suppose you have the dataset as shown below and you want to get all the records for California and Iowa. This means that the condition should be the state is either California or Iowa (which makes this an OR condition).
The below formula will do this:
The above formula uses the addition operator in the condition to first check both the conditions and then add the result of each. Since these conditions return an array or TRUEs and FALSEs, you can add these (since a TRUE is 1 and FALSE is 0 in Google Sheets).
This will give you 0 (or FALSE) where both the conditions are not met, 1 where one of the two conditions are met, and 2 where both the conditions are met.
And then the FILTER formula will return all the records where the conditions return value more than 0.
Example 4 – Filter Top 3 or Top 5 Records Based on Value
You can also use the FILTER function to quickly get the top 3 or top 5 (or whatever number of top/bottom number of records you choose).
For example, suppose I have the dataset as shown below and I want to quickly get the records for the top 3 sales values.
The below formula will do this:
The above formula uses the LARGE function to get the third largest value in the dataset. This value is then used in the condition to check whether the values in column C are greater than or equal to this value or not.
This would return all the records that match the criteria, which would be the top three records.
In case you want to get the bottom three records, you can use the below FILTER formula:
Example 5 – SORT the Filtered Data (Using a Combination of FILTER and SORT)
So far, all the FILTER formula examples that we have seen would filter the data in the same order in which it occurs in the dataset.
But what if you want to get the sorted dataset.
For example, suppose you’re filtering the top 5 records, it would be more useful to have these sorted in descending order (largest at the top).
Below is the formula that will filter the data and show it in descending order:
The above formula uses the same FILTER formula we used in the previous example to fetch the top three records based on the sales value.
And since I wanted the final result to be sorted in descending order, I have wrapped the FILTER function within the SORT function. The SORT function uses the result given by the FILTER formula and sorts it based on the third column (which has the sales value).
The third argument in the SORT function is FALSE, which is to specify that I want the final result in descending order. In case you leave it blank (or make it TRUE), the result will be in ascending order.
Example 6 – Filter All EVEN Number Records (or ODD number records)
This is not such a common use-case, but it’s something I have had to do when I get my data from someone else or from a database or webpages.
Many times, the data you need will only be in alternate rows (or every third/fourth/fifth row), and you would have a need to get rid of the extra rows so that you can get all the useful data together.
In such a case, you can use the FILTER function to quickly filter and get all the even number rows together (or all odd-numbered rows together). And you can also modify the formula to filter every third, fourth, or nth row in Google Sheets.
Suppose you have a dataset as shown below and you want to filter all the even-numbered rows in this dataset.
Below is the formula that will filter all the even rows:
The above formula uses the ROW function to get the row numbers of all the rows in the dataset. It then subtracts 1 from it as our dataset starts from the second row onwards.
Now, it uses the MOD function to check the following condition – MOD(ROW(A2:A11)–1,2)=0)
This would return TRUE for all the even-numbered rows and FALSE for all odd-numbered rows. And this array of TRUE and FALSE is used by the FILTER function to extract the records.
Similarly, if you want only the ODD numbered records, you can use the below formula:
And in case you want to filter every third row, you can use the below formula in Google Sheets:
So these are some of the examples where FILTER function in Google Sheets can be a real time saver. And when you combine it with other formulas, you can get a lot of stuff done with it.
Hope you found this tutorial useful.
Other Google Sheets tutorials you may like: