Excel FILTER Function
FILTER returns only the rows or columns from an array that pass a TRUE/FALSE include test.
Use it to create dynamic lists, filtered reports, task views, or matching record sets without manually copying rows into a separate area.
FILTER syntax & arguments
Syntax
=FILTER(array, include, [if_empty])
-
1
array
RequiredThe range or array containing the rows or columns to return.
-
2
include
RequiredA TRUE/FALSE expression that explicitly defines the range to test, such as
B2:B20="West". If FILTER is returning rows, the test must return one TRUE or FALSE result for each row inarray; if returning columns, one result for each column. -
3
if_empty
OptionalThe fallback result to return when no values pass the include test. If omitted, Excel returns a
#CALC!error for no matches.
Example
=FILTER(A2:D20, B2:B20="West", "No matches")
Return rows from A2:D20 where the matching region in B2:B20 is West.
FILTER caveats
FILTER is powerful because it returns dynamic arrays, but that also means the include test and spill area both matter.
-
The include test must match the filtered direction
When FILTER returns rows,
includeneeds one TRUE or FALSE result for each row inarray. When it returns columns,includeneeds one result for each column. -
The include range must be written explicitly
Unlike certain conditional aggregation functions like SUMIF, FILTER does not infer which range to test from the
arrayargument. Define the array to test insideinclude, such asB2:B20="West". -
No matches need a fallback
If nothing passes the include test and
if_emptyis omitted, FILTER returns a#CALC!error. -
The result spills into nearby cells
FILTER may return several rows or columns. Blocked output cells can cause a spill error.
-
Errors in the include test propagate
If the TRUE/FALSE include array contains an error, FILTER returns that error instead of a filtered list.
Filtering messy text? Clean key fields with TRIM before building include tests that depend on exact text matches.
Intro FILTER practice problems
No intro FILTER problems are currently available.
Advanced FILTER practice problems
Use FILTER alongside other Excel functions in realistic, less-prescriptive challenges.
