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.
- 1 Video Explanation
- 2 Purpose
- 3 Syntax
- 4 Common Errors
- 5 Related Functions
- 6 Examples
Returns data from your specified source with only the criteria that you selected. Yes, it gets rid of what you don’t want.
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
#N/A – No matches are found in FILTER evaluation.
– None of the data in the
range meets the
#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.
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
💡 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.
The formula looks for all cells in the
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
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
Example 2 – Filtering Two Columns of Data
Second, let’s filter a table with two columns of data using one
We have expanded our
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
Example 3 – Filtering Two Columns with Two Conditions
For the third example, we filter this
range of two columns of data by two
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
B1:B4. There is only one row that meets those
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.
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.