Excel 365 + Google Sheets · FILTER

Show only the rows you care about

Returns only the rows that meet your condition, and updates when the data changes.

The formula

=FILTER(A2:B5, B2:B5>1000)

What each part means

A2:B5What to return
B2:B5>1000Which rows to keep

Example

Before

	A	B	C	D	E
1	Rep	Sales		Rep	Sales
2	Laura	1,250
3	João	980
4	Priya	2,410
5	Tom	640

After

	A	B	C	D	E
1	Rep	Sales		Rep	Sales
2	Laura	1,250		Laura	1,250
3	João	980		Priya	2,410
4	Priya	2,410
5	Tom	640

Step by step

Click cell D2.
Type the formula: =FILTER(A2:B5, B2:B5>1000)
Press Enter.
The results fill the cells below by themselves (it’s a dynamic array formula). Don’t type anything in them.

Where it works

Excel 365 and Excel 2021 or newer
Google Sheets
Excel for the web (free)

The same formula in other languages

English FILTER
Español FILTRAR
Português FILTRO

Get 1 formula a day, free

Plus a free weekly planner template for Excel and Google Sheets. Unsubscribe in 1 click.

✓ Done! Check your inbox to confirm.

Want ready-made templates? See the Formulyo pack →

Keep learning

TEXTJOINMerge a whole list into one cell Find duplicatesSpot duplicates instantly GOOGLETRANSLATEGoogle Sheets translates for you SUMIFSAdd up only what matches your rules UNIQUERemove duplicates without deleting IFERRORHide every #DIV/0!