FILTER Function in Google Sheets

The FILTER function lets you narrow down a range of data to include only items that meet specified criteria. The function creates a new, smaller range of data called a dynamic array. Be sure to allow enough room for the output; otherwise, you will receive an error.

Google Sheets' helper text for the FILTER function

The results of the filter function update in real time with any changes to the source data.

Get a copy of the Google Sheet with examples to follow along.

Video Explanation

Purpose

Returns data from your specified source with only the criteria that you selected. Yes, it gets rid of what you don’t want.

Syntax

=FILTER(range,condition1,[condition2...])

Basic information about the inputs:

  • range – The range of data that is to be filtered
  • condition1 – The criteria upon which the range is to be filtered
  • condition2... – The optional additional criteria upon which the range is to be filtered

Common Errors

#N/A – No matches are found in FILTER evaluation.

– None of the data in the range meets the conditions.

#REF – Array result was not expanded because it would overwrite data in [range].

– Data is in a cell where the FILTER function tries to write its output. You need to delete or move this data to resolve the error.

Related Functions

SORT – Outputs a sorted range of data

SORTN – Sorts a range of data and returns the first n items

UNIQUE – Outputs data with duplicates removed

QUERY – A flexible function that can filter output

Examples

💡 This function’s output can spill below, and sometimes to the right, of its cell.

Example 1 – Filtering One Column of Data

First, we are using the FILTER function with one column of data.

Using the FILTER function with one column and one condition
One Column with One Condition

Formula used: =FILTER(A1:A4,A1:A4<4)

The formula looks for all cells in the range of A1:A4 that match condition1, which is a number less than 4. It may look strange to specify A1:A4 twice. You will see why later, as the condition and the range are not always the same.

Since the numbers in A1, A3, and A4 are less than 4, the FILTER function returns these three. The function always writes its output below and, if there is more than one column, to the right. In this case, Google Sheets places the result in cells A6 through A8.

Example 2 – Filtering Two Columns of Data

Second, let’s filter a table with two columns of data using one condition.

Filter a two column table by one condition
Two Columns with One Condition

Formula used: =FILTER(A1:B4,B1:B4="b")

We have expanded our range to A1:B4, and changed the condition to B1:B4="b". This example shows that the condition does not need to use the entire range. Even though only one column is in the condition, the result contains both columns from the range.

Example 3 – Filtering Two Columns with Two Conditions

For the third example, we filter this range of two columns of data by two conditions.

The filter function working on two columns using two conditions
Two Columns and Two Conditions

Formula used: =FILTER(A1:B4,A1:A4=3,B1:B4="b")

The filter function can accept more than one condition. In this case, we are looking for a 3 in the range of A1:A4 and a "b" in the range of B1:B4. There is only one row that meets those conditions.

Example 4 – Filtering that Meets One of Two Conditions

The filter function returns values that meet the conditions specified with the regular syntax of the function. However, there is a trick to making the function return values if one condition OR another is met.

The filter function using an OR condition

Formula used: =FILTER(A1:B4,(A1:A4=3)+(B1:B4="b"))

In this formula, we broke from the prescribed method of separating the conditions with commas (or semi-colons in countries that use commas as decimal separators). Instead, we used a + to join them and wrapped each condition in parenthesis. That’s all it takes, and now the FILTER function works when either condition is met instead of both.

Live Examples in Sheets

Go to this spreadsheet for several examples of the FILTER function that you can study and use anywhere you want.

Leave a Comment