FILTER returns only the rows of a range that meet your conditions, as a live list that rebuilds itself whenever the data changes. Type it in one cell and the matching rows spill into the cells below. It is the quickest way to build a "just the West region" or "orders over $2,000" view without touching the original data.
Syntax
=FILTER(range, condition1, [condition2, ...])
range
The data to return, e.g. A2:A7 for one column or A2:E7 for whole rows. Leave out the header row.
condition1
A column of TRUE/FALSE tests with the same number of rows as range, such as B2:B7="West" or E2:E7>2000.
condition2, ...
Optional. More conditions. A row is returned only when it passes all of them (AND).
Practice FILTER
Task 1 of 5
List the reps who work in the West region. Write one formula in G2 and let it spill down.
G2
fx
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
FILTER examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
A
B
C
D
E
1
Rep
Region
Product
Units
Revenue
2
Ava
West
Monitor
12
$2,400.00
3
Liam
East
Keyboard
30
$1,500.00
4
Maya
West
Mouse
45
$900.00
5
Noah
North
Monitor
8
$1,600.00
6
Zara
East
Monitor
15
$3,000.00
7
Omar
West
Keyboard
22
$1,100.00
Sales by rep
Formula
Result
What it shows
=FILTER(A2:A7, B2:B7="West")
Ava
Maya
Omar
Reps in the West region, one per row.
=FILTER(A2:E7, E2:E7>=2000)
Ava
West
Monitor
12
2400
Zara
East
Monitor
15
3000
Whole rows for orders of $2,000 or more.
=FILTER(A2:A7, C2:C7="Monitor", D2:D7>10)
Ava
Zara
Two conditions: both must be true (AND).
=FILTER(A2:A7, (B2:B7="East")+(B2:B7="North"))
Liam
Noah
Zara
Adding the tests makes an OR: East or North.
=FILTER(A2:A7, B2:B7="South")
#N/A
No rows match, so FILTER returns #N/A.
=IFERROR(FILTER(A2:A7, B2:B7="South"), "No matches")
No matches
IFERROR swaps the #N/A for a message.
=SORT(FILTER(A2:E7, B2:B7="West"), 5, FALSE)
Ava
West
Monitor
12
2400
Omar
West
Keyboard
22
1100
Maya
West
Mouse
45
900
West orders, sorted by revenue, highest first.
AND and OR conditions in FILTER
Every extra condition you pass to FILTER is combined with AND. For OR, put the tests inside one condition and add them: (B2:B7="East")+(B2:B7="North"). Each test gives 1 for a match and 0 otherwise, so the sum is above zero when at least one matches. You can mix both: =FILTER(A2:E7, (B2:B7="East")+(B2:B7="North"), E2:E7>1000) means "East or North, and over $1,000".
FILTER vs a filter view vs QUERY
Filter views (Data › Create a filter) hide rows in place. Good for browsing, but they don't give you a separate list you can reference in other formulas.
FILTER builds a separate, live copy of the matching rows. Easy to read and easy to combine with SORT, UNIQUE and COUNTA.
QUERY can filter, choose columns, sort, group and total in one formula. Reach for it once you need several of those at once.
Tips and common mistakes
The range and every condition must cover the same rows. A2:A7 with B2:B8 gives a #VALUE! "mismatched range sizes" error.
#REF! means the results can't spill because something is in the way. Clear the cells below (and to the right) of the formula.
Text comparisons in FILTER ignore case, so B2:B7="west" also matches "West".
For partial text matches, use SEARCH inside the condition: FILTER(A2:A7, ISNUMBER(SEARCH("mon", C2:C7))).
Use open-ended ranges like A2:A and B2:B so new rows are picked up automatically.