The FILTER function
Updated · Works in: Excel 2021+, Microsoft 365, Google Sheets · Every example checked before release
Short answer
=FILTER(array, condition, [if_empty]) returns every matching row and spills it. Multiply conditions for AND, add them for OR, and give a third argument for the no-match case. Excel 2021, Microsoft 365 and Google Sheets.
FILTER(array, include, [if_empty]) returns every row that matches, and the result spills into the cells below. It replaces a lot of manual filtering and copy-pasting.
One condition
The include argument is a column of TRUE/FALSE as tall as the data. Write the formula once; the blue outline shows the spill.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | North reps | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Amara Osei | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Eva Novak | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | ||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | ||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | ||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | ||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | ||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
AND and OR
Multiply conditions for AND: (B2:B9="South")*(E2:E9>12000). Add them for OR: (B2:B9="North")+(B2:B9="East").
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | North or East | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Amara Osei | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Lina Chen | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | Eva Novak | |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | Grace Kim | |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | ||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | ||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | ||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
When nothing matches
With no matches, Excel's FILTER returns #CALC! and Google Sheets' returns #N/A. The third argument (Excel) replaces it with anything you like.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | West, huge Sep | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | No one | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | ||
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | ||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | ||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | ||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | ||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | ||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Return several columns
Give FILTER a wider array and it returns whole rows. Wrap it in SORT to order the result.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Rep | Region | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Eva Novak | North | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Farid Haddad | South | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | Hugo Martin | West | |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | Lina Chen | East | |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | |||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | |||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | |||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Check yourself
One real exercise from the “Dynamic arrays” unit. Checked on our server; no account needed.List every rep in the East with FILTER. Write it in H2 and let it spill.
- Use FILTER
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | East reps | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | ||
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | ||
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | ||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | ||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | ||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | ||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | ||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Common mistakes
A condition range of a different height from the data (#VALUE!).
Something typed in the cells the result needs (#SPILL!).
Using AND() or OR() inside FILTER: they return one value, not one per row. Multiply or add instead.
Questions
Which versions have FILTER?+
Excel 2021, Excel 2024, Microsoft 365 and Google Sheets. Excel 2019 and older don't.
How do I count the rows FILTER returns?+
Wrap it in ROWS, which counts the rows of a range or result: `ROWS(FILTER(A2:A9, E2:E9>13000))`.
Does Google Sheets FILTER work the same way?+
FILTER(range, condition) works in both. Google Sheets also takes several conditions as separate arguments and returns rows that meet all of them; Excel's third argument is instead the value to show when nothing matches.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.