This page isn't translated yet, so its text is in English. Function names and formulas are shown in your Excel language.
Excel function · Dynamic arrays
FILTER
Returns the rows of a range that meet a condition.
What it does
FILTER keeps the rows where a condition is TRUE and spills them below the formula. Unlike the Filter button, it leaves the original data untouched and updates by itself.
Syntax
=FILTER(array; include; [if_empty])Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;
Arguments
- array
- The range to return rows from: one column or several.
- include
- A condition with one TRUE or FALSE per row, such as
B2:B5>=100. - if_empty
- What to return when no row matches, instead of
#CALC!.
Example
Which members have 100 points or more?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Member | Points | 100 or more | |
| 2 | Ana | 120 | Ana | |
| 3 | Ben | 80 | Kai | |
| 4 | Kai | 150 | ||
| 5 | Lia | 95 |
The formula in D2:
=FILTER(A2:A5;B2:B5>=100)The result spills into the cells below: Ana; Kai
The condition checks each row: Ana 120 and Kai 150 pass, Ben 80 and Lia 95 don't. The names that pass, Ana and Kai, spill into D2 and D3.
Common mistakes
- Making the condition a different height from the range, which gives
#VALUE!. - Combining conditions with
AND, which returns one value for the whole range. Multiply the conditions instead: (cond1)*(cond2). - Not planning for no matches. Give the third argument a message such as "none".
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | FILTER | , |
| Deutsch | FILTER | ; |
| Français | FILTRE | ; |
| Español (España) | FILTRAR | ; |
| Español (México) | FILTRAR | , |
| 日本語 | FILTER | , |
| 한국어 | FILTER | , |
| 繁體中文 | FILTER | , |
| ไทย | FILTER | , |
| ខ្មែរ | FILTER | , |
| Tiếng Việt | FILTER | ; |