FILTER Function: syntax, examples, mistakes and translation notes
Filters a range using criteria.
Syntax
=FILTER(array, include, [if_empty])
Arguments
array
Range or array to return rows/columns from.
include
TRUE/FALSE array that decides what to keep.
if_empty
Optional value when no rows match.
Worked examples
=FILTER(A2:D100,D2:D100="Open","No rows")
Returns rows where column D is Open.
=FILTER(A2:C100,(B2:B100>=70)*(C2:C100="East"),"None")
Applies two AND-style conditions.
Practical tips
- Keep the include array aligned with the filtered array.
- Provide if_empty to avoid an avoidable no-result error.
- Leave enough blank cells for the result to spill.
Common mistakes
- Blocking the spill range with existing values.
- Using mismatched range sizes inside the include calculation.
Related functions
Frequently asked questions
What does FILTER do?
Filters a range using criteria.
Why might a FILTER formula return an unexpected result?
Blocking the spill range with existing values. Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.