What FILTER does
FILTER returns only the records that meet one or more logical tests. It is one of Excel's dynamic array functions, so the results spill into neighboring cells automatically.
Practical examples
Filter rows for one region
=FILTER(A2:D20,C2:C20="West","No matches")This returns only the rows from A2:D20 where the region in column C is West.
Filter rows with two conditions
=FILTER(A2:D20,(B2:B20="Open")*(D2:D20>=1000),"No matches")Use * to combine conditions with AND logic. This example returns only open items with values of at least 1000.
Common mistakes and notes
The include array must align with the data
If array has 19 rows, the include argument must evaluate to 19 TRUE/FALSE results. Misaligned dimensions cause errors.
FILTER spills into adjacent cells
If cells below or beside the formula are not empty, Excel may return #SPILL!. Clear the spill area or move the formula.
Use * for AND and + for OR
When you combine conditions inside FILTER, multiplication acts like AND and addition acts like OR. That pattern is common in dynamic array formulas.