FILTER

How FILTER works

FILTER takes a range and a TRUE/FALSE mask the same height. Wherever the mask is TRUE, the matching row from the range is kept. Wherever it's FALSE, the row is dropped. The kept rows spill into the cells below the formula.

The include argument is usually a comparison on one of the columns - like A2:A100="Active" or B2:B100>1000. Excel evaluates the comparison row by row, producing a TRUE/FALSE array, which FILTER uses to pick rows.

Combine multiple conditions by multiplying their TRUE/FALSE arrays (* for AND) or adding them (+ for OR). FILTER itself doesn't have multiple-criteria slots like SUMIFS - the criteria all live inside the second argument.

= FILTER(array, include, [if_empty])

Arguments

FILTER basic: pull active employees

Same scenario as COUNTIF, different deliverable: instead of counting active employees, return the actual rows. FILTER walks the Status column, keeps rows where Status equals Active, and spills them.

=FILTER(A2:B11,B2:B11="Active")

• •A2:B11 -> the rows to subset (Name + Status, two columns)
• •B2:B11="Active" -> the mask: a TRUE/FALSE array, one per row
• •Spill -> the result fills as many rows as match, expanding cells below
• •Live -> add a new active row in source, the spilled output updates instantly

FILTER with comparison: pull big-ticket sales

Same shape as the basic case, the mask is a numeric comparison instead of a text equality. Useful for any 'pull just the rows that meet this threshold' question.

=FILTER(A2:B11,B2:B11>1000,"No matches")

• •A2:B11 -> the order log (id + amount)
• •B2:B11>1000 -> mask: TRUE for amounts over $1,000
• •"No matches" -> graceful fallback when nothing matches - shown as a single cell
• •Spill behavior -> result is N rows tall, where N is the count of matches

FILTER with AND: active engineers only

Multiple conditions combine inside the include argument. Multiply the TRUE/FALSE arrays for AND - row-wise multiplication treats TRUE as 1 and FALSE as 0, so only rows where every condition is TRUE survive (1*1 = 1).

=FILTER(A2:C11,(B2:B11="Engineering")*(C2:C11="Active"))

• •A2:C11 -> the full roster: name, department, status
• •(B2:B11="Engineering") -> first mask: TRUE for engineers
• •(C2:C11="Active") -> second mask: TRUE for actives
• •* between them -> multiplies element-wise, giving TRUE only where both are TRUE
• •Operator pattern -> * = AND, + = OR. Wrap each comparison in parentheses.

FILTER with OR: employees in two departments

Use + instead of * for OR: a row passes if any condition is TRUE. "Engineering OR Marketing" is two equality tests added together.

=FILTER(A2:B11,(B2:B11="Engineering")+(B2:B11="Marketing"))

• •(B2:B11="Engineering") -> first mask
• •(B2:B11="Marketing") -> second mask
• •+ between them -> row-wise addition: TRUE+FALSE=1, TRUE+TRUE=2, FALSE+FALSE=0
• •Excel treats non-zero as TRUE -> so rows matching either condition are kept

Live filter: subset by a cell input

Reference a cell inside the comparison and FILTER becomes interactive. Type a department name in cell E1 and the filtered roster updates instantly. The classic "search box" pattern.

=FILTER(A2:C11,B2:B11=E1,"No matches")

• •B2:B11=E1 -> compare each department to whatever the user typed in E1
• •Live -> edit E1, the spill updates
• •Empty E1 -> matches blank departments only - usually returns the if_empty fallback
• •Combine with FIND -> use FIND for partial-match: ISNUMBER(FIND(E1,B2:B11)) returns TRUE for any row containing the substring

Where people go wrong

1. 01
   Forgetting the if_empty argument

   When zero rows match the condition, FILTER returns #CALC! by default. Reports look broken because of one stray edge case.

   Fix: Always pass the third argument in production: =FILTER(array, include, "No matches") or "" for blank.
2. 02
   Wrong array shape between array and include

   If array has 50 rows and include has 49 (or 51), FILTER returns #VALUE!. The include array must match the array's height exactly.

   Fix: Use the same row range in both: =FILTER(A2:C100, B2:B100=...) - both 99 rows tall.
3. 03
   Spill blocked by content below

   FILTER expands downward. If a cell underneath your formula already has data, you get a #SPILL! error. The function refuses to overwrite existing content.

   Fix: Clear the cells below the formula or move the formula somewhere with empty space below it. The spill range is a contiguous block from the formula's cell down/right.
4. 04
   Using AND() / OR() inside include

   Writing =FILTER(A2:C11, AND(B2:B11="X", C2:C11="Y")) doesn't work the way you'd expect. AND() collapses the entire array to a single TRUE/FALSE - so FILTER either returns ALL rows or NONE.

   Fix: Use * for AND and + for OR with parentheses around each comparison: (B2:B11="X")*(C2:C11="Y"). These operate row-wise.

Notes

• FILTER is a dynamic-array function. The result spills into adjacent cells - you can't put other content in the spill range without breaking it.
• Available in Excel 365 and Excel 2021. Older versions don't have it; share files cautiously.
• Combine criteria with * (AND) or + (OR), wrapping each comparison in parentheses.
• The include argument must be a 1D boolean array the same height as the array's rows.
• When zero rows match, FILTER returns #CALC! unless you supply if_empty.
• Reference a cell in the include argument to make the filter live - typing in that cell re-runs the filter.
• Combine with SORT (=SORT(FILTER(...))) to get sorted filtered results in one formula.

What to learn next

Now prove it