FormulasSign inOpen the generator
Lookup & reference

FILTER formula examples

Return only rows or columns that satisfy one or more conditions.

fxFILTER(array, include, [if_empty])
Open generator with this promptBack to library
Examples

Real spreadsheet prompts for FILTER

FILTER
"Return columns A through D only for rows where status in column D is Open."
=FILTER(A2:D100,D2:D100="Open","No open tasks")
Try this example
FILTER
"Return West region rows where revenue in column C is greater than 1000."
=FILTER(A2:D100,(A2:A100="West")*(C2:C100>1000),"No matches")
Try this example
Before you paste

FILTER compatibility and common errors

Excel, Google Sheets, and WPS

FILTER uses dynamic arrays in Google Sheets and current Excel. Older Excel and some WPS versions do not support spilling results.

Common mistakes

  • The include range must have the same height or width as the array being returned.
  • Existing values in the spill area can cause a #SPILL! error in Excel.
  • Multiple AND conditions must be combined into one Boolean include expression.
Related functions

Compare FILTER with the next formula in the workflow

XLOOKUPIFERRORCOUNTIFS