formulastreak

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.

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = Amara Osei
ABCDEFGH
1RepRegionJulAugSepTargetNorth reps
2Amara OseiNorth$12,400$13,100$11,800$36,000Amara Osei
3Ben FischerSouth$9,800$10,250$12,100$30,000Eva Novak
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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").

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = Amara Osei
ABCDEFGH
1RepRegionJulAugSepTargetNorth or East
2Amara OseiNorth$12,400$13,100$11,800$36,000Amara Osei
3Ben FischerSouth$9,800$10,250$12,100$30,000Lina Chen
4Lina ChenEast$15,200$14,800$16,900$45,000Eva Novak
5Diego AlvarezWest$8,700$9,100$8,400$28,000Grace Kim
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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.

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = No one
ABCDEFGH
1RepRegionJulAugSepTargetWest, huge Sep
2Amara OseiNorth$12,400$13,100$11,800$36,000No one
3Ben FischerSouth$9,800$10,250$12,100$30,000
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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.

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = Eva Novak
ABCDEFGHI
1RepRegionJulAugSepTargetRepRegion
2Amara OseiNorth$12,400$13,100$11,800$36,000Eva NovakNorth
3Ben FischerSouth$9,800$10,250$12,100$30,000Farid HaddadSouth
4Lina ChenEast$15,200$14,800$16,900$45,000Hugo MartinWest
5Diego AlvarezWest$8,700$9,100$8,400$28,000Lina ChenEast
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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
Select H2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGH
1RepRegionJulAugSepTargetEast reps
2Amara OseiNorth$12,400$13,100$11,800$36,000
3Ben FischerSouth$9,800$10,250$12,100$30,000
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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.

Practise it now