Excel practice › Excel functions › FILTER

FILTER in Excel: how it works, with examples you can edit

FILTER returns the rows of a range that meet a condition. To list everything stored in the Reno warehouse, with the table in A2:C8 and the warehouses in column B: =FILTER(A2:C8, B2:B8="Reno").

You type the formula in one cell and the result spills into as many cells as it needs. Change the data and the list changes with it. Microsoft lists FILTER for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.

Skip to the 3 exercises ↓

Syntax

=FILTER(array, include, [if_empty])

array
The range to return rows from. It can be the whole table or only the columns you want back.
include
The condition: a comparison on a column that is as tall as array, like B2:B8="Reno". It gives one TRUE or FALSE per row, and FILTER keeps the TRUE rows.
if_empty
Optional. What to return when no row passes. Without it, an empty result is a #CALC! error.
Live exampleEverything in the Reno warehouse
E2fx
ABCDEFG
1ItemWarehouseOn handItemWarehouseOn hand
2Copy paperReno140Copy paperReno140
3TonerDallas12StaplersReno35
4StaplersReno35LabelsReno6
5BindersAtlanta8
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90

How Excel reads it

  1. B2:B8="Reno" compares each of the seven cells with "Reno". The result is a list of seven answers: TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE.
  2. FILTER goes through A2:C8 row by row and keeps the rows where the answer is TRUE.
  3. The rows that are left spill from the cell that holds the formula, down and to the right.

Only E2 holds a formula. The other cells of the result belong to it: to change or delete the list, edit E2. If one of the cells the result needs isn't empty, Excel shows #SPILL! until the cell is cleared (see the #SPILL! error).

The mistake: a filter that finds nothing

Excel has no empty list. When no row passes the condition, FILTER returns #CALC!. Nothing is stored in Boise, so this formula ends in an error. Give FILTER a third argument and it returns that instead: =FILTER(A2:C8,B2:B8="Boise","None").

Live example#CALC!: no row passes
E2fx
ABCDEFG
1ItemWarehouseOn handItemWarehouseOn hand
2Copy paperReno140#CALC!
3TonerDallas12
4StaplersReno35
5BindersAtlanta8
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90

A filter that works today can fail next month, when the last matching row is gone. If the result can ever be empty, write the third argument from the start.

Two conditions: * for AND, + for OR

For rows that have to pass two conditions, put each one in its own parentheses and multiply them. A row counts only when both are TRUE. Here: stored in Reno and fewer than 50 on hand.

Live exampleAND: both conditions, multiplied
E2fx
ABCDEFG
1ItemWarehouseOn handItemWarehouseOn hand
2Copy paperReno140StaplersReno35
3TonerDallas12LabelsReno6
4StaplersReno35
5BindersAtlanta8
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90

For rows that pass one condition or the other, add them. This list also shows that array doesn't have to be the whole table: it returns the item names only, and tests the warehouse column.

Live exampleOR: either condition, added
E2fx
ABCDE
1ItemWarehouseOn handItem
2Copy paperReno140Toner
3TonerDallas12Binders
4StaplersReno35Envelopes
5BindersAtlanta8Markers
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90

The AND and OR functions don't work for this. Each of them returns a single TRUE or FALSE for everything you give it, and FILTER needs one answer per row. That is why the conditions are multiplied or added instead.

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.

Exercise 1The rows of one warehouse

In E2, return the full rows (columns A to C) of everything stored in Dallas.

E2fx
ABCDEFG
1ItemWarehouseOn handItemWarehouseOn hand
2Copy paperReno140
3TonerDallas12
4StaplersReno35
5BindersAtlanta8
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90
Show the answer

=FILTER(A2:C8,B2:B8="Dallas") gives Toner (2 rows, 3 columns, starting with Toner).

The first argument is what comes back (the whole table), the second decides which rows: the ones where column B is Dallas.

Exercise 2A reorder list that can be empty

Items with fewer than 20 on hand have to be reordered. In E2, list the names of those items (column A only). When no item is under 20, the formula has to show the text None instead of an error.

E2fx
ABCDE
1ItemWarehouseOn handReorder
2Copy paperReno140
3TonerDallas12
4StaplersReno35
5BindersAtlanta8
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90
Show the answer

=FILTER(A2:A8,C2:C8<20,"None") gives Toner (3 rows, starting with Toner).

FILTER returns column A for the rows where column C is under 20. The third argument is only used when no row passes, and it replaces the #CALC! error.

Exercise 3OR and AND in one filter

In E2, list the names (column A) of the items stored in Atlanta or Dallas that have at least 50 on hand. Add the two warehouse conditions, put that sum in its own parentheses, then multiply by the quantity condition.

E2fx
ABCDE
1ItemWarehouseOn handItem
2Copy paperReno140
3TonerDallas12
4StaplersReno35
5BindersAtlanta8
6EnvelopesDallas260
7LabelsReno6
8MarkersAtlanta90
Show the answer

=FILTER(A2:A8,((B2:B8="Atlanta")+(B2:B8="Dallas"))*(C2:C8>=50)) gives Envelopes (2 rows, starting with Envelopes).

The sum of the two warehouse tests is 1 for a row in Atlanta or Dallas and 0 for any other. Multiplying by the quantity test leaves 1 only where both parts hold. Without the outer parentheses, the multiplication would apply to Dallas alone, and every Atlanta item would pass whatever its quantity.

Questions people ask

How do I use FILTER with multiple criteria in Excel?

Put each condition in parentheses and combine them in the second argument: multiply for AND, add for OR. =FILTER(A2:C8,(B2:B8="Reno")*(C2:C8<50)) keeps the rows that pass both tests, and (B2:B8="Dallas")+(B2:B8="Atlanta") keeps the rows that pass either one.

Why does FILTER return #CALC!?

Because no row passed the condition, and Excel can't return an empty list. Add the third argument, if_empty, with what you want to see in that case: =FILTER(A2:C8,B2:B8="Boise","None").

Can FILTER return only some columns?

Yes. The first argument is what comes back, and it can be narrower than the table: =FILTER(A2:A8,C2:C8<20) returns the names in column A for the rows where column C is under 20. The condition has to cover the same rows as the first argument.

Does the FILTER function work in Excel 2019 or 2016?

Microsoft lists FILTER for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.

Keep going

Lesson 10 is nine drills on a support desk's ticket log: UNIQUE, a blocked spill to clear, SORT, FILTER with one and with several conditions, the empty result, and a summary table built on a spilled list with #. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

SORT function, with live examplesUNIQUE function, with live examples#SPILL! error in Excel: meaning and fixesSUMIFS function, with live examplesXLOOKUP function, with live examplesExcel FILTER, SORT, UNIQUE practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: FILTER function. Last checked November 2026.