Function guides ยท Lookup
FILTER function
Returns the rows of a range that meet a condition, spilling the results into neighboring cells.
Syntax
=FILTER(array, include, [if_empty])
| Argument | What it is |
|---|---|
array | The range or table to filter. |
include | A TRUE/FALSE test, one value per row, such as B2:B9>300. |
if_empty | Optional. What to return when no row qualifies. Without it you get #CALC!. |
What FILTER does
FILTER returns the rows of a range that meet a condition. It is the formula version of the filter button, and unlike the button, it updates when the data does.
You give it the data and a test that yields TRUE or FALSE for each row. The matching rows come back as a block that spills out of the formula cell into the empty cells beside and below it.
Because the result is dynamic, it can feed other functions directly: SORT to order it, SUM to total it, ROWS to count it.
It is a Microsoft 365 and Excel 2021 function. Earlier versions do not have it.
FILTER examples
1. All rows for one region
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Sale | ||||
| 2 | West | Dana | 450 | West | Dana | 450 | |
| 3 | East | Luis | 900 | West | Mei | 620 | |
| 4 | West | Mei | 620 | West | Kai | 275 | |
| 5 | North | Omar | 310 | ||||
| 6 | West | Kai | 275 |
The include test A2:A6="West" produces TRUE or FALSE for each row, and FILTER keeps the TRUE rows. The answer is not one cell. It spills down and across to fill as many cells as the result needs.
2. Two conditions with multiplication
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Rep | Sale | |||
| 2 | West | Dana | 450 | Dana | 450 | |
| 3 | East | Luis | 900 | Mei | 620 | |
| 4 | West | Mei | 620 | |||
| 5 | North | Omar | 310 | |||
| 6 | West | Kai | 275 |
Multiply the tests to require both: West and over 300. Use plus for either one. Only Dana (450) and Mei (620) qualify, and Kai's 275 is dropped.
3. A message when nothing matches
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Rep | ||
| 2 | West | Dana | No reps | |
| 3 | East | Luis |
No rep is in the South, so the filter would return a #CALC! error. The third argument supplies text instead, which is easier to read in a report.
Common FILTER mistakes
- #SPILL! Something is in the way of the spill range, a typed value or another formula. Clear the cells below and to the right of the formula.
- #CALC! when nothing matches. Add the third argument to return a friendly value.
- Using it in older Excel. FILTER is in Microsoft 365 and Excel 2021. In Excel 2019 and earlier it returns #NAME?.
- Include test the wrong size. The include array must have as many rows as the data.
A2:A6againstA2:C9returns #VALUE!. - Using AND and OR inside the test. AND and OR collapse the arrays to a single answer. Use * for and, + for or.
FILTER on Excel job tests
Prompts such as "list all orders from the West region" or "show employees with salary over 60,000 without using the filter button" want FILTER. Put one formula in the top-left cell of the output area and leave room underneath and to the right for the spill.
Common extensions: sort the result with SORT(FILTER(...),3,-1), and return only some columns by choosing a narrower array. Clear any cells in the way first, or you get #SPILL!.
If the test runs in an older Excel, the answer changes to AutoFilter, Advanced Filter, or INDEX with SMALL and IF. Read the instructions for the version.
Related functions
FILTER questions
- What is a spill?
- When a formula returns several values, Excel writes them into the cells below and to the right. The formula lives in the first cell.
- How do I filter with two conditions?
- Multiply for AND:
(A2:A9="West")*(C2:C9>300). Add for OR. - Why do I get
- Cells in the output area are not empty. Clear them.
- Is FILTER available in Excel 2019?
- No. It is in Excel 2021 and Microsoft 365.
Practice FILTER
Try it in a working grid: the free lesson FILTER: return the rows that match (module 19 of Analyst) has checked FILTER exercises.
Practice this function