The FILTER function returns the rows of a range that meet a condition you set. Instead of copying, sorting, or using AutoFilter by hand, you write one formula and Excel spills the matching rows into the cells below. When the source data changes, the result updates on its own.
FILTER is a dynamic array function, available in Excel for Microsoft 365 and Excel 2021 and later. It is not in Excel 2019 or earlier.
Syntax
=FILTER(array, include, [if_empty])
- array - the range of rows you want to return.
- include - a condition that resolves to TRUE/FALSE for each row. Only TRUE rows come back.
- if_empty - optional. What to show when no row matches (otherwise you get a #CALC! error).
Return the rows for one region
With names in A2:A100, regions in B2:B100, and sales in C2:C100, return every row where the region is "East":
=FILTER(A2:C100, B2:B100="East", "No matches")
Excel returns all three columns for the matching rows and spills them down and across from the formula cell. Add a new "East" row to the source and the result grows automatically.
Filter on a number
Return the rows where sales are above 5000:
=FILTER(A2:C100, C2:C100>5000)
Two conditions with AND
Multiply conditions together for AND (both must be true). Region is "East" and sales above 5000:
=FILTER(A2:C100, (B2:B100="East")*(C2:C100>5000))
Each condition is a TRUE/FALSE array; multiplying turns them into 1s and 0s, and a row is kept only where the product is 1.
Two conditions with OR
Add conditions together for OR (either can be true). Region is "East" or "West":
=FILTER(A2:C100, (B2:B100="East")+(B2:B100="West"))
Return one column only
FILTER can pull a single column just as easily. Return only the names for the East region:
=FILTER(A2:A100, B2:B100="East")
Common Errors
- #CALC! - no rows matched and you did not supply if_empty. Add a third argument like "No matches".
- #SPILL! - a cell in the spill range is not empty. Clear the cells below and to the right of the formula.
- #VALUE! - the array and include ranges are different heights. They must have the same number of rows.
- #NAME? - your Excel version does not have FILTER. It requires Microsoft 365 or Excel 2021.
Frequently Asked Questions
What is the difference between FILTER and AutoFilter?
AutoFilter hides rows in place and is a manual, one-time action. FILTER is a formula that extracts matching rows into a new location and recalculates automatically when the data changes. FILTER also leaves the original data untouched.
How do I filter by two or more conditions?
Combine the conditions inside include. Multiply them for AND, add them for OR: (A="x")*(B>1) keeps rows where both are true, (A="x")+(A="y") keeps rows where either is true.
Why does FILTER return #CALC!?
No row met the condition and you did not give an if_empty value. Add a third argument, for example =FILTER(range, condition, "None"), and that text shows instead of the error.
Does FILTER work in Excel 2019?
No. FILTER is a dynamic array function introduced with Microsoft 365 and Excel 2021. In Excel 2019 or earlier, use an array formula with INDEX and SMALL, or AutoFilter, instead.
Can I sort the filtered result?
Yes. Wrap FILTER in SORT: =SORT(FILTER(A2:C100, B2:B100="East"), 3, -1) filters to the East region, then sorts by the third column in descending order.