Excel FILTER Function
FILTER Function
The FILTER function is a premade function in Excel, which returns the rows of a range that meet one or more conditions.
The matching rows are placed where you type the formula. The original data is not changed.
This page is about the FILTER function, which you type in a cell. To hide rows with the Filter buttons in the header row, see the Excel Filter page.
It is typed =FILTER and has the following parts:
=FILTER(array, include, [if_empty])
Note: FILTER is available in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not available in Excel 2019 or earlier. If you type it in an older version, you get a #NAME? error.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Array: The range to filter, for example A2:E21. Leave out the header row.
Include: The condition. It is checked for every row, for example C2:C21="Fire". It must have the same number of rows as the array.
If_empty: Optional. The value to show if no rows meet the condition. If you leave it out and nothing matches, FILTER returns a #CALC! error.
How to use the FILTER function:
- Select a cell (
G2) - Type
=FILTER - Double click the FILTER command
- Mark the range to filter (
A2:E21) - Type (
,) - Mark the column to check (
C2:C21) - Type the condition (
="Fire") - Hit enter
Let's have a look at an example!
This is the same Pokemon table as on the XLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
FILTER does not return the header row. Copy the headers in A1:E1 and paste them into G1.
Use the FILTER function to list all the Pokemon with Fire as Type 1. Type the formula in G2:
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | ID# | Name | Type 1 | Type 2 | Total | |
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | 4 | Charmander | Fire | 0 | 309 | |
| 3 | 2 | Ivysaur | Grass | Poison | 405 | 5 | Charmeleon | Fire | 0 | 405 | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | 6 | Charizard | Fire | Flying | 534 | |
| 5 | 4 | Charmander | Fire | 309 | |||||||
| 6 | 5 | Charmeleon | Fire | 405 | |||||||
| 7 | 6 | Charizard | Fire | Flying | 534 | ||||||
| 8 | 7 | Squirtle | Water | 314 | |||||||
| 9 | 8 | Wartortle | Water | 405 | |||||||
| 10 | 9 | Blastoise | Water | 530 | |||||||
| 11 | 10 | Caterpie | Bug | 195 | |||||||
| 12 | 11 | Metapod | Bug | 205 | |||||||
| 13 | 12 | Butterfree | Bug | Flying | 395 | ||||||
| 14 | 13 | Weedle | Bug | Poison | 195 | ||||||
| 15 | 14 | Kakuna | Bug | Poison | 205 | ||||||
| 16 | 15 | Beedrill | Bug | Poison | 395 | ||||||
| 17 | 16 | Pidgey | Normal | Flying | 251 | ||||||
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | ||||||
| 19 | 18 | Pidgeot | Normal | Flying | 479 | ||||||
| 20 | 19 | Rattata | Normal | 253 | |||||||
| 21 | 20 | Raticate | Normal | 413 |
=FILTER(A2:E21, C2:C21="Fire")
FILTER checks C2:C21 row by row. Three rows have Fire: Charmander, Charmeleon and Charizard. FILTER returns these three rows, with all five columns.
The formula is typed only in G2. The result spills into G2:K4.
The result updates by itself. If you change the Type 1 of another Pokemon to Fire, it is added to the list.
Note: Empty cells in the array are returned as 0, not as empty cells. Charmander and Charmeleon have no Type 2, so J2 and J3 show 0.
Note: The cells that the result spills into must be empty. If they are not, FILTER returns a #SPILL! error.
Several Conditions: AND
To keep only the rows that meet two conditions, put each condition in parentheses and multiply them with *.
List the Bug Pokemon with a Total above 300. Replace the formula in G2 with this one:
| G | H | I | J | K | |
|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total |
| 2 | 12 | Butterfree | Bug | Flying | 395 |
| 3 | 15 | Beedrill | Bug | Poison | 395 |
=FILTER(A2:E21, (C2:C21="Bug")*(E2:E21>300))
There are six Bug Pokemon, but only Butterfree and Beedrill have a Total above 300.
Why multiply? Each condition gives TRUE or FALSE for every row. Excel counts TRUE as 1 and FALSE as 0. 1*1 is 1, and anything times 0 is 0. So only the rows where both conditions are TRUE are kept.
Note: The AND and OR functions do not work for this. They return one TRUE or FALSE for the whole range, not one for each row.
Several Conditions: OR
To keep the rows that meet at least one of the conditions, add the conditions with +.
List the Pokemon with Water or Fire as Type 1:
| G | H | I | J | K | |
|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total |
| 2 | 4 | Charmander | Fire | 0 | 309 |
| 3 | 5 | Charmeleon | Fire | 0 | 405 |
| 4 | 6 | Charizard | Fire | Flying | 534 |
| 5 | 7 | Squirtle | Water | 0 | 314 |
| 6 | 8 | Wartortle | Water | 0 | 405 |
| 7 | 9 | Blastoise | Water | 0 | 530 |
=FILTER(A2:E21, (C2:C21="Water")+(C2:C21="Fire"))
A row is kept when the sum is 1 or more. That means at least one of the conditions is TRUE.
The rows come in the same order as in the table. The Fire Pokemon come first, because they are higher up in the table. To change the order, use the SORT function.
If Nothing Is Found
There are no Psychic Pokemon in the table. If nothing matches, FILTER returns #CALC!:
| G | H | I | J | K | |
|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total |
| 2 | #CALC! |
=FILTER(A2:E21, C2:C21="Psychic")
Use the if_empty part to show your own message instead:
| G | H | I | J | K | |
|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total |
| 2 | No Pokemon found |
=FILTER(A2:E21, C2:C21="Psychic", "No Pokemon found")
Note: Text in a formula must be inside double quotes: " "
Filter Command vs FILTER Function
| Filter command | FILTER function | |
|---|---|---|
| How you use it | Click the filter buttons in the header row | Type a formula in a cell |
| The result | Rows that do not match are hidden in the table | A new list of the matching rows, where you type the formula |
| When the data changes | You must apply the filter again | The list updates by itself |
| Excel versions | All versions | Microsoft 365, Excel 2021, Excel 2024 and Excel for the web |
Use the Filter command to look through a table quickly. Use the FILTER function when you want the matching rows in their own list, for example on a report or a second sheet.
See also: UNIQUE lists the different values in a range, COUNTIFS counts the rows that match several conditions, and XLOOKUP returns only the first match.
