What it does
FILTER returns matching rows from a range or array.
Syntax or pattern
=FILTER(array, include, [if_empty])✅
5 practical examples
1
Filter by region
Return rows for the East region.
=FILTER(Sales,Sales[Region]="East","No rows")Dynamic results spill into nearby cells.
2
Filter by date range
Return rows between two dates.
=FILTER(Sales,(Sales[Date]>=F1)*(Sales[Date]<=G1),"No rows")Multiplication applies AND logic.
3
Filter by selected category
Use a dropdown cell as criteria.
=FILTER(Products,Products[Category]=H2,"No products")Great for interactive sheets.
4
Filter multiple conditions with OR
Return East or West rows.
=FILTER(Sales,(Sales[Region]="East")+(Sales[Region]="West"),"No rows")Addition applies OR logic.
5
Filter and return one column
Return matching product names only.
=FILTER(Products[Name],Products[Category]=H2,"None")The array can be one column.
Common mistakes to avoid
- Make sure spill ranges have empty space below or beside them.
- Use Excel Tables where possible for expanding source data.
- Older Excel versions may not support all dynamic array functions.
Related Excel examples
FAQ
What is a spill formula?
A spill formula returns multiple cells from one formula entered in a single cell.