How to use the FILTER formula in Google Sheets

Written by Valentine Schelstraete

Apr 13, 2017

The FILTER formula in Google Sheets helps us filter and return the rows in a range that meet specified criteria. And, we can add multiple criteria across columns. Accordingly, we will be able to generate a new set of data while the original data remains intact.

Syntax

FILTER(range, condition1, [condition2, …])

  • range – is the address reference to the range of cells that the formula 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 formula. Either all the conditions should be of column type or row type, and the FILTER formula doesn’t allow mixing them.

Usage: FILTER formula Google Sheets

Here is the sample data on which we will try various combinations of the FILTER formula, and understand its behavior.

Sample Data: Food Category and Energy Fat Details
Case 1: FILTER(range, condition1)

In this example, we will use just one condition and see how it works.

FILTER Formula Google Sheets: Case 1

In the above example, our condition to filter on the first column is “Vegetables”. So the FILTER formula fetches all those rows where the first column has the value “Vegetables”.

Please note that we have keyed in the formula in the cell E1, and accordingly the returned data flows from E1 towards the right and further down. The number of columns the formula returned is 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 we keep the cells, where we expect the data to flow, clear of any values. Otherwise, the formula returns #REF! error. This is demonstrated in the screenshot below.

Case 1 Ref Error in Google Sheets
Case 2: FILTER(range, condition1, condition2)

Let us add one more filter condition and see what happens.

FILTER Formula Google Sheets:  Case 2

We essentially asked Google Sheets to show those rows that belong to Vegetablesfood category and whose energy is greater than 20 Kcal, and the FILTER formula obliged!

Case 3: FILTER(range, condition1, condition2, condition3)

We will add one more condition and see the outcome.

FILTER Formula Google Sheets: Case 3

And it works like a charm!

Case 4: When there is no match!

What would happen if it doesn’t find any rows that match the filter conditions? Of course, it throws up an error!

FILTER Formula Google Sheets: Case 4
Case 5: When we mix things up!

We already know we can’t input row and column type conditions within a single FILTER formula. Let us try that anyway and see what Google Sheets has to say.

FILTER Formula Google Sheets: Case 5

And that’s all there is to it! 🙂

The UNIQUE formula in Google Sheets can be of great help to you as well. Check it out in our blog post: How to use the UNIQUE formula in Google Sheets.

You may also like…

Share This