Practice free

FILTER function

FILTER returns only the rows or columns that meet one or more conditions, spilling the results into neighboring cells. With no matches it returns #CALC! unless you supply an if-empty value.

Example

Read it as a sentence: look at the items in B2:B7 and keep only those whose Qty in D2:D7 is more than 10, which starts with Boxer cartridge.

Practice this in the app

Syntax

FILTER(array, include, [if_empty])

Arguments in [square brackets] are optional.

array (required)
Data to filter.
include (required)
A TRUE/FALSE array the same height (or width) as array.
if_empty (optional)
What to return when nothing matches.

Returns: The rows (or columns) that meet the condition.

One FILTER formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=FILTER({1,2,3},{1,0,1}) 1 Horizontal arrays
=FILTER(Items!B2:B7,Items!D2:D7>10) Boxer cartridge Basic
=FILTER(Items!B2:E7,(Items!C2:C7="Smoking")*(Items!D2:D7>10)) Clay pipe AND of conditions
=FILTER(Items!B2:B7,(Items!C2:C7="Papers")+(Items!D2:D7>15)) Candle OR of conditions
=FILTER(Items!B2:B7,Items!D2:D7>100,"none") none if_empty
=FILTER(Items!B2:B7,Items!D2:D7) Boxer cartridge Include is numbers
=FILTER(Items!B2:B7,Items!D2:D7>100) #CALC! Nothing matches
=FILTER(Items!B2:B7,Items!D2:D3>1) #VALUE! Include wrong size

The Items sheet these formulas read

Watch out for

  • Combine criteria with * for AND and + for OR, not AND()/OR().
  • With no matches you get #CALC! unless you provide if_empty.
  • include must have matching size or you get #VALUE!.

Where it works

Excel
FILTER arrived in Excel 2021. Older versions do not have it.
Google Sheets
Same name, but behaves differently. Sheets FILTER(range, condition1, [condition2...]) takes conditions directly and has no if_empty argument; wrap in IFERROR.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .