=FILTER(A2:C7,B2:B7="North") returns every row of A2:C7 whose region in column B is North. You type it in one cell and the matching rows spill into the cells below and to the right. Change a region in column B to North, or a North to South, and the list updates.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Cara | North | 200 | |
| 4 | Cara | North | 200 | Finn | North | 60 | |
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Only E2 holds a formula. The other filled cells in E:G are its spilled result: click F3 and you see that it belongs to the formula in E2. If something is typed into that area, FILTER shows #SPILL! instead of the rows (see #SPILL! errors).
FILTER syntax
=FILTER(array, include, [if_empty])
arrayis what you want back: one column, several columns or the whole table.includeis a condition with one TRUE or FALSE per row ofarray, such asB2:B7="North". It must have exactly as many rows asarray. (To filter columns instead, give it one value per column.)if_emptyis what to show when no row matches. Without it, an empty result is the #CALC! error.
FILTER needs Excel 2021, Excel 2024 or Microsoft 365. In Excel 2019 and older it shows #NAME?, and the Filter button on the Data tab is the way to filter there. Google Sheets has FILTER too, and there each condition can also be given as a separate argument.
Text comparisons are not case sensitive: B2:B7="north" matches North. FILTER keeps the rows in their original order; sorting the result is a separate step, shown further down.
Filter by a cell value
Hard-coding "North" in the formula means editing the formula every time. Put the value in a cell and compare with the cell instead. Pick another region in F1 and the result follows:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Region | North | ||
| 2 | Ann | North | 120 | ||||
| 3 | Ben | South | 80 | Ann | North | 120 | |
| 4 | Cara | North | 200 | Cara | North | 200 | |
| 5 | Dan | East | 150 | Finn | North | 60 | |
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
West has no rows, so picking it shows the if_empty text, No sales.
Numbers work the same way. C2:C7>=F1 with 100 in F1 keeps every row with sales of at least 100, and C2:C7>F1 makes it strictly greater than.
FILTER with multiple criteria (AND)
To keep a row only when two conditions are both true, multiply them. This returns North rows with sales over 100:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Cara | North | 200 | |
| 4 | Cara | North | 200 | ||||
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Ann (120) and Cara (200) pass. Finn is North but his 60 is not over 100, so he is left out.
Why multiply: each condition is a column of TRUE and FALSE, and in arithmetic TRUE counts as 1 and FALSE as 0. A row gets 1 only when every factor is 1, so * works as AND. Each condition needs its own parentheses, and you can chain as many as you like: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).
AND() does not work here. AND(B2:B7="North",C2:C7>100) reduces the whole range to one single TRUE or FALSE instead of one per row, so FILTER gets the wrong shape.
FILTER with OR
Add the conditions to keep a row when at least one of them is true. This returns North and East rows:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Cara | North | 200 | |
| 4 | Cara | North | 200 | Dan | East | 150 | |
| 5 | Dan | East | 150 | Finn | North | 60 | |
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
A row that meets both conditions adds up to 2, and FILTER keeps any row whose result is not 0, so the sum works as OR. You can mix the two: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) means (North or East) and over 100. Here that returns Ann, Cara and Dan.
FILTER returns #CALC! when nothing matches
When no row passes, FILTER has nothing to return. Without a third argument that is the #CALC! error; with one, you get your own text:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | No if_empty | With if_empty | |
| 2 | Ann | North | 120 | #CALC! | No match | |
| 3 | Ben | South | 80 | |||
| 4 | Cara | North | 200 | |||
| 5 | Dan | East | 150 | |||
| 6 | Eve | South | 95 | |||
| 7 | Finn | North | 60 |
#CALC! The calculation has no result, for example a FILTER that matches nothing.E2 shows #CALC! and F2 shows No match. Change B3 from South to West and both formulas return Ben. To show nothing at all, use an empty string: =FILTER(A2:A7,B2:B7="West","").
This sheet also shows filtering a single column: array is A2:A7, so only names come back. To get some columns of a table but not all, wrap the result in CHOOSECOLS: =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) returns names and sales without the region. CHOOSECOLS needs Microsoft 365 or Excel 2024.
Sort the FILTER result
FILTER returns rows in the order they appear in the table. Wrap it in SORT to order the result: here North rows sorted by sales, largest first. The 3 is the column of the result to sort by, and -1 means descending.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Cara | North | 200 | |
| 3 | Ben | South | 80 | Ann | North | 120 | |
| 4 | Cara | North | 200 | Finn | North | 60 | |
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Cara (200) comes first, then Ann (120) and Finn (60). To return only the top rows, wrap it once more in TAKE: =TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2) keeps the first two (TAKE needs Microsoft 365 or Excel 2024). SORT and SORTBY covers the other sort options.
FILTER rows that contain text
FILTER has no wildcards, so B2:B7="*th*" looks for the literal text *th*. To keep rows whose name contains some text, test each cell with SEARCH, which returns a position when the text is found and an error when it is not, and wrap it in ISNUMBER:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Dan | East | 150 | |
| 4 | Cara | North | 200 | ||||
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
This returns Ann and Dan: SEARCH ignores case, so "an" also matches the An of Ann. Use FIND instead of SEARCH for a case-sensitive match.
Practice: FILTER with two conditions
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | ||||
| 3 | Ben | South | 80 | ||||
| 4 | Cara | North | 200 | ||||
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Your turn: In E2, return the rows (all three columns) of South reps with sales over 85.
Practice: FILTER by a cell, with a fallback
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Region | North | |
| 2 | Ann | North | 120 | |||
| 3 | Ben | South | 80 | Names | ||
| 4 | Cara | North | 200 | |||
| 5 | Dan | East | 150 | |||
| 6 | Eve | South | 95 | |||
| 7 | Finn | North | 60 |
Your turn: In F3, list the names (column A only) of reps in the region typed in F1. If there are none, show None.
Common FILTER mistakes
- Ranges of different heights.
=FILTER(A2:C7,B2:B6="North")checks 5 rows for a 6-row table, and Excel returns #VALUE!. Makeincludestart and end on the same rows asarray. - Zeros where the source is empty. FILTER returns 0 for an empty cell in
array. Swap the blanks for empty text before filtering:=FILTER(IF(A2:C7="","",A2:C7),B2:B7="North"). - Whole columns.
=FILTER(A:C,B:B="North")works, but if the formula itself sits in columns A to C it refers to itself. Put the result beside the table, or use a fixed range such as A2:C1000. - Quotes around numbers.
C2:C7>"100"compares numbers with text and keeps nothing. WriteC2:C7>100. - Expecting the Filter button. FILTER copies matching rows to a new place and leaves the table alone. To hide rows in the table itself, use Data > Filter.
Frequently Asked Questions
How do I use the FILTER function in Excel?
Give it the rows to return and a condition for each row: =FILTER(A2:C7,B2:B7="North") returns every row of A2:C7 where column B is North. Type it in one cell; the matching rows spill into the cells below and to the right.
How do I FILTER with multiple criteria in Excel?
Multiply the conditions for AND and add them for OR: =FILTER(A2:C7,(B2:B7="North")*(C2:C7>100)) keeps rows that meet both, =FILTER(A2:C7,(B2:B7="North")+(B2:B7="East")) keeps rows that meet either. Each condition needs its own parentheses.
Why does FILTER return #CALC!?
Because no row matched and you gave no third argument. Add one to show something else: =FILTER(A2:C7,B2:B7="West","No match") shows No match instead of the error.
Which Excel versions have the FILTER function?
Excel 2021, Excel 2024 and Microsoft 365, plus Excel for the web. Excel 2019 and older do not have it and show #NAME?; there you need the Filter button on the Data tab or an INDEX and SMALL array formula.
How do I return only some columns with FILTER?
Filter only the columns you need, or wrap the result in CHOOSECOLS (Microsoft 365 or Excel 2024): =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) returns the first and third columns of the matching rows.
