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.
#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.
Complex array formulas or Advanced Filter dialog
One formula, spills into every matching row
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.
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.
Syntax
FILTER takes 2 required arguments and 1 optional argument.
| Argument | Type | Description |
|---|---|---|
| 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
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.
Spill Zone
| Order ID | Customer | Region | Product | Total |
|---|---|---|---|---|
| ORD-1002 | TechFlow LLC | West | Ergo Chair | $1,245 |
| ORD-1007 | TechFlow LLC | West | Filing Cabinet | $378 |
| ORD-1012 | Summit Partners | West | Laptop Pro 15 | $3,897 |
| ORD-1015 | Pacific Ventures | West | Antivirus Pro | $1,068 |
| ORD-1020 | Summit Partners | West | Ergo Chair | $1,992 |
| ORD-1024 | TechFlow LLC | West | Monitor 27" | $658 |
| ORD-1027 | Pacific Ventures | West | Filing Cabinet | $567 |
Spills 7 rows. Add a West order to Source Data → this list grows. Delete one → it shrinks. Zero maintenance.
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).
| Order ID | Customer | Region | Product | Category | Total |
|---|---|---|---|---|---|
| ORD-1012 | Summit Partners | West | Laptop Pro 15 | Electronics | $3,897 |
| ORD-1024 | TechFlow LLC | West | Monitor 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.
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.
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.
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.
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.
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.
| Order ID | Customer | Product | Total |
|---|---|---|---|
| ORD-1012 | Summit Partners | Laptop Pro 15 | $3,897 |
| ORD-1020 | Summit Partners | Ergo Chair | $1,992 |
| ORD-1002 | TechFlow LLC | Ergo Chair | $1,245 |
| ORD-1015 | Pacific Ventures | Antivirus Pro | $1,068 |
| ORD-1024 | TechFlow LLC | Monitor 27" | $658 |
| ORD-1027 | Pacific Ventures | Filing Cabinet | $567 |
| ORD-1007 | TechFlow LLC | Filing 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
Electronics → 9 orders
West + Electronics → 2
Antarctica → 0
Total > $2000 → 3
Formula & Spill Count
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.
Row passes only if BOTH criteria are TRUE. TRUE × TRUE = 1, TRUE × FALSE = 0. Chain as many as you need: (A) * (B) * (C) * (D).
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.
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.
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.
| Aspect | FILTER (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
| Symptom | Cause | Fix |
|---|---|---|
| #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.
Related Functions
=UNIQUE(FILTER(...)) for deduplicated matches.=SORT(FILTER(...), col, -1).=TAKE(SORT(FILTER(...), 9, -1), 3). Excel 365 only.=UNIQUE(CHOOSE({1,2}, A:A, B:B)).Compatibility
| Platform | Supported | Version | Notes |
|---|---|---|---|
| Excel 2019 & earlier | ✗ | — | Not available — #NAME? error |
| Excel 2021 / 2024 | ✓ | Perpetual | Full support |
| Microsoft 365 | ✓ | Current | Full support with all spill features |
| Excel Online | ✓ | Current | Full support |
| Excel Mobile (iOS/Android) | ✓ | Current | Full support |
| Google Sheets | ✓ | All | Same syntax, identical behavior |
| LibreOffice Calc | ✓ | 7.4+ | Earlier versions: not available |
How to Use FILTER — Step by Step
- Click an empty cell with room to spill downward — ideally the top of a blank column.
- Type
=FILTER(— Excel auto-suggests as soon as you type "FIL". - Select the source array (the whole table or specific columns you want back), then type a comma.
- Type the include criteria as a boolean expression referencing a column:
D2:D100="West". Multiply with*for AND, add with+for OR. - Optionally add an if_empty fallback:
, "No matches"— this prevents#CALC!if nothing matches. Always do this in dashboards. - Close with
)and press Enter. The result spills into the cells below with a blue dashed border marking the spill zone. - 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.
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.
Templates That Use FILTER
Sales Dashboard
Region-selector cell drives a FILTER on the orders log; every dashboard tile reactively updates via spill# references. No macros.
USES FILTERCustomer Segmentation Tool
Multi-criteria FILTER on RFM scores — high-value / at-risk / new customers auto-populate their own tabs as source data grows.
USES FILTEROrder Tracking Sheet
Status-based views (Open, In Progress, Shipped) via FILTER with if_empty fallbacks. Add a status → new view appears.
USES FILTERInventory by Region
Warehouse-selector dropdown drives FILTER on the master SKU list. Low-stock and reorder alerts flow from the filtered slice.
USES FILTERHR Directory Search
Type a partial name or department into a search cell; FILTER with SEARCH-based include returns matching employees in real time.
USES FILTEREvent Attendee Filter
Ticket-tier and dietary-restriction filters combined via AND/OR patterns. Export view auto-generates from the filtered set.
USES FILTERExpense Report by Category
Category and date-range filters on the expense log. Monthly totals recompute from the filtered slice with SUM(spill#).
USES FILTERLive Report Builder
Configurable report tool where users pick criteria in dropdown cells; FILTER + SORT + UNIQUE chain generates the output.
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 →