The FILTER function in Google Sheets helps you filter and return the rows in a range that meet specified criteria. You can also add multiple criteria across columns. The FILTER function generates a new set of data while keeping the original data intact.
How to use the FILTER function in Google Sheets
Syntax
- range – is the address reference to the range of cells that the function filters.
- condition1 – is an array, row or column equal in length or width as that of the corresponding first row or column of range respectively. It contains evaluated TRUE or FALSE values.
- condition2 … – these are optional and additional arrays, rows or columns containing evaluated TRUE or FALSE values to specify if the corresponding row or column within the range needs consideration for the filtering process.
Please note that we cannot use both row and column conditions in the same function. Either all the conditions should be of column type or row type, and the FILTER function doesn’t allow mixing them.
How to use the FILTER function
Below is the sample data on which I will try various combinations of the FILTER function to demonstrate its behaviour.
Case 1: FILTER(range, condition1)
In this example, I will use just one condition and see how it works.
The function I use here is =Filter(A2:D10,A2:A10=”Vegetables”).
In the above example, the condition to filter on the first column is “Vegetables”. So the FILTER function fetches all those rows where the first column has the value “Vegetables”.
Please note that I have keyed in the function in the cell F1, and accordingly the returned data flows from F1 towards the right and further down. The number of columns the function returned is the same as that of the input range. However, the number of rows might vary based on the filter conditions.
So, it is very important that you keep the cells where you expect the data to flow, clear of any values. Otherwise, the function returns #REF! error. This is demonstrated in the screenshot below.
Case 2: FILTER(range, condition1, condition2)
Let’s add one more filter condition and see what happens.
I essentially asked Google Sheets to show those rows that belong to Vegetables food category and whose energy is greater than 20 Kcal, and the FILTER function obliged! I did this by typing =Filter(A2:D10,A2:A10=”Vegetables”,C2:C10>20).
Case 3: FILTER(range, condition1, condition2, condition3)
I will add one more condition and see the outcome when I input the function =Filter(A2:D10,A2:A10=”Vegetables”,C2:C10>20,D2:D10<0.3.
And it works like a charm!
Case 4: Multiple “OR” conditions
So far all the examples have been using “and” conditions – that is, the data has to meet all the conditions specified in order for it to pass the filter.
However, there is another option – you can use “or” conditions that will work for data that meets any one of multiple conditions. The example below demonstrates this.
As you can see, the filter here is finding any item that is a vegetable OR has less than 0.3g of fat. Because only one condition needs to be met, Apple and Broccoli are both included even though an apple is not a vegetable, and broccoli has more than 0.3g of fat. The function used here is =Filter(A2:D10,(A2:A10=”Vegetables”)+(D2:D10<0.3)).
In order to use an OR condition, all you need to do is put the conditions in brackets and add them together with a plus sign, instead of separating them by a comma.
Case 5: Largest values
Another helpful way of using the FILTER function is to find the top few items in any category.
In the example below, I’ve filtered the data to find the three foods with the largest amount of calories by typing =Filter(A2:D10,(C2:C10>=LARGE(C2:C10,3))).
This is achieved by using the FILTER function in combination with the LARGE function, which identifies the largest numbers in a dataset.
I’ve used the LARGE function to identify the three largest calorie numbers, and then filtered the data according to these. You can use this method to filter for any number of the largest or smallest values.
Case 6: Sorting filtered results
In the previous example, I found the top 3 foods with the most calories. But as you could see, the results weren’t shown in order from most calorie-dense to least.
To sort the results of the FILTER function, you can combine it with the SORT function. I do just that in the example below by typing =SORT(Filter(A2:D10,(C2:C10>=LARGE(C2:C10,3))),3,False).
All I’ve done here is added the SORT function in front of the FILTER function, then told the SORT function to sort based on column 3 in descending order (by indicating False).
Case 7: When there is no match!
What would happen if it doesn’t find any rows that match the filter conditions? Of course, it generates an error!
Case 8: When you mix things up
You already know you can’t input row and column type conditions within a single FILTER function. I’ll try that anyway and see what Google Sheets has to say.
That’s how to get started with the Google Sheets FILTER function. Looking for more spreadsheet tutorials and tips? Check out our other posts below!
The UNIQUE function in Google Sheets can be of great help to you as well. Check it out in our blog post: How to use the UNIQUE function in Google Sheets.
![How to use the Google Sheets QUERY function [UPDATED: 2023]](https://blog.sheetgo.com/wp-content/uploads/2023/07/blog-cover-gsheets-functions_03-400x250.png)
