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

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

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.