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.
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, likeB2: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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Item | Warehouse | On hand | |
| 2 | Copy paper | Reno | 140 | Copy paper | Reno | 140 | |
| 3 | Toner | Dallas | 12 | Staplers | Reno | 35 | |
| 4 | Staplers | Reno | 35 | Labels | Reno | 6 | |
| 5 | Binders | Atlanta | 8 | ||||
| 6 | Envelopes | Dallas | 260 | ||||
| 7 | Labels | Reno | 6 | ||||
| 8 | Markers | Atlanta | 90 |
Reno to Dallas and the list has two rows.How Excel reads it
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.- FILTER goes through A2:C8 row by row and keeps the rows where the answer is TRUE.
- 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").
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Item | Warehouse | On hand | |
| 2 | Copy paper | Reno | 140 | #CALC! | |||
| 3 | Toner | Dallas | 12 | ||||
| 4 | Staplers | Reno | 35 | ||||
| 5 | Binders | Atlanta | 8 | ||||
| 6 | Envelopes | Dallas | 260 | ||||
| 7 | Labels | Reno | 6 | ||||
| 8 | Markers | Atlanta | 90 |
,"None".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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Item | Warehouse | On hand | |
| 2 | Copy paper | Reno | 140 | Staplers | Reno | 35 | |
| 3 | Toner | Dallas | 12 | Labels | Reno | 6 | |
| 4 | Staplers | Reno | 35 | ||||
| 5 | Binders | Atlanta | 8 | ||||
| 6 | Envelopes | Dallas | 260 | ||||
| 7 | Labels | Reno | 6 | ||||
| 8 | Markers | Atlanta | 90 |
50 to 200 and it comes back.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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Item | |
| 2 | Copy paper | Reno | 140 | Toner | |
| 3 | Toner | Dallas | 12 | Binders | |
| 4 | Staplers | Reno | 35 | Envelopes | |
| 5 | Binders | Atlanta | 8 | Markers | |
| 6 | Envelopes | Dallas | 260 | ||
| 7 | Labels | Reno | 6 | ||
| 8 | Markers | Atlanta | 90 |
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.
In E2, return the full rows (columns A to C) of everything stored in Dallas.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Item | Warehouse | On hand | |
| 2 | Copy paper | Reno | 140 | ||||
| 3 | Toner | Dallas | 12 | ||||
| 4 | Staplers | Reno | 35 | ||||
| 5 | Binders | Atlanta | 8 | ||||
| 6 | Envelopes | Dallas | 260 | ||||
| 7 | Labels | Reno | 6 | ||||
| 8 | Markers | Atlanta | 90 |
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.
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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Reorder | |
| 2 | Copy paper | Reno | 140 | ||
| 3 | Toner | Dallas | 12 | ||
| 4 | Staplers | Reno | 35 | ||
| 5 | Binders | Atlanta | 8 | ||
| 6 | Envelopes | Dallas | 260 | ||
| 7 | Labels | Reno | 6 | ||
| 8 | Markers | Atlanta | 90 |
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.
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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Warehouse | On hand | Item | |
| 2 | Copy paper | Reno | 140 | ||
| 3 | Toner | Dallas | 12 | ||
| 4 | Staplers | Reno | 35 | ||
| 5 | Binders | Atlanta | 8 | ||
| 6 | Envelopes | Dallas | 260 | ||
| 7 | Labels | Reno | 6 | ||
| 8 | Markers | Atlanta | 90 |
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.