FILTER Function
Returns every row that meets your conditions — and updates live.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2021+
Returns
A spilled array of matching rows
Overview
FILTER is like AutoFilter in a formula. Give it your data and a TRUE/FALSE test, and it spills every row that passes into the cells below and to the right.
Unlike VLOOKUP or XLOOKUP, which return the first match, FILTER returns all matches. Change the data or the criteria and the results update instantly — no clicking, no refreshing.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The data to filter — one column or a whole table. |
| include | Yes | A TRUE/FALSE test the same height (or width) as array, such as `B2:B6="East"`. |
| if_empty | Optional | What to show when nothing matches. Without it you get #CALC!. |
Examples
Example 1 — All rows for one region
Show every sale from the region typed in G1.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region: | East | ||
| 2 | Ana | East | 400 | ||||
| 3 | Ben | West | 350 | Ana | East | 400 | |
| 4 | Cara | East | 520 | Cara | East | 520 | |
| 5 | Dev | West | 275 | Eli | East | 610 | |
| 6 | Eli | East | 610 | ||||
| 7 | Fay | North | 330 |
Example 2 — Two conditions (AND)
East region and sales over 450. Wrap each test in brackets and multiply them:
Each test produces TRUE/FALSE (1/0). Multiplying gives 1 only when both are true, so this returns Cara and Eli.
Example 3 — Either condition (OR)
East region or sales over 450. Add the tests instead:
Adding gives 1 or more if either test is true. Here it returns Ana, Cara and Eli (no West or North rep is over 450).
Example 4 — Sorted results
Wrap FILTER in SORT to rank the matches — East reps by sales, highest first.
SORT's second argument is the column to sort by (3 = Sales) and -1 means descending: Eli, Cara, Ana.
Example 5 — Only the columns you need
Return just Rep and Sales, skipping Region, by combining FILTER with CHOOSECOLS.
Example 6 — Partial text matches
FILTER has no wildcards, but SEARCH can find text anywhere in a cell. Show reps whose name contains "a":
SEARCH returns a position number when it finds the text and an error when it doesn't; ISNUMBER turns that into TRUE/FALSE.
Common mistakes
- #CALC! errors. Nothing matched. Add an if_empty value like "None".
- #SPILL! errors. Something is in the way of the results. Clear the cells below/right of the formula.
- #VALUE! errors. The include range is a different size from array — e.g. A2:C7 with B2:B10.
- Using AND() or OR(). These collapse the whole range into a single TRUE/FALSE. Use * for AND and + for OR.
Notes
- Refer to the whole spilled result elsewhere with the # symbol, e.g. =COUNTA(E3#).
- Related functions: SORT, SORTBY, UNIQUE, XLOOKUP, CHOOSECOLS.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.