Excel FILTER Function

The FILTER function returns only the rows of a range that meet a condition: =FILTER(array, include, [if_empty]). It replaces messy array formulas and manual AutoFilter copy-paste with one formula that updates automatically when the source data changes.

What Is the Syntax of the FILTER Function?

FILTER takes the range to return, a TRUE/FALSE condition the same height as that range, and an optional value to show when nothing matches.

=FILTER(A2:C100, B2:B100="West")
' Returns every row in A2:C100 where column B equals "West"

=FILTER(A2:C100, C2:C100>500)
' Rows where the value in column C is greater than 500

How Do You Use Multiple Criteria in FILTER?

Multiply conditions with * for AND logic, and add them with + for OR logic — FILTER has no built-in AND/OR arguments.

' AND: region is West AND amount over 500
=FILTER(A2:C100, (B2:B100="West")*(C2:C100>500))

' OR: region is West OR region is East
=FILTER(A2:C100, (B2:B100="West")+(B2:B100="East"))

' Combined: (West OR East) AND over 500
=FILTER(A2:C100, ((B2:B100="West")+(B2:B100="East"))*(C2:C100>500))

Each parenthesized comparison produces an array of TRUE/FALSE values. Multiplying treats them as 1s and 0s, so only rows where every condition is 1 survive.

What Does the if_empty Argument Do?

The third argument is what FILTER returns when zero rows match; without it you get a #CALC! error.

=FILTER(A2:C100, B2:B100="Antarctica", "No matches")
' Returns the text "No matches" instead of #CALC!

How Does FILTER Spill Results?

You enter FILTER in one cell and the results spill into as many rows and columns as needed — the neighboring cells must be empty or you get a #SPILL! error.

=FILTER(A2:C100, C2:C100>500)
' Entered in E2, results fill E2:G? automatically

=SUM(E2#)
' The # operator references the whole spill range, however big it is

FILTER is one of the dynamic array functions, so it resizes live: add a matching row to the source and the spill grows on the next recalc.

Common Pitfalls

• Using AND()/OR() instead of * and +: AND(B2:B100="West", C2:C100>500) collapses to a single TRUE/FALSE, filtering everything or nothing. Always use * and +.
• #SPILL! errors: anything in the spill zone — even an invisible space — blocks the output. Clear the cells below and to the right.
• Mismatched range heights: the include array must be exactly as tall as the data array, or you get #VALUE!.
• Forgetting if_empty: a legitimate zero-match case shows #CALC! to your users. Pass "" or a message.