The FILTER function in Excel
FILTER returns every row that matches a condition, as a live list: all North orders, all open invoices, every product below its reorder level. Change the data and the list updates by itself.
The result spills: you type one formula, and the matching values fill the cells below it. FILTER is available in Excel 365 and Excel 2021 or newer.
Syntax
=FILTER(array, include, [if_empty])
| Argument | What it means |
|---|---|
| array | The range to return values from: one column or several. |
| include | The condition, written over a range of the same height, e.g. B2:B6="North". |
| if_emptyoptional | What to show when nothing matches, instead of #CALC!. |
Examples
1. All orders from one region
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North orders | |
| 2 | 1001 | North | 250 | Paid | 1001 | |
| 3 | 1002 | South | 90 | Paid | 1003 | |
| 4 | 1003 | North | 420 | Open | 1005 | |
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
One formula in F2; the other two matches spilled into F3 and F4.
2. Two conditions: multiply them
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North, paid | |
| 2 | 1001 | North | 250 | Paid | 1001 | |
| 3 | 1002 | South | 90 | Paid | 1005 | |
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Each condition gives TRUE or FALSE per row; multiplying them means "both". Order 1003 is North but Open, so it's left out. For "either", add them with + instead.
3. When nothing matches
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | West orders | |
| 2 | 1001 | North | 250 | Paid | None | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
There are no West orders. Without the third argument the result would be #CALC!.
Common mistakes
#SPILL! error. Something is in the way of the cells the result wants to fill.
Fix: Clear the cells below (and to the right of) the formula.
Using AND() or OR() in the condition. They combine whole ranges into a single TRUE or FALSE.
Fix: Multiply conditions for "and", add them for "or".
#VALUE! error. The condition range has a different height than the array.
Fix: Make both cover the same rows, e.g. A2:A6 and B2:B6.
Frequently asked questions
Can FILTER return whole rows?
Yes. Give it several columns, e.g. =FILTER(A2:D6,B2:B6="North"), and the result spills across and down.
How do I sort the filtered list?
Wrap it in SORT: =SORT(FILTER(C2:C6,B2:B6="North"),1,-1).
Related functions
- SORTSORT returns a sorted copy of a range that updates when the data changes, ascending or descending, by any column.
- UNIQUEUNIQUE returns each value of a list once, removing duplicates with a formula that updates by itself.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.