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.
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.
Contents
StaySorted Add-On
If you’re using the menus to sort your data, you must redo it when you add a new row. This can slow you down, especially when working with a large spreadsheet. The StaySorted add-on is a great solution for this problem. It automatically sorts any new entries in your spreadsheet, so you don’t have to worry about it.
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.
StaySorted Add-On
If you’re using the menus to sort your data, you must redo it when you add a new row. This can slow you down, especially when working with a large spreadsheet. The StaySorted add-on is a great solution for this problem. It automatically sorts any new entries in your spreadsheet, so you don’t have to worry about it.
Syntax
=FILTER(range,condition1,[condition2...])
Basic information about the inputs:
range
– The range of data that is to be filteredcondition1
– The criteria upon which the range is to be filteredcondition2...
– 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.
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
.
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
.
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.
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.