The Excel FILTER function returns all rows of a range that meet your criteria — one formula, many results. The output spills onto the worksheet and updates automatically when the data changes, replacing copy-paste filtering and array gymnastics with a single readable formula.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to filter (the rows you want back). |
include | Required | A TRUE/FALSE test with the same number of rows as array — e.g. B2:B9="West". |
if_empty | Optional | What to return when no rows match. Omit it and an empty result shows #CALC!. |
Available in: Excel for Microsoft 365, Excel 2021+, and Excel for the web. Older versions show #NAME?.
Filter by one criterion
The orders table below sits in A2:C9. One formula pulls every "West" order (highlighted rows):
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Region | Amount |
| 2 | Acme Corp | West | $1,850 |
| 3 | Bolt LLC | East | $920 |
| 4 | Cedar Inc | West | $2,400 |
| 5 | Delta Co | North | $1,310 |
| 6 | Echo Ltd | West | $480 |
| 7 | Foxtrot | East | $3,175 |
| 8 | Gamma LLC | North | $760 |
| 9 | Helix Co | West | $1,490 |
The result lands in the cell holding the formula and spills right and down. Refer to the whole spilled result elsewhere with the spill operator: if the formula is in E2, use E2#.
Try it: interactive FILTER demo
Filter the orders table live. Combine a region with a minimum amount and watch the formula grow AND logic.
Multiple criteria: AND uses *, OR uses +
The include argument is just arithmetic on TRUE/FALSE arrays. Multiply tests for AND, add them for OR:
Each test sits in its own parentheses. You can chain as many as you need.
Sort, count, and shape the results
FILTER nests beautifully with other dynamic array functions:
Errors & common pitfalls
#CALC! — empty result. No rows matched and you didn't supply if_empty. Add a third argument like "No results" or "".
#SPILL! — no room to spill. Something occupies the cells the result needs. Clear the spill range below and to the right of the formula.
#VALUE! — mismatched sizes. The include test must cover exactly as many rows as array. =FILTER(A2:C9, B2:B8="West") fails — 8 rows filtered by a 7-row test.
Pitfall: don't type inside the spill area. Manually entering data where results will land causes #SPILL!. Keep the landing zone clear — results grow and shrink as data changes.
Pitfall: sharing files with older Excel. Excel 2019 and older don't have FILTER — recipients see #NAME?. For maximum compatibility you'd need helper columns or PivotTables instead.
Practice workbook
Frequently asked questions
Which Excel versions have the FILTER function?
How do I FILTER with multiple criteria?
What's the difference between FILTER and AutoFilter?
Can FILTER return only certain columns?
How do I sort FILTER results?
Why does my FILTER return #SPILL!?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class