FILTER in Excel: how to use it
FILTER fetches every row you want to see, whereas VLOOKUP stops at the first one. You do need Excel 2021 or 365.
Try the first exercises for freeIn the lesson The FILTER function
What does FILTER do?
FILTER fetches all the rows that meet a condition and puts them in a new list. Where VLOOKUP stops at the first match, FILTER gives you all of them.
You give three things: what you want to show, the condition, and if you like what should appear if nothing matches. For two conditions at once, multiply them: (A2:A6="Leeds")*(C2:C6>500). FILTER works in Excel 2021 and 365.
When do you use FILTER?
- All the revenue from one region: =FILTER(C2:C100,A2:A100="Leeds").
- The whole row of every laptop: =FILTER(A2:C100,B2:B100="Laptop").
- Everything above a limit, with a message if there's nothing: =FILTER(A2:C100,C2:C100>1000,"None").
- Two conditions at once: =FILTER(A2:C100,(A2:A100="Leeds")*(C2:C100>500)).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =FILTER(C2:C6,A2:A6="Leeds")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
FILTER | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | FILTER |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array | What you want to show: one column or whole rows. | C2:C6 |
include | The condition, such as A2:A6="Leeds". It has to be the same length as the array. | A2:A6="Leeds" |
[if_empty] | Optional: what you see if nothing meets the condition, such as "None". | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
FILTER step by step
E1 contains =FILTER(C2:C6,A2:A6="Leeds"). The first of the two revenues from Leeds. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1, with empty cells below it, and type FILTER with a bracket.
=FILTER( - Select what you want to show, the revenue in C2:C6, and type a comma.
=FILTER(C2:C6, - Type the condition: the regions in A2:A6 have to be equal to "Leeds".
=FILTER(C2:C6,A2:A6="Leeds" - Close the bracket and press Enter: the two revenue figures from Leeds appear underneath each other.
=FILTER(C2:C6,A2:A6="Leeds")
More examples
Whole rows
E1 contains =FILTER(A2:C6,B2:B6="Laptop"). The complete rows of the laptops, with region, product and revenue.
A message if there's nothing
E1 contains =FILTER(C2:C6,C2:C6>2000,"None"). No revenue is above 2000. Without the third argument, you'd get #CALC!.
Two conditions
E1 contains =FILTER(B2:B6,(A2:A6="Leeds")*(C2:C6>500)). Multiplying means AND: from Leeds and over 500. Only the laptop matches.
Where it often goes wrong
- Give a third argument, otherwise you get an error when nothing is found.
Practise this in the lesson The FILTER function. The first exercises are free.
Frequently asked questions
How is it different from the filter button?
The button hides rows in your table, the function makes a new list.