Dynamic arrays

Excel FILTER Function: Return Every Row That Matches

Returns every row of a range that meets your condition, as a live result that resizes itself.

FILTER is the function that finally lets a formula return more than one answer. Every lookup before it — VLOOKUP, INDEX+MATCH, even XLOOKUP — returns a single match. FILTER returns all of them, spilling down as many rows as it needs and shrinking again when the data changes.

You give it a range to filter and a condition that evaluates to TRUE or FALSE for each row. The result appears in the cell you typed it in and the cells below and beside it, outlined in blue. You cannot edit those spilled cells individually; the whole block belongs to the one formula, and it updates itself whenever the source data changes.

Conditions combine arithmetically rather than with AND and OR: multiply them for AND, add them for OR. It looks strange the first time, but it follows directly from TRUE being 1 and FALSE being 0.

Syntax

=FILTER(array, include, [if_empty])

Arguments

array Required: The range to filter. Can be a single column or the whole table — the result keeps the same number of columns.
include Required: A condition producing TRUE or FALSE for every row of array, such as C2:C6="Overdue". It must be exactly as tall as array.
if_empty Optional: What to return when nothing matches. Without it, no matches produces a #CALC! error.

The example data

Headers in row 1, data in A2:D6.

	A	B	C	D
1	Region	Product	Status	Amount
2	North	Drill	Overdue	1240
3	South	Lead	Paid	385
4	North	Goggles	Overdue	2100
5	South	Gloves	Paid	940
6	North	Drill	Paid	156

Worked examples

=FILTER(A2:D6, C2:C6="Overdue")

Result: Two full rows: North/Drill/Overdue/1240 and North/Goggles/Overdue/2100

The whole table filtered to overdue invoices. Four columns in, four columns out.

=FILTER(B2:B6, D2:D6>1000)

Result: Drill, Goggles

Filtering one column by a condition on another. Only the product names spill out.

=FILTER(A2:D6, (A2:A6="North")*(C2:C6="Paid"))

Result: One row: North/Drill/Paid/156

Two conditions ANDed by multiplying them. TRUE*TRUE is 1, anything else is 0.

=FILTER(B2:B6, D2:D6>5000, "None")

Result: None

Nothing is over 5000, so the third argument prevents a #CALC! error.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: Show only the invoices that are actually overdue

Common errors and how to fix them

#SPILL!

Why it happens: Something is already sitting in the cells the result needs. FILTER cannot overwrite existing content, so it refuses entirely rather than partially.

How to fix it: Clear the cells below and to the right of the formula. Excel outlines the blocked area in dashed blue when you select the cell.

#CALC!

Why it happens: Nothing matched the condition and no if_empty argument was given.

How to fix it: Add a third argument: =FILTER(range, condition, "No matches").

#VALUE!

Why it happens: The include condition is a different height from array — usually A2:A100 filtered by a condition on B2:B50.

How to fix it: Make both spans identical. Converting the source to a Table and using structured references keeps them in step automatically.

#NAME?

Why it happens: The Excel version does not have FILTER.

How to fix it: FILTER needs Microsoft 365 or Excel 2021+. In older versions this needs an advanced filter, a pivot table, or helper columns.

Tips worth knowing

Multiply conditions for AND, add them for OR: (a)*(b) means both, (a)+(b) means either.
Wrap it in SORT to order the results: =SORT(FILTER(...), 4, -1) sorts by the fourth column, descending.
Reference a spilled range elsewhere with the # suffix: F2# means 'whatever F2 spilled'.
FILTER recalculates live, so a filtered report built on it updates the moment someone adds a row to the source.

Frequently asked questions

What is the difference between FILTER and a normal filter?

The ribbon's filter hides rows in place and has to be reapplied when the data changes. FILTER is a formula that produces a separate live result, so you can build a filtered view on another sheet and leave the original untouched. It also updates itself.

How do I filter on two conditions?

Multiply them for AND: =FILTER(A2:D6, (A2:A6="North")*(D2:D6>1000)). Add them for OR: (A2:A6="North")+(A2:A6="South"). Each bracket must be a full-height comparison.

Why does my FILTER show #SPILL!?

There is data in the way. The result needs a clear block of cells to expand into, and anything already there — even a single stray space — blocks it. Select the formula cell and Excel will outline exactly which cells are in the way.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions