How it works.
FILTER is a dynamic array function. It scans a data range and returns all rows that match a true/false condition. The result automatically "spills" down and across to adjacent cells, updating in real-time as the source data changes.
💡 In simple terms.
Sifts through a table of data and extracts only the rows that match your criteria, spilling the results down the sheet.
"Think of this like sifting sand through a sieve. Only the grains that match your rule pass through, while the pebbles stay behind."
Syntax guide.
Parameters
The range of cells you want to filter.
A logical array condition that evaluates to TRUE or FALSE for each row.
The value to return if no records meet the filter conditions.
Default: "No Matches"Interactive formula builder.
Fill in the input arguments below to construct your custom formula string. Copy it directly to paste into your Excel spreadsheets.
Best Practices
- To combine conditions, use multiplication `*` for AND logic (e.g. `(B2:B6="Red")*(C2:C6>50)`) and addition `+` for OR logic.
- Ensure cells below the filter formula are completely empty to let the array spill successfully.
Common Gotchas
- If the spill range is blocked by existing text or numbers, the formula returns a #SPILL! error. Clear the blocking cells to fix it.
- FILTER is a dynamic array function and is only supported in Excel 365 and Excel 2021+.