How to Use FILTER in Excel: Spill Examples
By Szabó Gergő · Updated
FILTER returns every row or column that meets a condition and spills the result into neighboring cells. It replaces many helper columns used for extracting matching records.
Syntax and arguments
=FILTER(array, include, [if_empty])- array
- The range to return, often several columns wide.
- include
- A Boolean array the same height or width as array.
- if_empty
- Optional value when no row meets the condition.
FILTER examples
Status is in C2:C100 and the full table is A2:E100.
=FILTER(A2:E100,C2:C100="Paid","No paid rows")Only rows whose status equals Paid spill below the formula.
Region is B2:B200 and amount is D2:D200.
=FILTER(A2:E200,(B2:B200=G2)*(D2:D200>=G3),"None")Multiplying two TRUE/FALSE arrays requires both region and amount to match.
Source data lives on Data!A2:F500.
=FILTER(Data!A2:F500,Data!F2:F500<>"","")Nonblank rows from the Data sheet spill on the reporting sheet.
Common mistakes
Include range size does not match
Use the same number of rows as the array, such as A2:E100 with C2:C100.
#SPILL! because a cell is blocked
Clear cells in the spill range or move the formula to an empty area.
OR logic multiplied instead of added
Use + between conditions for OR, and * for AND.
FILTER FAQ
Does FILTER work in Excel 2016?
No. FILTER requires Microsoft 365 or Excel 2021. Older files should use AutoFilter or helper columns.
How do I sort a FILTER result?
Wrap it in SORT, for example =SORT(FILTER(A2:C100,B2:B100="East"),3,-1).
Why do I get #CALC!?
No record matched and if_empty was omitted. Provide a fallback such as "None".