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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
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.
2 Return the full rows (A to F) where Sales are 120 or more.
3 List the Reps with Widget orders in the East.
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 pricingCommon FILTER mistakes
- Condition ranges of a different height from the range, which gives an error.
- Leaving cells below the formula filled, so the result can't spill (#REF!).
- Using AND() for multiple conditions. In FILTER, give each condition as its own argument instead.
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.