How to use the FILTER function in Google Sheets

The FILTER function in Google Sheets helps you filter and return 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.

If you do not find the information you are looking for in this post, you can check out 100+ Functions & Formulas for Google Sheets & Excel Basics.

What is the syntax of the filter function?

This is the basic structure of the filter function:

=FILTER(range, condition1, [condition2, …])
  • range – a reference to the range of cells that the function filters.
  • condition1 – 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 … – 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 you can’t use both row and column conditions in the same function. All conditions should be either columns or rows, and the FILTER function doesn’t let you mix 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.

filter-function-google-sheets-1

Case 1: FILTER with one condition

In this first Google Sheets FILTER example, I will use just one condition and see how it works. This is the formula I will use:

=FILTER(A2:D10,A2:A10=”Vegetables”)

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”.

filter-function-google-sheets-2

I placed the function in cell F1, and accordingly the returned data flows from F1 towards the right and further down. The number of columns the function matches that of the input range. However, the number of rows might vary based on the filter conditions.

So, it is very important to keep cells free of data where you expect the data to flow. Otherwise, the function returns a #REF! Error as demonstrated in the screenshot below.

filter-function-google-sheets-3

Formulas are just the start

Case 2: Filter with two conditions

Let’s add one more filter condition and see what happens.

 =FILTER(A2:D10,A2:A10=”Vegetables”,C2:C10>20)

I essentially asked Google Sheets to show those rows that belong to the “Vegetables” category and whose energy is greater than 20 Kcal, and the FILTER function obliged!

filter-function-google-sheets-4

Case 3: Google Sheets FILTER, multiple conditions

I will add one more condition and see the outcome.

=FILTER(A2:D10,A2:A10=”Vegetables”,C2:C10>20,D2:D10<0.3)

And it works like a charm!

filter-function-google-sheets-5

Case 4: Filter with “or” condition

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.

=FILTER(A2:D10,(A2:A10=”Vegetables”)+(D2:D10<0.3))

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. 

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.

filter-function-google-sheets-6

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.

=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.

filter-function-google-sheets-7

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.

=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).

filter-function-google-sheets-8

Case 7: What if there is no match?

Of course, not getting any matches generates an error. This is what it looks like.

filter-function-google-sheets-9

Case 8: Breaking the rules

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.

filter-function-google-sheets-10

Google Sheets FILTER not working?

Let’s go over a short checklist to help you troubleshoot common issues with the filter function:

  1. Check the syntax of your filter function. Ensure that you’ve correctly specified the range of data you want to filter and that the conditions or criteria are accurately defined.
  2. Verify that your data range is correct and points to the intended rows and columns.
  3. Ensure there are no hidden rows or columns within your data range, this can affect the outcome of the filter function.
  4. Examine the filtering criteria you’ve set for filtering your data.

If this doesn’t work, AI might be the answer. For a great prompt to troubleshoot spreadsheet functions check out 15 awesome Chat GPT prompts for spreadsheet users.

You are officially a FILTER builder!

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.

Ready to streamline your spreadsheet data?

You may also like…