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.
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.
| Formula | Excel shows | Case |
|---|---|---|
=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 .