Filtering rows with FILTER

1 / 4

Show only the orders with sales of at least F1, 470, as a list that updates by itself.

keyboard Typing formulas is easiest on a computer with a keyboard.

orders

filtered list

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How FILTER works

What does FILTER do?

It returns the rows of a table that meet a condition, as a live copy that spills into the cells below:
=FILTER(A2:C13,B2:B13="North")
What are its parts?
- array: the rows to return, usually the whole table without its headers.
- include: a test for each row, such as B2:B13="North" or C2:C13>=500. It must be as tall as the array.
- [if_empty]: what to show when no row passes. Without it you get #CALC!.
Why put the condition in a cell?

=FILTER(A2:C13,B2:B13=F1) lets anyone change F1 and watch the list update. That's a simple report or dashboard with no clicking.
How do I use two conditions?

Multiply the tests for AND, add them for OR:
- (B2:B13="North")*(C2:C13>=500): North and at least 500.
- (B2:B13="North")+(B2:B13="South"): North or South.
How is it different from the filter button?

The filter button hides rows in the original table. FILTER leaves the table alone and builds a second copy that updates by itself.

Know someone fighting with a spreadsheet? Send them this page.