Excel FILTER Function

FILTER extracts the rows or columns from a range that match a condition you specify, and returns them as a dynamic array that spills directly onto the sheet. Change the source data or the criteria, and the results update automatically — no re-running a filter, no clicking Apply. The one thing that trips people up: if nothing matches and you haven't supplied the third argument, FILTER returns a #CALC! error instead of a blank result.

Available in: Excel 365, Excel 2021. Not available in Excel 2019 or earlier.

Syntax

=FILTER(array, include, [if_empty])

Parameter	Required	Description
array	Yes	The range or array you want to extract results from. Can be a single column, a row, or a full table.
include	Yes	A TRUE/FALSE array marking which rows (or columns) to keep. Usually built from a logical expression like orders[Region]="West". Must have the same number of rows as array when filtering rows, or the same number of columns when filtering columns.
if_empty	No	What to display if nothing matches. Defaults to a #CALC! error if you leave it out.

Basic Example

You're tracking sales in an orders table with columns for Region, Rep, and Amount. You want to pull every order from the West region into its own list.

=FILTER(orders, orders[Region]="West", "No matches")

// orders               — the full table to search
// orders[Region]="West" — the condition: keep rows where Region equals "West"
// "No matches"          — what to show if zero rows match

FILTER checks every row in the Region column, builds a TRUE/FALSE array, and returns only the rows where the condition is TRUE. The result spills into as many rows and columns as needed below and to the right of the formula cell — you write one formula, not one per row.

How FILTER Works

Results spill automatically

FILTER is a dynamic array function. Enter it once and the output fills adjacent cells on its own — this is called spilling. You don't press Ctrl+Shift+Enter, and you don't copy the formula down. If another cell is in the way, FILTER throws #SPILL! instead of overwriting it.

Multiple conditions require boolean math

FILTER only takes one include argument, so combining conditions means building them yourself with * for AND and + for OR:

// AND: Region is West AND Amount is over 500
=FILTER(orders, (orders[Region]="West")*(orders[Amount]>500), "No matches")

// OR: Region is West OR Region is East
=FILTER(orders, (orders[Region]="West")+(orders[Region]="East"), "No matches")

Each condition evaluates to an array of 1s and 0s. Multiplying them mimics AND — every condition must be true for the row to survive. Adding them mimics OR — any single TRUE keeps the row in.

The original data never changes

Unlike the AutoFilter feature on the Data tab, FILTER doesn't hide rows or touch the source range at all. It reads the data and writes a fresh, separate result somewhere else. Delete the FILTER formula and your source table looks exactly as it did before.

You can return specific columns, not the whole table

Most examples pass an entire table into array, but you don't have to. Wrap CHOOSE around the column references to pull only the fields you need, in whatever order you want:

=FILTER(CHOOSE({1,2}, orders[Rep], orders[Amount]), orders[Region]="West", "No matches")

This returns just the Rep and Amount columns for West region orders, skipping Region and any other fields in the table.

Common Use Cases

Build a dropdown-driven report

Reference a cell instead of hardcoding a value, so the report updates when someone picks a different option from a data validation dropdown in F2.

=FILTER(orders, orders[Region]=F2, "No matches for " & F2)

Change F2 from "West" to "Midwest" and the entire extract refreshes without touching the formula.

Rank and filter in one formula

Combine FILTER with SORT to pull matching rows already ordered by size, useful for a "top performers in this region" view.

=SORT(FILTER(orders, orders[Region]=F2, "No matches"), 3, -1)

// FILTER — pulls rows for the selected region
// SORT   — reorders them by column 3 (Amount), descending

Filter on partial text matches

FILTER doesn't understand wildcards on its own, so pair it with ISNUMBER and SEARCH to catch text that contains a substring rather than matching it exactly.

=FILTER(orders, ISNUMBER(SEARCH("west", orders[Region])), "No matches")

This catches "West," "Southwest," and "West Coast" alike, since SEARCH looks for the fragment anywhere in the cell.

Filter by a rolling date window

Use a date function inside include to build a moving range, like orders placed in the last 30 days, without hardcoding a start date that goes stale.

=FILTER(orders, orders[OrderDate]>=TODAY()-30, "No orders in the last 30 days")

Because TODAY() recalculates daily, this formula never needs manual updating.

Handling Errors

FILTER throws two distinct errors, and they behave differently.

Common causes of #CALC!:

• No rows in array satisfy the include condition, and if_empty was omitted
• The condition references a value that doesn't exist anywhere in the data

Common causes of #SPILL!:

• Another cell is blocking the space FILTER needs to spill into
• FILTER is nested inside a function that doesn't support arrays touching merged cells

// Avoid — IFERROR wraps the whole formula and hides any error, not just a zero-row match
=IFERROR(FILTER(orders, orders[Region]=F2), "No matches found")

// Preferred — if_empty handles the zero-match case directly, no wrapper needed
=FILTER(orders, orders[Region]=F2, "No matches found")

#SPILL! isn't something IFERROR can catch — it happens before the formula fully resolves. Clear the cells in the spill range instead of wrapping the formula.

Notes & Gotchas

How do I use the FILTER function in Excel?

Type =FILTER( followed by the range you want to search, a logical condition built from that range, and an optional fallback value, then press Enter. Excel spills the matching rows automatically — there's no button to click and no dialog box involved, unlike the AutoFilter feature. The formula lives in one cell, but the results occupy however many cells the match requires.

What's the difference between FILTER and a basic Excel AutoFilter?

AutoFilter hides non-matching rows in place and changes only what's visible, not the underlying data structure. FILTER writes an entirely new, separate set of results elsewhere on the sheet and leaves the source table untouched. AutoFilter is manual — you reopen the dropdown and reapply it after the data changes. FILTER recalculates on its own the moment the source data or the criteria change.

How can I filter a range of data in Excel?

Pass any contiguous range — a table, a named range, or a plain cell reference like A2:D200 — as the array argument, and a matching TRUE/FALSE condition as include. FILTER works the same way whether the source is a formatted Excel table or a raw range; a table just keeps the reference stable if rows are added later. For a raw range, remember that include must cover exactly the same number of rows as array.

What happens when no matching values are found in the FILTER function?

FILTER returns whatever you specified in if_empty. If you left that argument out, it returns #CALC! instead of a blank cell or a zero. This is the single most common support question for FILTER, and the fix is always the same: add a third argument.

How do I filter dates or times in Excel?

Build the include condition using standard comparison operators against date cells, since Excel stores dates as serial numbers under the hood. Functions like MONTH(), YEAR(), or TODAY() slot into the same condition when you need relative or partial date logic, and time values follow the identical pattern since Excel stores them as decimal fractions of a day.

=FILTER(orders, orders[OrderDate]>=DATE(2026,1,1), "No orders since Jan 1, 2026")

// orders                          — the full table to search
// orders[OrderDate]>=DATE(2026,1,1) — condition: keep rows dated Jan 1, 2026 or later
// "No orders since Jan 1, 2026"    — fallback text if no rows qualify

Does FILTER slow down large workbooks?

Not noticeably on typical business data, but FILTER recalculates every time the source range changes, which adds up across dozens of large arrays on the same sheet. On data sets in the tens of thousands of rows with several nested FILTER formulas, you may notice a recalculation lag. If performance becomes an issue, consider filtering once into a helper table and referencing that instead of nesting FILTER calls three or four layers deep.

Related Functions

Function	Use this when...
SORT	You need the filtered results ordered by a column instead of in source order.
UNIQUE	You need distinct values from a range rather than rows matching a condition.
XLOOKUP	You need a single matching value, not every matching row.
COUNTIFS	You need a count of matching rows instead of the rows themselves.