Filter Data with a Formula

Excel 365Excel 2021+Dynamic ArraySpills

The FILTER function extracts every row that meets a condition and spills the results into a live range — no AutoFilter clicks, no copy-paste, no helper columns. When the source data changes, the filtered output updates by itself.

Quick formula: to return every row of A2:C10 where region (column B) equals F1:

=FILTER(A2:C10, B2:B10=F1, "No matches")

The third argument is what to show when nothing matches — without it, an empty result returns #CALC!.

Functions used (tap for the full reference guide):

FILTER SORT

The example

An orders list. We want just the West rows pulled out automatically.

	A	B	C
1	Order	Region	Amount
2	1001	East	$120
3	1002	West	$80
4	1003	East	$200
5	1004	West	$300
6	1005	South	$50

The formula

Entered in a single cell, the result spills down and across to fill the matching rows:

=FILTER(A2:C6, B2:B6="West", "No matches") // returns orders 1002 and 1004

How it works

FILTER keeps only the rows where the condition is TRUE:

The first argument, A2:C6, is the data to return — all three columns.
The second argument, B2:B6="West", builds a column of TRUE/FALSE, one per row. FILTER keeps the rows marked TRUE.
The result spills automatically into the cells below and to the right; you only enter the formula once, in the top-left cell.
The third argument, "No matches", is returned instead of a #CALC! error when no row qualifies.

Try it: interactive demo

Live demo

Pick a region and (optionally) a minimum amount; the FILTER formula and the spilled result update live.

Region (F1)

Min amount

Variations

Filter on two conditions (AND)

Multiply conditions for AND — West and at least $100:

=FILTER(A2:C6, (B2:B6="West")*(C2:C6>=100), "No matches")

Filter on either condition (OR)

Add conditions for OR — West or South:

=FILTER(A2:C6, (B2:B6="West")+(B2:B6="South"), "No matches")

Filter and sort in one step

Wrap FILTER in SORT to return matches highest-amount first:

=SORT(FILTER(A2:C6, B2:B6="West"), 3, -1) // sort by col 3 descending

Pitfalls & errors

#CALC! — empty filter. No rows matched and you omitted the third argument. Always supply an if_empty value such as "No matches" or "".

#SPILL! — blocked range. Something is sitting in the cells the result needs to fill. Clear everything below and to the right of the formula cell.

Condition length must match. The condition array (B2:B6) must have the same number of rows as the data (A2:C6). Mismatched heights return #VALUE!.

Not in Excel 2019 or older. FILTER requires Excel 365 or 2021. Older versions show #NAME? — use AutoFilter or an Advanced Filter there instead.

Practice workbook

📊

Download the free Filter Data with a Formula practice workbook

The orders list with live FILTER examples for AND, OR, and FILTER+SORT, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I filter data with a formula in Excel?

Use FILTER: =FILTER(data_range, condition, "if empty"). It returns every row where the condition is TRUE and spills the results automatically. Available in Excel 365 and 2021.

How do I filter on more than one condition?

Multiply conditions for AND, e.g. (B2:B6="West")*(C2:C6>=100), and add them for OR, e.g. (B2:B6="West")+(B2:B6="South"). Each condition must be the same height as the data.

Why does FILTER return #CALC!?

No rows matched and you didn't supply the third argument. Add an if_empty value such as "No matches" or "" to handle empty results gracefully.

Does FILTER work in Excel 2019?

No. FILTER is a dynamic array function available only in Excel 365, Excel 2021, and Excel for the web. Older versions show a #NAME? error.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: FILTER · SORT