How to Use the FILTER Function in Excel
The Filter button hides rows in your data. The FILTER function is different: it creates a separate, live list of matching rows somewhere else, and it updates automatically whenever the data changes. It's perfect for dashboards, reports and anything you'd otherwise copy and paste by hand.
FILTER is available in Microsoft 365, Excel 2021 and later, and Excel for the web.
The formula
=FILTER(what to return, condition, what to show if nothing matches)
A simple example
Your sales data is in A2:D100, with the region in column C. To list every sale in the North:
=FILTER(A2:D100, C2:C100="North")
Type it into one cell and press Enter. The results spill into as many rows and columns as they need, with a thin blue border around them. You only ever edit the first cell.
The rows are in the same order as the original data.
Point it at a cell
Instead of typing "North" into the formula, refer to a cell:
=FILTER(A2:D100, C2:C100=G1)
Now type a different region in G1 and the list changes instantly. Combine it with a drop-down list in G1 and you've got an interactive report.
Handle no results
If nothing matches, FILTER returns a #CALC! error. Add the third argument to show a message instead:
=FILTER(A2:D100, C2:C100=G1, "No results")
More than one condition
AND: both must be true
Multiply the conditions with *, each in its own brackets:
=FILTER(A2:D100, (C2:C100="North")*(D2:D100>500))
This shows sales in the North and over 500.
OR: either can be true
Add the conditions with +:
=FILTER(A2:D100, (C2:C100="North")+(C2:C100="South"))
This shows sales in the North or the South.
Other useful conditions
• Text that contains a word: ISNUMBER(SEARCH("laptop", B2:B100))
• Dates after a certain date: A2:A100>DATE(2026,1,1)
• Not blank: C2:C100""
• Dates after a certain date: A2:A100>DATE(2026,1,1)
• Not blank: C2:C100""
Sort the results
Wrap FILTER in SORT:
=SORT(FILTER(A2:D100, C2:C100="North"), 4, -1)
The 4 sorts by the 4th column of the results, and -1 means largest first.
Return only some columns
To show only certain columns, use CHOOSECOLS (Microsoft 365):
=CHOOSECOLS(FILTER(A2:D100, C2:C100="North"), 1, 4)
returns just columns 1 and 4.
Use a Table for data that grows
If your data is a Table called Sales, write:
=FILTER(Sales, Sales[Region]="North")
New rows added to the table are included automatically.
Troubleshooting
#SPILL! Something is in the way of the results. Even a single space in a cell counts. Click the warning icon, choose Select Obstructing Cells and clear them. FILTER also can't spill inside a Table, so put it outside.
#CALC! Nothing matched. Add the third "if empty" argument.
#VALUE! The ranges are different sizes. Make sure the condition range has the same number of rows as the data.
#NAME? Your version of Excel doesn't have FILTER. Use AutoFilter or Advanced Filter instead.
Wrapping up
=FILTER(data, condition, "No results") gives you a live filtered list in one cell. Use * for AND, + for OR, and wrap it in SORT to put the results in order.
