FILTER Function in Excel — The Spill Revolution (2026) | Sheets & Cells
⚡ Dynamic Array Starters

FILTER Function in Excel

Returns rows from a range that match your criteria — as a live, spilling formula. The function that killed Advanced Filter dialogs, made reactive dashboards possible without macros, and rewrote what "everyday Excel" looks like. One formula, many rows out.

Modern Excel Only
FILTER requires Microsoft 365, Excel 2021+, Excel Online, Google Sheets, or LibreOffice 7.4+. Excel 2019 and earlier will show #NAME?. This is a modern-Excel-only function — check your version before deploying to shared workbooks.

⚡ The Spill Revolution — What FILTER Actually Changed

Understanding FILTER means understanding what it replaced. The way you extract matching rows in Excel changed fundamentally in 2019/2020 with the dynamic array update.

Before 2020 — Legacy Excel

Complex array formulas or Advanced Filter dialog

{=IFERROR(INDEX($A$2:$F$100,SMALL(IF($D$2:$D$100="West",ROW($D$2:$D$100)-1),ROW(A1)),1),"")}
Required Ctrl+Shift+Enter, dragged down column by column, broke when the source grew, opaque to anyone reading the workbook later. Or: use the Advanced Filter dialog — a one-time snapshot with no reactivity.
2020+ — Modern Excel

One formula, spills into every matching row

=FILTER(A2:F100, D2:D100="West")
Type once in ONE cell. Excel auto-fills every row that matches — a live "spill zone" with a blue dashed border. Add a row to source → filter grows. Delete a match → filter shrinks. No macros, no arrays, no ceremony.
The paradigm shift: single-cell formulas that return many cells. This is called spilling. FILTER is the poster child but SORT, UNIQUE, SEQUENCE, and RANDARRAY all work the same way. Learning one teaches you all five.

Quick Answer

FILTER returns rows from an array that pass a boolean condition. Live formula — updates when source data changes. Supports multi-column criteria via AND (multiply) and OR (add) boolean logic.

=FILTER(array, include, [if_empty])

Example: =FILTER(A2:F100, D2:D100="West") returns every row where column D equals "West" and spills into the cells below. Multi-criteria: =FILTER(A2:F100, (D2:D100="West") * (E2:E100="Electronics")).

⚡ Dynamic Arrays Practice Workbook

All 5 FILTER examples plus UNIQUE and SORT patterns on 30 realistic Q1 2026 sales orders.

↓ Download .xlsx (Free)
Filedynamic-arrays-2026.xlsx
Size~30 KB
Sheets6 tabs
CoversFILTER · UNIQUE · SORT
2020
Introduced
2–3
Arguments
365 / 2021+
Required
100+
Templates Use It

Syntax

FILTER takes 2 required arguments and 1 optional argument.

ArgumentTypeDescription
array Required The range or array of rows to filter. Can be one column or many. This is what FILTER returns from.
include Required A boolean array with the same height as array. Row is included when the boolean is TRUE (or non-zero). Typically a comparison: D2:D100="West". Multiply for AND, add for OR.
if_empty Optional What to return when NO rows match. Without this, an unmatched filter throws #CALC!. Use "No matches" or 0 for defensive dashboards.

Spill behavior: FILTER returns a range. Type it in one cell — Excel auto-spills the result into as many rows as match. The top-left cell holds the formula; the rest are read-only spill cells.

5 Worked Examples

Example 01

Filter orders from the West region

The bread-and-butter FILTER. Reference the whole table for the array, then a boolean array (column comparison) for the include.

=FILTER(OrdersTable, OrderRegion="West")

Spill Zone

Order IDCustomerRegionProductTotal
ORD-1002TechFlow LLCWestErgo Chair$1,245
ORD-1007TechFlow LLCWestFiling Cabinet$378
ORD-1012Summit PartnersWestLaptop Pro 15$3,897
ORD-1015Pacific VenturesWestAntivirus Pro$1,068
ORD-1020Summit PartnersWestErgo Chair$1,992
ORD-1024TechFlow LLCWestMonitor 27"$658
ORD-1027Pacific VenturesWestFiling Cabinet$567

Spills 7 rows. Add a West order to Source Data → this list grows. Delete one → it shrinks. Zero maintenance.

Example 02

Multi-criteria AND: West region AND Electronics category

Multiply boolean arrays for AND logic. Each * combines conditions; a row is included only if ALL conditions are TRUE (both booleans multiply to 1).

=FILTER(OrdersTable, (OrderRegion="West") * (OrderCat="Electronics"))
Order IDCustomerRegionProductCategoryTotal
ORD-1012Summit PartnersWestLaptop Pro 15Electronics$3,897
ORD-1024TechFlow LLCWestMonitor 27"Electronics$658

Only 2 rows match both conditions. Any row failing EITHER criterion is excluded. The * is the Excel-formula equivalent of a SQL AND.

Example 03

OR criteria: Electronics OR Furniture

Add boolean arrays for OR logic. Each + combines conditions; a row is included if ANY condition is TRUE. Warning: if a row matches BOTH, the sum could be 2 — still truthy, so still included. Excel doesn't care about the number, only zero-vs-nonzero.

=FILTER(OrdersTable, (OrderCat="Electronics") + (OrderCat="Furniture"))

Spills 16 rows — 9 Electronics + 7 Furniture. Add for OR, multiply for AND. You can chain both for complex logic like (A=1) * ((B="X") + (B="Y")): A must equal 1, AND B is X or Y.

Example 04

The [if_empty] argument — no more #CALC! errors

The 3rd argument specifies what to return when NO rows match. Without it, unmatched filters throw #CALC! — which breaks any downstream formula or chart. Always include this in production dashboards.

=FILTER(OrdersTable, OrderRegion="Antarctica", "No orders in that region")

Returns the fallback text ("No orders in that region") instead of #CALC!. For numeric downstream use, return 0 instead of a string. Defensive by default.

Example 05

FILTER + SORT combo: West orders sorted by Total descending

Dynamic arrays compose naturally. Nest FILTER inside SORT — filter first, then order the results. This is the pattern behind every "top orders in region X" dashboard tile.

=SORT(FILTER(OrdersTable, OrderRegion="West"), 9, -1)
Order IDCustomerProductTotal
ORD-1012Summit PartnersLaptop Pro 15$3,897
ORD-1020Summit PartnersErgo Chair$1,992
ORD-1002TechFlow LLCErgo Chair$1,245
ORD-1015Pacific VenturesAntivirus Pro$1,068
ORD-1024TechFlow LLCMonitor 27"$658
ORD-1027Pacific VenturesFiling Cabinet$567
ORD-1007TechFlow LLCFiling Cabinet$378

Same 7 West rows as Ex 01, biggest first. To get just the top 3, wrap with INDEX: =INDEX(SORT(FILTER(...), 9, -1), SEQUENCE(3), SEQUENCE(1, 9)).

Interactive Playground

Assume 30-row Orders table with columns for Region, Category, Total
West region → 7 orders
Electronics → 9 orders
West + Electronics → 2
Antarctica → 0
Total > $2000 → 3
Formula & Spill Count
=FILTER(Orders, Region="West")
spills 7 rows
=FILTER(Orders, (Region="West")*(Cat="Electronics"))
spills 2 rows
=FILTER(Orders, Region="Antarctica")
#CALC!
=FILTER(Orders, Region="Antarctica", "None")
"None"
=FILTER(Orders, Total > 2000)
spills 3 rows

The Dynamic Array Starters

Three modern-Excel functions that spill results. Learn one, understand all three — they compose naturally.

Boolean Array Patterns — AND, OR, NOT

FILTER's include argument is a boolean array. Combine multiple booleans with arithmetic — this is the syntax that lets one function handle infinite criteria complexity.

*AND — multiply booleans
=FILTER(Orders, (Region="West") * (Cat="Electronics"))

Row passes only if BOTH criteria are TRUE. TRUE × TRUE = 1, TRUE × FALSE = 0. Chain as many as you need: (A) * (B) * (C) * (D).

+OR — add booleans
=FILTER(Orders, (Cat="Electronics") + (Cat="Furniture"))

Row passes if EITHER criterion is TRUE. TRUE + FALSE = 1, TRUE + TRUE = 2 (still truthy). Excel treats any non-zero as inclusion, so overlapping matches don't cause duplicates.

−NOT — subtract from 1, or use <>
=FILTER(Orders, Region<>"West")

Simplest NOT: use the inequality operator. For complex exclusions inside a chain, use 1 - (condition): e.g. (A=1) * (1 - (B="X")) means A=1 AND B is NOT X.

()Grouping — parentheses are essential
=FILTER(Orders, (A=1) * ((B="X") + (B="Y")))

Without parentheses, operator precedence would combine things wrong. Always wrap each condition in () and each logical group in another (). Once you internalize the pattern, complex logic reads like sentences.

FILTER vs the Advanced Filter Dialog — Live Formula vs One-Shot

Excel has had an "Advanced Filter" dialog for decades. FILTER didn't remove it — it made it obsolete for most cases. Here's when each wins.

AspectFILTER (formula)Advanced Filter (dialog)
Reactivity Live — updates when source changes One-shot — re-run manually every time
Location Cell formula — travels with the workbook Menu action — no formula trail, hard to audit
Setup Type one formula Create a criteria range, open dialog, select ranges, click OK
Complex logic (AND/OR) Direct — * (AND), + (OR) Requires a multi-row criteria range
Compatibility Excel 365 / 2021+ only Works in Excel 2003 onward
Unique values only Wrap with UNIQUE: =UNIQUE(FILTER(...)) Built-in checkbox in the dialog
Extract to new location Automatic — spills into destination cell Dialog can copy to another range but doesn't stay linked

Rule of thumb: modern Excel + reactive workbook needed → FILTER. Legacy Excel or one-time report extraction → Advanced Filter dialog.

Common Errors

SymptomCauseFix
#NAME? You're on Excel 2019 or earlier — FILTER doesn't exist. Or you typo'd the name. Upgrade to Excel 365, 2021, or 2024. On legacy Excel, use INDEX+SMALL+ROW array formulas or the Advanced Filter dialog.
#CALC! Zero rows match your criteria and you didn't provide an if_empty argument. Add the 3rd argument: =FILTER(array, include, "No matches") or 0. Always include this in production formulas.
#SPILL! Something is blocking the spill zone — a value in a cell where the result needs to go, or the sheet edge is too close. Clear the blocking cells. If you don't know where they are, hover the spill indicator — Excel points at the block. Move the formula to a cell with room to grow.
#VALUE! The include array has a different height than array. Or contains error values. Both must have matching row counts. Check that criteria columns cover the exact same range as the source data. Wrap error-prone criteria in IFERROR(criteria, FALSE).
Multiple matches but only one row appears You wrote the formula in an existing table (Ctrl+T Table) — spill is disabled inside legacy Excel Tables. Move the FILTER formula OUT of the Table into a regular cell. Or convert the Table back to a range. Modern spill and legacy Tables don't mix well.
FILTER returns duplicates I didn't expect Multiple rows in the source genuinely match your criteria — that's not deduplication behavior of FILTER (that's UNIQUE's job). Wrap with UNIQUE: =UNIQUE(FILTER(array, criteria)). Filters what matches, then removes duplicates from the result.

⚡ Practice on Real Sales Data

Every FILTER pattern — AND/OR/NOT criteria, if_empty defense, FILTER+SORT combo — on 30 Q1 2026 orders across 4 regions and 4 categories.

↓ Get Workbook

Related Functions

Compatibility

PlatformSupportedVersionNotes
Excel 2019 & earlier✗—Not available — #NAME? error
Excel 2021 / 2024✓PerpetualFull support
Microsoft 365✓CurrentFull support with all spill features
Excel Online✓CurrentFull support
Excel Mobile (iOS/Android)✓CurrentFull support
Google Sheets✓AllSame syntax, identical behavior
LibreOffice Calc✓7.4+Earlier versions: not available

How to Use FILTER — Step by Step

  1. Click an empty cell with room to spill downward — ideally the top of a blank column.
  2. Type =FILTER( — Excel auto-suggests as soon as you type "FIL".
  3. Select the source array (the whole table or specific columns you want back), then type a comma.
  4. Type the include criteria as a boolean expression referencing a column: D2:D100="West". Multiply with * for AND, add with + for OR.
  5. Optionally add an if_empty fallback: , "No matches" — this prevents #CALC! if nothing matches. Always do this in dashboards.
  6. Close with ) and press Enter. The result spills into the cells below with a blue dashed border marking the spill zone.
  7. Reference the spill in other formulas using # notation: =COUNTA(B2#) counts rows in your FILTER result. This makes downstream formulas react to filter changes automatically.

Explore More Functions

⚡ One More Time — Grab the Workbook

Every FILTER pattern plus UNIQUE and SORT combos, on 30 realistic sales orders. Free, one file, six sheets.

↓ Download Free

Frequently Asked Questions

Why does FILTER return #NAME?

Your Excel version doesn't support FILTER. It was introduced in Microsoft 365 in 2020 and added to perpetual Excel 2021. Excel 2019 and earlier will always show #NAME? because the function simply doesn't exist there.

Options: upgrade to Excel 365/2021+ (recommended), use the Advanced Filter dialog for one-time extractions, or use the legacy {=IFERROR(INDEX(..., SMALL(IF(...), ROW(...)-1)), ""} array formula pattern (Ctrl+Shift+Enter). The legacy version works but is unmaintainable.

How do I filter with multiple criteria?

Multiply boolean arrays for AND: =FILTER(data, (col1="X") * (col2="Y")). Add for OR: =FILTER(data, (col1="X") + (col1="Y")). Combine with parentheses for complex logic.

Every condition should be wrapped in its own (), and each logical group in another (). Example: "region is West AND (category is Electronics OR Furniture)" becomes (Region="West") * ((Cat="Electronics") + (Cat="Furniture")).

What does #CALC! mean and how do I fix it?

Zero rows match your criteria. Without the 3rd argument, FILTER can't return an empty result, so it errors. Add an if_empty fallback: =FILTER(data, criteria, "No matches") or 0 for numeric context.

Best practice: always include an if_empty in production formulas. What you return depends on downstream use — a message for display, zero for math, empty string ("") for concatenation, or a blank array constant for further processing.

What does #SPILL! mean?

Something is in the way of the spill zone — a value in a cell where FILTER wants to place its result. Excel refuses to overwrite existing content, so it errors instead.

Fix: hover the spill indicator in the top-left cell — Excel highlights the blocking cell. Clear it. If your table is close to the sheet edge, move the formula to a location with room to grow downward.

Can I filter by more than one column?

Yes — multi-column criteria is what multiplication (AND) and addition (OR) achieve. Each condition references its own column: =FILTER(data, (RegionCol="West") * (CategoryCol="Electronics") * (TotalCol>1000)).

There's no limit on how many conditions you can chain. Readability degrades past 3-4 — at that point, use LET to name intermediate booleans: =LET(w, RegionCol="West", e, CategoryCol="Electronics", FILTER(data, w * e)).

How do I filter and remove duplicates in one formula?

Wrap FILTER with UNIQUE: =UNIQUE(FILTER(customer_col, region_col="West")). This filters first, then removes duplicate values from the result.

Common use: dropdown source of "customers who bought from a specific region" or "products sold in a category". Wrap the whole thing with SORT for alphabetical: =SORT(UNIQUE(FILTER(...))) — the standard dropdown-source pattern.

How do I get just the top N matching rows?

Three approaches. Classic: =INDEX(SORT(FILTER(data, criteria), col, -1), SEQUENCE(N), SEQUENCE(1, cols)). Excel 365 modern: =TAKE(SORT(FILTER(data, criteria), col, -1), N). LET-friendly: name the sorted-filter result, then TAKE.

TAKE is much more readable but requires the newer TAKE function. INDEX+SEQUENCE works in any dynamic-array Excel.

What's the difference between FILTER and XLOOKUP?

XLOOKUP returns ONE row/value that matches a lookup key. FILTER returns ALL rows that match a boolean criteria. If you know there's exactly one match and you want a single value → XLOOKUP. If there could be multiple matches → FILTER.

They also differ in criteria: XLOOKUP takes a lookup value; FILTER takes a boolean expression. FILTER is more flexible for complex criteria like ranges, arithmetic comparisons, or OR logic. XLOOKUP is optimized for the common "look up this key, give me the matching row" case.

Can I use FILTER inside a regular Excel Table?

Not directly — spill is disabled inside legacy Ctrl+T Tables. If you write a FILTER formula in a Table column, only the first row of the result appears (no spilling).

Workarounds: put the FILTER formula in a cell OUTSIDE the Table, or convert the Table back to a range. Modern spill and legacy Tables were designed at different times; Microsoft hasn't unified them yet.

How do I reference a FILTER result in another formula?

Use the spill reference operator #. If your FILTER formula is in cell B2, then B2# refers to the ENTIRE spilled result (all rows, all columns). Perfect for downstream formulas that should react to filter changes.

Examples: =ROWS(B2#) counts spilled rows. =SUM(INDEX(B2#, 0, 5)) sums column 5 of the result. =COUNTIF(source, B2#) pairs with a spilled unique list for a live pivot.

Does FILTER slow down large workbooks?

FILTER is generally fast — Excel's dynamic array engine is optimized for it. Performance concerns typically come from the SIZE of what you're filtering (millions of rows) or the number of FILTER formulas (hundreds referencing the same range).

Best practices: filter down as early as possible in a formula chain (FILTER first, then SORT, then wrap with UNIQUE if needed). Avoid volatile functions like NOW() or RAND() inside criteria. For truly huge datasets (500K+ rows), consider Power Query for the initial reduction, then FILTER for the reactive display layer.

Is FILTER available in Google Sheets?

Yes — Google Sheets has had FILTER since well before Microsoft added it to Excel. Same name, same syntax, same behavior. If you learn Excel's FILTER, you already know Google Sheets' FILTER.

The main compatibility gap is the modern Excel-only functions like TAKE, DROP, and TEXTSPLIT that don't yet exist in Sheets. FILTER, UNIQUE, SORT, and SEQUENCE all work identically across both platforms.

Build Reactive Dashboards Without Writing a Single Macro

Excel Wizard writes FILTER patterns — multi-criteria AND/OR chains, if_empty defense, FILTER+SORT+UNIQUE cascades — from a plain-English prompt. Turn any static workbook into a reactive dashboard, right inside Excel.

Get Excel Wizard Add-in →