The FILTER function in Excel
Last updated
FILTER returns the rows of a range that meet a condition, and the result spills into the cells below. For example, =FILTER(B2:B11,A2:A11="North") lists every rep of the North region. The list updates as soon as the data changes. FILTER needs Excel 2021, Excel 2024 or Microsoft 365.
FILTER syntax
=FILTER(array, include, [if_empty])
- array
- The range to return, e.g.
B2:B11for one column orA2:D11for whole rows. - include
- A test with one TRUE or FALSE per row, e.g.
A2:A11="North". It must have as many rows as array. Multiply tests with*for AND, add them with+for OR. - if_empty
- Optional. What to show when no row matches, e.g.
"No sales". Without it, FILTER returns #CALC!.
Practice FILTER here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Add up one region’s sales
In F2, use FILTER to keep only the sales of the North region from D2:D11, and wrap it in SUM to get their total.
| Region | Rep | Product | Sale ($) | North total ($) | |
| South | Ava | Laptop | 1,450.00 | ||
| North | Liam | Monitor | 820.00 | ||
| East | Noah | Printer | 1,980.00 | ||
| North | Mia | Laptop | 1,340.00 | ||
| West | Ethan | Desk chair | 610.00 | ||
| North | Olivia | Printer | 2,150.00 | ||
| South | Lucas | Monitor | 930.00 | ||
| East | Emma | Desk chair | 1,120.00 | ||
| North | Jack | Desk chair | 470.00 | ||
| West | Sofia | Laptop | 1,760.00 |
How to use FILTER, step by step
- Click the top-left cell of an empty area, with room below (and to the right for several columns), and type
=FILTER(. - Select the range to return, then type a comma.
- Write the test, such as
A2:A11="North"orA2:A11=G1to take the value from a cell. Put each test in parentheses if you combine several. - Add a message in quotes for when nothing matches, close the parenthesis and press Enter. The results spill into the cells below.
FILTER examples
List the reps of one region
=FILTER(B2:B11,A2:A11="North")
Returns the name of each North rep, one per row, starting in the formula cell.
Return whole rows
=FILTER(A2:D11,D2:D11>1000)
Returns all four columns of every sale over 1,000. The results spill down and to the right.
Two conditions (AND)
=FILTER(A2:D11,(A2:A11="North")*(D2:D11>1000))
Multiplying the tests keeps only North sales over 1,000. Use + instead of * to keep rows that pass either test.
Total, count or sort the result
=SUM(FILTER(D2:D11,A2:A11=G1,0))
Wrap FILTER in SUM, COUNTA or SORT to work with the list. The 0 makes the total 0 instead of #CALC! when the region in G1 has no sales.
Common FILTER errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #SPILL! | The cells where the results should spill aren’t empty, or the formula sits in an Excel table. | Clear the cells below (and to the right of) the formula, or move it outside the table. |
| #CALC! | No row meets the condition, so the result would be empty. | Add the if_empty argument, e.g. "No sales", or check the condition for a typo. |
| #VALUE! | include doesn’t have as many rows as array, e.g. A2:D11 with A2:A10="North". | Make both ranges start and end on the same rows. |
| #NAME? | Your version of Excel doesn’t have FILTER (Excel 2019 and earlier), or the name is misspelled. | Use Excel 2021, 2024 or Microsoft 365, or filter the table with the Filter button instead. |
FILTER function vs the Filter button
The Filter button (Data > Filter) hides rows of the table itself: quick for a look, but you have to reapply it when the data changes, and it shows one view at a time. The FILTER function copies the matching rows to another place and recalculates on its own, so you can keep several filtered lists side by side, take the condition from a cell, and feed the result to SUM or SORT.
Frequently asked questions
Which versions of Excel have the FILTER function?
Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In Excel 2019 and earlier, FILTER returns #NAME?.
How do I filter with several conditions?
Put each test in parentheses and multiply them for AND: =FILTER(A2:D11,(A2:A11="North")*(D2:D11>1000)). Add them with + for OR.
How do I sort the result of FILTER?
Wrap it in SORT: =SORT(FILTER(B2:B11,A2:A11="North")) sorts the names from A to Z.
Why does FILTER show #CALC!?
No row meets the condition. Add a third argument, such as "No sales", to show your own message instead.
Keep practising
All 5 FILTER exercises, with explanations and progress tracking.
Open the FILTER practice set