The Excel FILTER function, explained interactively.
FILTER keeps the rows of an array where a boolean expression evaluates to TRUE and spills the survivors into the cells below. It’s the dynamic-array replacement for AutoFilter — a live, formula-driven view that recomputes on every data change.
How to use FILTER
- Pick an empty cell with blank space below (and to the right if filtering multiple columns). FILTER spills just like UNIQUE.
- Write
=FILTER(and point at the array you want filtered — the range whose rows will survive or disappear. - Add the include boolean array — a comparison on a sibling column (
B2:B100="Ava") or a compound expression. - Always add if_empty — a fallback value like
"—". Skip it and an empty result returns#CALC!.
Try the demo — flip include to the “no matches” option and watch the if_empty fallback "—" appear in E2.
array where include is TRUE. One formula in one cell spills every matching row into the range below.array — FILTER doesn’t project.B2:B8="Ava" or C2:C8>=100.#CALC! — that’s the single most common gotcha. Plain "—" or "" works for most dashboards.| A | B | C | E | |
|---|---|---|---|---|
| 1 | Title | Owner | Priority | Filtered |
| 2 | Ship auth fix | Ava | High | Ship auth fix |
| 3 | Write docs | Ben | Medium | Migrate DB |
| 4 | Fix typo | Chloe | Low | Monitor outage |
| 5 | Migrate DB | Ava | High | |
| 6 | Logo tweak | Dev | Low | |
| 7 | Monitor outage | Ben | High | |
| 8 | Plan roadmap | Chloe | Medium |
FILTER syntax and arguments
Three arguments — the range, the boolean test, and the fallback. See Microsoft’s official FILTER reference for the canonical specification.
array — if you pass A2:C100, the output has 3 columns too. To return only some columns, pass only those columns as array, or wrap with CHOOSECOLS.array. Usually a comparison on a sibling column (B2:B100="Ava"). Compound with * for AND or + for OR — not the AND/OR functions, which collapse the array.#CALC! on empty results — a frequent surprise. Plain "—" or0 keeps dashboards readable. Rubric of thumb: always supply this argument in production formulas.FILTER examples
Four patterns that cover nearly every FILTER you’ll ever write.
Example 1: FILTER by a single condition
The canonical case — show only rows that match one criterion.
Returns every row where column B is Ava. Output preserves all three columns of the source. Without the "—" fallback, an empty result returns #CALC!.
Example 2: FILTER with AND (multiple conditions)
Combine boolean arrays with element-wise multiplication.
Each comparison yields TRUE/FALSE per row; multiplying produces 1 only when both are TRUE. Do not useAND() — it collapses to a single scalar. Addition (+) produces OR logic instead.
Example 3: FILTER combined with SORT and UNIQUE
The modern dynamic-array idiom — filter, dedupe, and sort in one expression.
Reads inside-out: FILTER keeps active rows, UNIQUE collapses duplicates, SORT alphabetises. Each function handles one concern; the composition replaces what used to be a pivot or a helper column.
Example 4: FILTER with a cell-driven condition
Build a dashboard where the filter value lives in an input cell the user can change.
E1 holds the filter value. Type a name, the filtered list recomputes instantly. Pair with UNIQUE to build a dropdown of valid filter values.
Common FILTER errors and fixes
Four failure modes, each with what to check and how to recover.
FILTER returns #CALC! on empty matches
Cause: the include expression returns all FALSE and no if_empty argument was supplied.
Always provide a fallback: =FILTER(range, condition, "—"). This is the single most common FILTER error; treat the third argument as required in production formulas.
FILTER returns #VALUE! when using AND or OR
Cause: AND() and OR() collapse arrays to a single scalar, so FILTER can’t apply them per-row.
Replace with element-wise arithmetic: (A:A="x")*(B:B="y") for AND, (A:A="x")+(B:B="y") for OR. Keep the parentheses — they’re not optional.
FILTER returns #SPILL!
Cause: cells below (or to the right of) the formula aren’t empty, so FILTER has nowhere to write its output.
Clear the blocking cells or move FILTER to a location with enough empty space. Merged cells also block spilling. Hover the blue dashed outline to see exactly which cell is in the way.
FILTER output misses rows you expected
Cause: comparison tripping on whitespace, case, or text-vs-number mismatch between the filter value and the source column.
Wrap the source with TRIM for whitespace,LOWER for case normalisation, or VALUE to coerce stored-as-text numbers. If the condition is a literal, quote exactly: "Active" won’t match "active ".
FILTER vs SORT, UNIQUE & IF
Four dynamic-array patterns. Pick based on whether you need to drop rows, reorder them, collapse duplicates, or branch per row.
| Function | Operation | Preserves row count | Typical use |
|---|---|---|---|
| FILTER | Drop rows that fail a test | No — output can be shorter | Live filtered view of a table |
| SORT | Reorder rows by a key | Yes — same rows, new order | Alphabetise, rank, pair with UNIQUE |
| UNIQUE | Drop duplicates | No — output can be shorter | Dedupe, or find exactly-once values |
| IF | Branch per row / return a fallback | Yes — always full-height | Per-row decision (not a filter) |
The composition rule: SORT(UNIQUE(FILTER(...))) reads inside-out. FILTER drops rows, UNIQUE collapses duplicates, SORT imposes order. Use IF only for per-row decisions — it won’t drop rows. Old-style AutoFilter remains useful for ad-hoc exploration; FILTER wins the moment the view needs to be live or embedded in another formula.
FILTER frequently asked questions
6.01Why does FILTER return #CALC!?▸
#CALC! appears when no rows match the include expression and no if_empty value is supplied. Add a third argument — =FILTER(range, condition, "—") — so the formula has something to show when the filter is empty.
6.02How do I filter by multiple conditions (AND) in FILTER?▸
Multiply the boolean arrays: =FILTER(A2:C100, (B2:B100="Ava")*(C2:C100="High"), "—"). Each expression returns TRUE/FALSE per row; multiplying coerces to 1/0, so both must be TRUE for the product to survive. Addition (+) produces OR logic.
6.03Can FILTER return specific columns instead of all of them?▸
Yes, but indirectly. Pass only the column you want as the array argument: =FILTER(A2:A100, B2:B100="Ava"). To pick several non-adjacent columns, wrap with CHOOSECOLS: =CHOOSECOLS(FILTER(A2:C100, B2:B100="Ava"), 1, 3).
6.04Why does FILTER return #VALUE! when I use AND?▸
Because AND() collapses a range to a single boolean. You want element-wise multiplication instead. Replace =FILTER(A:A, AND(B:B="x", C:C="y")) with =FILTER(A:A, (B:B="x")*(C:C="y")).
6.05What version of Excel has FILTER?▸
Excel 365 and Excel 2021 or later. Older versions don’t support dynamic arrays at all — use AutoFilter, a PivotTable, or INDEX/SMALL array tricks there. Google Sheets has always had FILTER.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.