Dynamic ArrayLevel: ExpertAvailable in: Excel 365 / 2021+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Example 3
6. Power combo
7. Common errors and fixes
8. Related functions

FILTER returns all rows (or columns) that meet a condition, and the result updates live as data changes.

Syntax

=FILTER(array, include, [if_empty])

Argument	What it means
array	The data to filter.
include	TRUE/FALSE test with the same height, e.g. B2:B500=”North”.
if_empty	What to show when nothing matches.

Examples

Example 1

=FILTER(A2:E500, B2:B500="North", "None")

All North rows.

Example 2

=FILTER(A2:E500, (B2:B500="North")*(E2:E500>50000))

AND: multiply conditions.

Example 3

=FILTER(A2:E500, (B2:B500="North")+(B2:B500="East"))

OR: add conditions.

Power combo

=SORT(FILTER(A2:E500, E2:E500>50000), 5, -1)

Live “big deals” report sorted by amount.

Common errors and fixes

You see	Why, and the fix
#SPILL!	Something is in the way of the results — clear the cells below/right.
#CALC!	Nothing matched and if_empty is missing.

Related functions

SORT · UNIQUE · XLOOKUP · TEXTJOIN

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Keep reading

Excel Functions A–ZOFFSET Function in Excel: A Range That Moves or ResizesExcel Functions A–ZMATCH Function in Excel: Find the Position of a ValueExcel Functions A–ZROUND Function in Excel: Round to Any Number of Digits