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