Excel FILTER Function (Dynamic Subset) – Examples & Practice
Practice the Excel FILTER function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Return rows from A2:C6 where C2:C6 equals "North"; if none, return "No match".
Formula Syntax
=FILTER(array, include, [if_empty])
- array: Range or array to return rows/columns from.
- include: Boolean array whose TRUE rows are kept (must align with array rows).
- [if_empty]: Optional value when no rows match.
What it does
FILTER returns the rows (or columns) of an array that meet one or more TRUE/FALSE conditions. Results spill into neighboring cells in dynamic Excel.
Excel FILTER Function Examples
Rows where region is North
=FILTER(A2:C6, C2:C6="North", "No match")
Returns all columns A:C for rows where C equals North, or the text No match if empty.
Numeric threshold
=FILTER(A2:C6, B2:B6>=100, "None")
Keeps rows whose B column meets the threshold.
AND logic
=FILTER(A2:C6, (A2:A6="South")*(B2:B6="Paul"), "None")
Both conditions must be true on the same row.
dynamic-array-data.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | Region |
| 2 | A | 120 | North |
| 3 | B | 340 | South |
| 4 | A | 560 | North |
| 5 | C | 225 | West |
| 6 | B | 90 | South |
| 7 | Output |
Input Formula
Need Help?
Tips
- Keep masks readable with LET on complex logic.
- Leave blank rows below spill formulas.
- Pair with **SORT** and **UNIQUE** for polished mini-reports.
FILTER Function Use Cases
- Live slices for dashboards without pivot refresh
- QA lists that show only exceptions
- Feeding SORT/UNIQUE downstream
- Replacing complex array formulas
- Teaching modern Excel data workflows
Common mistakes - FILTER function not working
- Include mask height not matching the array height
- OR logic written as
*instead of+ - Forgetting
if_emptyand getting #CALC! when nothing matches - Blocking spill with stray values below the formula
- Referencing entire columns on huge sheets without need
FAQ
What Excel versions support FILTER?
Microsoft 365 and Excel versions that support dynamic array formulas.
Why #SPILL!?
Something blocks the spill range—merged cells, values in the spill area, or insufficient space.
Can FILTER return multiple columns?
Yes. The array argument can include many columns; the boolean include mask must align row-wise.
How do I AND multiple rules?
Multiply boolean arrays: `(A2:A100="North")*(C2:C100>300)` inside FILTER.
FILTER vs AutoFilter?
FILTER is formula-driven and updates automatically; AutoFilter is a UI tool.
Comparison
| Tool | Type |
|---|---|
| FILTER | Formula spill |
| Advanced Filter | One-off UI |
| QUERY in Sheets | Different platform |
Example
=FILTER(A2:C6, C2:C6="North", "No match")
Advanced examples
FILTER then SORT
=SORT(FILTER(A2:C6, C2:C6="North"), 2, -1)
Sorts the filtered subset by column 2 descending.
Unique regions from filtered rows
=UNIQUE(FILTER(C2:C6, B2:B6>100))
Combines two dynamic functions for compact reporting.