Excel FILTER Function

FILTER returns a subset of a range based on conditions you define. The results spill automatically into adjacent cells — you write one formula and Excel fills in as many rows as match.

Syntax

FILTER(array, include, [if_empty])

Argument	What it means
array required	The range you want to filter — can include multiple columns.
include required	A TRUE/FALSE array the same height as array. Rows where this is TRUE are returned.
if_empty optional	What to return if no rows match. Defaults to a #CALC! error if omitted.

Basic example

Sales data in A2:C100 with Region in column B. Return only rows where Region is "North":

=FILTER(A2:C100, B2:B100="North", "No results")

Excel returns every matching row automatically — no Ctrl+Shift+Enter, no dragging.

Multiple criteria

AND (both conditions must be true)

Multiply the conditions together. Region is "North" AND Amount > 1000:

=FILTER(A2:C100, (B2:B100="North") * (C2:C100>1000), "No results")

OR (either condition can be true)

Add the conditions. Region is "North" OR "South":

=FILTER(A2:C100, (B2:B100="North") + (B2:B100="South"), "No results")

💡 Why multiply for AND, add for OR

TRUE = 1, FALSE = 0. Multiplying means both must be 1 (true) to get a non-zero result. Adding means either being 1 is enough.

Common errors

Error 1

#CALC! — no rows matched

Happens when your condition returns zero matches and you didn't set an if_empty value. Fix: always include a third argument like "No results".

Error 2

#SPILL! — something is blocking the output range

FILTER needs empty cells below and to the right to spill into. Clear whatever is occupying those cells.

⚠️ Availability

FILTER requires Excel 365, Excel 2021, or Excel for the web. It is not available in Excel 2019 or earlier. Use IFERROR + INDEX/MATCH as a fallback for older versions.

Can FILTER return results from a different sheet?

Yes. Use a full sheet reference: =FILTER(Sheet2!A2:C100, Sheet2!B2:B100="North", "No results")

How do I sort the FILTER results?

Wrap it in SORT: =SORT(FILTER(A2:C100, B2:B100="North"), 3, -1) — this filters first, then sorts the results by column 3 descending.

What is the difference between FILTER and AutoFilter?

AutoFilter hides rows visually but keeps all data in the sheet. FILTER is a formula that outputs a new, separate set of results — the original data stays untouched, and the output updates dynamically as source data changes.

Practice FILTER for real

Type it in a live spreadsheet, get instant feedback. No videos, no downloads.

Start practising free →