# FILTER function in Excel

> FILTER returns only the rows of a range that pass a test, such as every order from the West region.

Source: https://spreadsheetpractice.com/functions/filter · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** FILTER returns only the rows of a range that pass a test, such as every order from the West region.
- **Syntax:** `=FILTER(array, include, [if_empty])`
- **Most common use:** `=FILTER(A2:C8,B2:B8="West")` returns Bluebird Cafe (Every West order, all three columns)
- **Available in:** Excel 2021, 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** Only the rows where include is TRUE. It spills.
- **Google Sheets:** Google Sheets has FILTER, but extra arguments there are more conditions, not an if_empty value.

## What it does

FILTER returns only the rows of a range that pass a test, such as every order from the West region. The result is live, so changing the test cell or the data rewrites it instantly. Combine tests to build AND and OR conditions that SUMIFS-style functions can't handle.

## Syntax

`=FILTER(array, include, [if_empty])`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range to return rows from. |
| `include` | Required | A TRUE/FALSE test with the same number of rows as array, such as B2:B8="West". Multiply tests for AND, add them for OR. |
| `if_empty` | Optional | What to return when no rows pass, instead of #CALC!. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=FILTER(A2:C8,B2:B8="West")` | Bluebird Cafe (spills 3 × 3) | Every West order, all three columns |
| I2 | `=FILTER(A2:A8,(B2:B8="East")+(C2:C8>2000))` | Northwind (spills 4 × 1) | OR logic: East orders or any order over $2,000 |
| J2 | `=FILTER(A2:A8,C2:C8>5000,"None over $5,000")` | None over $5,000 | Nothing qualifies, so the fallback text shows |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Region | Amount |  | West orders |  |  |  | East or over $2,000 | Over $5,000 |
| 2 | Bluebird Cafe | West | $1,250 |  | Bluebird Cafe | West | $1,250 |  | Northwind | None over $5,000 |
| 3 | Northwind | East | $480 |  | Contoso | West | $2,310 |  | Contoso |  |
| 4 | Contoso | West | $2,310 |  | Tailspin | West | $395 |  | Adventure Works |  |
| 5 | Fabrikam | North | $760 |  |  |  |  |  | Proseware |  |
| 6 | Tailspin | West | $395 |  |  |  |  |  |  |  |
| 7 | Adventure Works | East | $1,580 |  |  |  |  |  |  |  |
| 8 | Proseware | South | $2,105 |  |  |  |  |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #CALC! | No rows passed the test. Add the third argument so the formula says so: =FILTER(A2:C8,B2:B8="West","No matches"). |
| #VALUE! | include is a different height from array. Make both cover the same rows, such as A2:C8 and B2:B8. |
| AND or OR gives one answer for everything | AND() and OR() return a single TRUE or FALSE for the whole range. Multiply the tests for AND and add them for OR. |
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |

## Tips

- Total a filtered list with SUM(FILTER(C2:C8,B2:B8="West")).
- Put the criteria in cells, such as B2:B8=G1, so anyone can change the report without editing the formula.

## Related functions

- [SORT](https://spreadsheetpractice.com/functions/sort): SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.
- [UNIQUE](https://spreadsheetpractice.com/functions/unique): UNIQUE returns each distinct value in a range once, in the order it first appears.
- [SUMIFS](https://spreadsheetpractice.com/functions/sumifs): SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.

## Questions

### How do I filter with multiple criteria?

Multiply the tests for AND, =FILTER(A2:C8,(B2:B8="West")*(C2:C8>1000)), and add them for OR, =FILTER(A2:C8,(B2:B8="West")+(B2:B8="East")).

### Can FILTER return only some columns?

Yes. Point array at just the columns you want, such as A2:A8, and keep the test the full height: =FILTER(A2:A8,B2:B8="West").

### Why does FILTER return #CALC!?

Nothing matched the test. Add a fallback as the third argument.

## Practice

A graded FILTER exercise ("Filter by region") is on [the FILTER page](https://spreadsheetpractice.com/functions/filter). It is checked instantly, then re-checked on changed data.

Lessons that use FILTER:

- [Lesson 27: FILTER](https://spreadsheetpractice.com/learn/filter-function) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
