You are staring at 2,000 rows of order history and need the 40 rows for one customer — without re-applying an AutoFilter that resets every time you click away. The Excel FILTER function does exactly this: it extracts matching rows into a live range that stays in the sheet and can feed other formulas.

The one FILTER formula you need to memorize

Every FILTER in this article is a rearrangement of a single pattern. You pass the data, a TRUE/FALSE test for each row, and optionally a message for the empty case:

=FILTER(array, include, [if_empty])
Argument What it is Example
array The data to filter, including the header row A1:E8
include A test that must equal TRUE for each row kept B2:B8="North"
if_empty Optional text shown when no row matches "no matches"

The include argument is the key idea: whatever you put there must return TRUE or FALSE for every row of array. That is why one three-argument shape handles single criteria, AND, OR, and partial text matching — the syntax never changes, only the test does.

The sample data: 7 rows of orders

Each example below works on this small order sheet, kept tiny so you can verify every result by hand:

Date Region Product Amount Status
2026-05-01 North Paper 1,240 Delivered
2026-05-02 South Toner 860 Pending
2026-05-03 North Ink 215 Shipped
2026-05-04 East Paper 980 Delivered
2026-05-05 South Ink 430 Cancelled
2026-05-06 West Toner 1,210 Delivered
2026-05-07 North Toner 760 Shipped

Example 1 — pull every order from one region

In cell G1, enter:

=FILTER(A2:E8, B2:B8="North")

Result: three rows spill into G1:I3 — the May 1 Paper order (1,240), the May 3 Ink order (215), and the May 7 Toner order (760).

Why it works: B2:B8="North" evaluates row by row into TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, and FILTER copies exactly the rows where the test is TRUE, in their original order. Because the result is a spill range, you never drag-fill or copy-paste — those three rows are live cells, not a static snapshot.

Example 2 — AND: stack two conditions with a multiplication sign

Now narrow it to North orders above 750:

=FILTER(A2:E8, (B2:B8="North")*(D2:D8>750))

Result: two rows — the 1,240 and the 760 orders. The 215 order drops out because it fails the second test, even though it is North.

Why it works: TRUE is 1 and FALSE is 0, so multiplying two tests yields 1 only when both are TRUE. This is the standard AND pattern, and it extends cleanly: (B2:B8="North")*(D2:D8>750)*(E2:E8="Delivered") keeps only one row, the May 1 order.

Example 3 — OR: swap the operator, keep the shape

Flip the requirement to North or South orders:

=FILTER(A2:E8, (B2:B8="North")+(B2:B8="South"))

Result: five rows — the three North orders plus the two South ones.

Why it works: addition is the OR operator in formula logic — any non-zero sum is treated as TRUE. A row can even satisfy both terms (a row matching two OR’d tests scores 2), and it is still kept. The two formulas in Examples 2 and 3 are identical in shape; only the operator between the tests changes, which is the fastest mental model for building complex criteria.

Example 4 — partial text matching with SEARCH

Product names in a real sheet will be messier than “Toner” — Toner XL, ink toner cartridge, and so on. Wildcard syntax like "*toner*" does not work in a plain comparison, so let SEARCH do the matching:

=FILTER(A2:E8, ISNUMBER(SEARCH("toner", C2:C8)))

Result: the two Toner rows (860 and 1,210). SEARCH locates “toner” anywhere inside each product name and returns its starting position, or an error if it is absent; ISNUMBER converts positions to TRUE and errors to FALSE. SEARCH is also case-insensitive, so “toner” matches “Toner” without you typing the exact capitalization.

Example 5 — a graceful message when nothing matches

Ask for a region that does not exist in the data:

=FILTER(A2:E8, B2:B8="Central")

With zero matches the formula spills nothing — a silent gap that can break any formula pointing at G1. The third argument fixes that:

=FILTER(A2:E8, B2:B8="Central", "no Central orders on this sheet")

Result: the single text cell no Central orders on this sheet appears where the spill would have started. Point the test at an input cell — =FILTER(A2:E8, B2:B8=$G$2, "nothing found") — and you have an extractor that behaves predictably for any value a user types.

Example 6 — filter and sort in one formula

Reports usually want the biggest values on top. Nest SORT inside FILTER and the ordering happens before the selection:

=FILTER(SORT(A2:E8, 4, -1), B2:B8="North")

Result: the three North rows, now ordered 1,240 → 760 → 215. SORT reads the whole array, orders it by column 4 (Amount) with -1 for descending, and FILTER then keeps only the North rows from that sorted list. Row order survives the filter, so there is no second sorting pass.

Where FILTER beats manual AutoFilter — and where it does not

AutoFilter (the arrows in a header row) is still the right tool for a one-time glance at your data. FILTER is the tool when the result has to live in the sheet:

Manual AutoFilter FILTER formula
Result location In place, rows hidden Spilled copy in empty cells
Survives closing Excel Filter state resets Formula stays, recalculates on open
Feedable to other formulas No Yes — wrap in INDEX, SUM, UNIQUE, …
Setup speed for a one-off check Faster Slightly slower to type

FILTER also has a clear boundary: it extracts rows. If you need one value keyed by another, that is lookup territory — compare your options in the XLOOKUP vs VLOOKUP guide. And if you only need a number, such as total North sales, skip row extraction entirely and let SUMIFS do it in a single cell; our SUMIFS date-range guide covers those patterns.

Five mistakes that break a FILTER formula

  1. Misaligned ranges. If array is A2:E8 but include is C3:C9, Excel evaluates without complaint — and every test lands on the wrong row. Keep both ranges starting on the same row and the same length.
  2. Wildcards in a plain comparison. C2:C8="*ton*" matches nothing, because normal equality has no wildcard support. Use ISNUMBER(SEARCH("ton", C2:C8)) instead.
  3. #SPILL! at the target cell. The result needs contiguous empty space to its right and below. Clear the destination block before testing, or move the formula to an empty area.
  4. Expecting blank cells to match a text test. An empty Status cell does not satisfy E2:E8="Delivered". If you specifically want the blanks, test for them: (E2:E8="").
  5. Hard-coding positions into a spill. Want the amount of the first North order? Wrap it — =INDEX(FILTER(A2:E8, B2:B8="North"), 1, 4) — instead of pointing at G2. Manual row numbers break the moment the data changes; see the INDEX MATCH examples guide for the pairing pattern.

Frequently Asked Questions

Which Excel versions support FILTER?

FILTER is a dynamic-array function, so it is available in Microsoft 365 (Windows, Mac, and Excel for the web) and in Excel 2021 for desktop. It is not in Excel 2019 or earlier — on those versions the classic workaround is an INDEX+SMALL array formula, or simply filter manually and copy the result as values.

What happens if FILTER finds no matching rows?

Without the third argument it spills nothing, which can leave a confusing empty area or break formulas that assume data at G1. Supply if_empty — for example =FILTER(A2:E8, B2:B8="North", "no matches") — and you get one clean, predictable cell instead.

Does FILTER update when my data changes?

Yes. It is a live formula, not a snapshot: add a row, edit a region, or change an amount, and the spill recalculates automatically. That is the core reason to prefer it over copy-paste — the extracted list is always the current list.

Can I filter first and then pull a single value?

Yes — wrap the filter in INDEX to grab one cell of the result: =INDEX(FILTER(A2:E8, E2:E8="Delivered"), 1, 4) returns the amount of the first delivered order. For a single value keyed by a lookup key, a plain lookup is usually the cleaner tool.

Wrap-up

FILTER is one syntax with a flexible middle argument: =FILTER(data, test, [empty message]). Master AND via *, OR via +, and SEARCH for partial text, and you can extract any slice of a sheet as live cells that stay current.

Want to skip the guesswork? Describe what you need in plain English and let ai-excel.net generate the formula for you.