FILTER Function in Excel: AND, OR and If Empty
· 8 min read
FILTER returns every row of a table that meets a condition, and the result updates on its own when the data changes. You write it once, in one cell, and the matching rows spill into the cells below and to the right:
=FILTER(array, include, [if_empty])- array is the table you want rows from.
- include is a test that gives TRUE or FALSE for every row, such as
C2:C11="Travel". It must be as tall as the array. - if_empty is what to show when no row matches. Without it you get
#CALC!.
FILTER needs Excel 2021, Excel for Microsoft 365 or Excel for the web. In Excel 2019 and older it does not exist, and the cell shows #NAME?. If people with older versions open your file, use the AutoFilter buttons or a helper column instead.
This FILTER function Excel guide works through one list of expense claims: one condition, two conditions joined with AND, two joined with OR, a message for no matches, and FILTER inside SORT.
The example table
Ten expense claims with the employee, their team, the category, the amount and the approval status. The formula in G2 lists every travel claim.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Team | Category | Amount | Status | Employee | Team | Category | Amount | Status | |
| 2 | Maya | Sales | Travel | 420 | Approved | Maya | Sales | Travel | 420 | Approved | |
| 3 | Leo | Support | Meals | 38 | Pending | Sam | Ops | Travel | 180 | Pending | |
| 4 | Ana | Sales | Meals | 65 | Approved | Leo | Support | Travel | 610 | Approved | |
| 5 | Sam | Ops | Travel | 180 | Pending | Ana | Sales | Travel | 95 | Pending | |
| 6 | Maya | Sales | Software | 29 | Approved | Ivy | Ops | Travel | 350 | Approved | |
| 7 | Leo | Support | Travel | 610 | Approved | ||||||
| 8 | Ivy | Ops | Meals | 22 | Rejected | ||||||
| 9 | Ana | Sales | Travel | 95 | Pending | ||||||
| 10 | Sam | Ops | Software | 240 | Approved | ||||||
| 11 | Ivy | Ops | Travel | 350 | Approved |
FILTER with one condition
=FILTER(A2:E11, C2:C11="Travel")Result: five rows, from Maya's 420 to Ivy's 350, in the same order as the source. C2:C11="Travel" compares every category with "Travel" and produces ten TRUE or FALSE values. FILTER keeps the rows where the value is TRUE.
A few details that save time:
- Only G2 holds a formula. The other blue cells are spilled values. You cannot edit one on its own.
- Type the headers yourself. FILTER returns only data rows, so G1:K1 is plain text.
- Point at a cell instead of typing the text. With the category in M1,
=FILTER(A2:E11, C2:C11=M1)lets anyone change the list without touching the formula. - Return fewer columns.
=FILTER(A2:A11, C2:C11="Travel")returns only the names. The array and the include range just need the same number of rows.
Text comparisons ignore case, so "travel" matches "Travel". A trailing space does not match.
FILTER with multiple criteria
FILTER has no separate argument for a second condition. You combine the conditions inside include with arithmetic, because Excel treats TRUE as 1 and FALSE as 0.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Team | Category | Amount | Status | Travel and 200 or more | |||||
| 2 | Maya | Sales | Travel | 420 | Approved | Maya | Sales | Travel | 420 | Approved | |
| 3 | Leo | Support | Meals | 38 | Pending | Leo | Support | Travel | 610 | Approved | |
| 4 | Ana | Sales | Meals | 65 | Approved | Ivy | Ops | Travel | 350 | Approved | |
| 5 | Sam | Ops | Travel | 180 | Pending | ||||||
| 6 | Maya | Sales | Software | 29 | Approved | Software or pending | |||||
| 7 | Leo | Support | Travel | 610 | Approved | Leo | Support | Meals | 38 | Pending | |
| 8 | Ivy | Ops | Meals | 22 | Rejected | Sam | Ops | Travel | 180 | Pending | |
| 9 | Ana | Sales | Travel | 95 | Pending | Maya | Sales | Software | 29 | Approved | |
| 10 | Sam | Ops | Software | 240 | Approved | Ana | Sales | Travel | 95 | Pending | |
| 11 | Ivy | Ops | Travel | 350 | Approved | Sam | Ops | Software | 240 | Approved | |
| 12 | |||||||||||
| 13 | AI's formula | ||||||||||
| 14 | #VALUE! |
AND: multiply the conditions
=FILTER(A2:E11, (C2:C11="Travel")*(D2:D11>=200))Result: three rows, Maya 420, Leo 610 and Ivy 350. For each row, 1*1 is 1 only when both tests pass. Any FALSE makes the product 0, and FILTER drops the row. Sam's 180 travel claim is out because it fails the amount test.
Each condition needs its own brackets. Without them, Excel applies * before =, so the formula compares the wrong things.
OR: add the conditions
=FILTER(A2:E11, (C2:C11="Software")+(E2:E11="Pending"))Result: five rows: Leo 38, Sam 180, Maya 29, Ana 95 and Sam 240. Adding gives 1 when either test passes and 2 when both do. FILTER keeps any number other than 0, so a row that meets both conditions still appears once.
You can mix the two: (C2:C11="Travel")*((E2:E11="Pending")+(D2:D11>400)) keeps travel claims that are pending or over 400. The brackets around the OR part matter. Without them, the travel test applies only to the pending check.
Why AND() does not work here
The AI's formula in G14 uses the AND function. AND reads all twenty values and returns a single TRUE or FALSE for the whole range, so FILTER gets one value for ten rows and shows #VALUE!. OR() has the same problem. Inside FILTER, use * and +. IF with AND and OR covers those functions in ordinary formulas, and the same multiply-and-add idea powers SUMPRODUCT.
if_empty: a message instead of #CALC!
When no row matches, FILTER has nothing to return and shows #CALC!. The third argument replaces that with a value of your choice.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Team | Category | Amount | Status | Team | Sales | ||||
| 2 | Maya | Sales | Travel | 420 | Approved | ||||||
| 3 | Leo | Support | Meals | 38 | Pending | Rejected claims | |||||
| 4 | Ana | Sales | Meals | 65 | Approved | None | |||||
| 5 | Sam | Ops | Travel | 180 | Pending | ||||||
| 6 | Maya | Sales | Software | 29 | Approved | All claims, largest first | |||||
| 7 | Leo | Support | Travel | 610 | Approved | Maya | Sales | Travel | 420 | Approved | |
| 8 | Ivy | Ops | Meals | 22 | Rejected | Ana | Sales | Travel | 95 | Pending | |
| 9 | Ana | Sales | Travel | 95 | Pending | Ana | Sales | Meals | 65 | Approved | |
| 10 | Sam | Ops | Software | 240 | Approved | Maya | Sales | Software | 29 | Approved | |
| 11 | Ivy | Ops | Travel | 350 | Approved |
=FILTER(A2:E11, (B2:B11=H1)*(E2:E11="Rejected"), "None")Sales has no rejected claims, so G4 shows None. Change H1 to Ops and Ivy's 22 rejected meal claim appears in its place.
Use a message that says what happened. An empty string "" also works, but then a blank cell could mean "no matches" or "broken formula". Avoid wrapping FILTER in IFERROR instead: it would also hide a #VALUE! from ranges of different sizes. See IFERROR in Excel for when catching errors hides a mistake.
FILTER and SORT together
FILTER keeps the source order. To sort the result, put FILTER inside SORT:
=SORT(FILTER(A2:E11, B2:B11=H1), 4, -1)SORT's second argument is the column to sort by, counted inside the filtered result, not on the sheet. Amount is the 4th column of A:E, so the index is 4. The third argument is 1 for ascending (the default) or -1 for descending. For Sales the result is Maya 420, Ana 95, Ana 65 and Maya 29.
To keep only the top few, wrap the result in TAKE: =TAKE(SORT(FILTER(A2:E11, C2:C11="Travel"), 4, -1), 3) returns the three largest travel claims. For distinct values from the filtered rows, such as which employees have pending claims, wrap it in UNIQUE, as the UNIQUE function guide shows.
Common mistakes
#SPILL! Something is in the way. A spill needs empty cells for its whole result, and even a single space counts as content. Click the error cell: Excel draws a dashed border around the range it wants. Clear it, or move the formula.
#VALUE! The include range is a different height from the array, for example A2:E11 with C2:C12, or you used AND() or OR().
#CALC! No row matches and there is no if_empty. Sometimes that is the answer. Sometimes the condition has a typo, such as "Travell".
Fixed ranges that stop short. A2:E11 does not grow when you add row 12. Format the data as a table (Ctrl+T) and use =FILTER(Claims, Claims[Category]="Travel"), which grows with it.
Numbers stored as text. Excel ranks any text above any number, so an amount stored as the text "180" passes D2:D11>=200 and shows up where it should not. Numbers stored as text are usually aligned left. Convert them with VALUE or Text to Columns before you filter.
FAQ
How do I use the FILTER function in Excel?
Write =FILTER(table, condition) in one empty cell, for example =FILTER(A2:E11, C2:C11="Travel"). The matching rows spill into the cells below. Keep the condition range the same height as the table.
How do I filter with multiple criteria?
Put each condition in brackets and multiply them for AND: (C2:C11="Travel")*(D2:D11>=200). Add them for OR: (C2:C11="Software")+(E2:E11="Pending").
Why does FILTER show #CALC!?
No rows match. Add a third argument such as "None", or check the condition for typos and extra spaces.
Which Excel versions have FILTER?
Excel 2021, Excel 2024, Excel for Microsoft 365 and Excel for the web. Excel 2019 and older show #NAME?.
You can practise FILTER on a full year of order data, including an audit where an AI's in-transit list picked up the wrong rows, on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- List West's December orders with FILTER
- FILTER with two conditions at once
- FILTER rows that meet either condition
- FILTER that says None instead of an error