FILTER practice exercises

FILTER returns only the rows of a range that meet your conditions, and the result updates automatically when the data changes. It is a live, formula-based version of the filter button.

Syntax

FILTER(range, condition1, [condition2, ...])
range
The rows (and columns) to return.
condition1
A column test of the same height, like B2:B9="East".
condition2...
More conditions are combined with AND.

The practice data

An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

Worked example

=FILTER(A2:A9,B2:B9="East")

Lists the Rep of every East order. Result:

Ana
Cara
Ben
Dev

Your turn: 3 exercises

Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.

1 List the Products sold by Ben.

H2

2 Return the full rows (A to F) where Sales are 120 or more.

H2

3 List the Reps with Widget orders in the East.

H2

Master FILTER in the full course

Lesson 14 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.

Start free See pricing

Common FILTER mistakes

FAQ

How do I FILTER with OR?

Add the conditions together: FILTER(A2:A9,(B2:B9="East")+(B2:B9="West")).

What if nothing matches?

FILTER returns #N/A. Wrap it in IFERROR to show a message instead.

Keep practicing