The FILTER function returns only the rows that meet a condition, then spills the result into the cells below. Change the criterion and the output updates immediately — no sorting, no copy-paste, no helper column. For assignments that ask for a filtered list, a regional extract or an overdue-orders report, it is usually the cleanest formula in Excel.
Quick answer: =FILTER(array, include, [if_empty]). The array is the data you want back, include is a TRUE/FALSE test with the same height, and if_empty is what to show when nothing matches. FILTER is available in Microsoft 365, Excel 2021 and Excel 2024 — not Excel 2019 or older.
FILTER Syntax and Arguments
=FILTER(array, include, [if_empty])
| Argument | What it does | Assignment example |
|---|---|---|
array | The range or table you want to return. | A2:D100 |
include | A TRUE/FALSE test with the same height as the array. | B2:B100="North" |
if_empty | Optional value when no row matches. | "No matching rows" |
The array and include ranges must line up. If the data starts on row 2, the condition must start on row 2 too. A mismatch is the usual cause of #VALUE!.
Example 1: Filter by One Criterion
Return every order from the North region:
=FILTER(A2:D100, B2:B100="North", "No matching rows")
The result includes all four columns from A to D. If you only want the customer and total, wrap FILTER inside CHOOSECOLS:
=CHOOSECOLS(FILTER(A2:D100, B2:B100="North", "No rows"), 2, 4)
CHOOSECOLS is available in Microsoft 365 and Excel 2024, not Excel 2021. On 2021, filter only the columns you need, for example =FILTER(A2:B100, B2:B100="North", "No rows"), or use INDEX to pick a column.
Example 2: Multiple Criteria with AND
Multiply the conditions to require all of them. This returns North orders above 1,000:
=FILTER(A2:D100, (B2:B100="North")*(D2:D100>1000), "No rows")
Each bracket produces TRUE or FALSE. Multiplication behaves like AND: 1×1 is 1, anything×0 is 0. Use a cell reference instead of a typed value when the criterion may change:
=FILTER(A2:D100, (B2:B100=H2)*(D2:D100>I2), "No rows")
Example 3: Multiple Criteria with OR
Add the conditions to accept either one. This returns North or South orders:
=FILTER(A2:D100, (B2:B100="North")+(B2:B100="South"), "No rows")
Addition behaves like OR: any TRUE makes the result TRUE. Combine AND and OR with brackets so the logic is unambiguous:
=FILTER(A2:D100, ((B2:B100="North")+(B2:B100="South"))*(D2:D100>1000), "No rows")
Write the condition on a copy of the sheet and check the TRUE/FALSE column before you trust the spill. A misplaced bracket can quietly return the wrong rows.
Example 4: Filter a Date Range
Use DATE rather than typed text so the comparison works in every locale:
=FILTER(A2:D100, (A2:A100>=DATE(2026,1,1))*(A2:A100<=DATE(2026,3,31)), "No rows")
If the dates include timestamps, an end date of 31 March excludes 31 March at 14:30. Use < with the next day, or strip the time with INT() in a helper column. For more date patterns, read the SUMIFS date range guide.
Example 5: Filter Text That Contains a Word
FILTER has no wildcard argument of its own. Wrap SEARCH in ISNUMBER to test whether the text appears anywhere in the cell:
=FILTER(A2:D100, ISNUMBER(SEARCH("north", B2:B100)), "No rows")
SEARCH is case-insensitive. Use FIND if the case must match. To exclude a word, put NOT around the test:
=FILTER(A2:D100, NOT(ISNUMBER(SEARCH("cancelled", C2:C100))), "No rows")
Example 6: Filter from Another Sheet
Qualify both ranges with the sheet name:
=FILTER(Data!A2:D100, Data!B2:B100="North", "No rows")
If the sheet name contains a space, wrap it in apostrophes: ='Q1 Data'!A2:D100. Keep the output on a separate report sheet so a spill never overwrites the source data.
Example 7: FILTER + SORT for a Top-N List
Sort the filtered rows by the fourth column, largest first:
=SORT(FILTER(A2:D100, B2:B100="North", "No rows"), 4, -1)
Wrap it in TAKE to keep only the top five:
=TAKE(SORT(FILTER(A2:D100, B2:B100="North", "No rows"), 4, -1), 5)
TAKE is available in Microsoft 365 and Excel 2024, not Excel 2021. On 2021, use INDEX with SEQUENCE(5) for each column, or move the extract into Power Query.
Example 8: FILTER + UNIQUE and a Dynamic Count
List the unique customers who have an order above 1,000:
=UNIQUE(FILTER(B2:B100, D2:D100>1000))
Count the matching rows without showing them:
=IFERROR(ROWS(FILTER(A2:A100, B2:B100="North")), 0)
The IFERROR handles the empty-result #CALC! error. Use the third argument when you want a visible message instead of a zero count.
Common FILTER Errors and Fixes
| Error | Most likely cause | First fix |
|---|---|---|
#CALC! | No row matches and no if_empty value was supplied. | Add a third argument such as "No rows", or wrap the formula in IFERROR. |
#SPILL! | Something blocks the cells below or to the right. | Clear the output range; avoid merged cells and Excel Tables. |
#VALUE! | The array and include ranges have different heights, or include contains an error. | Line up the first and last rows; clean the source data. |
#NAME? | The Excel version does not support FILTER, or the name is misspelled. | Check the version; use Advanced Filter or Power Query on Excel 2019. |
If the formula returns the wrong rows rather than an error, the logic is usually the problem. Paste it into the free Formula Checker for a structural diagnosis, or read the dynamic-array troubleshooting guide.
FILTER vs Advanced Filter vs Power Query
| Task | FILTER | Power Query |
|---|---|---|
| One-off extract in the workbook | Best — one formula, updates instantly. | Overkill unless the source is a file or database. |
| Repeatable monthly cleanup | Fine for a single table; formulas can sprawl. | Better — refresh the query and keep the steps. |
| Excel 2019 or older | Not available. | Use Power Query or Advanced Filter. |
| Very large dataset | Can be slow if it spills thousands of rows. | Better for repeatable large imports. |
Assignment Patterns Where FILTER Wins
- Regional extract: return only the rows for a selected region, then chart the spill.
- Overdue invoices: filter due dates before TODAY() and balances above zero.
- Top customers: FILTER the segment, SORT by revenue, TAKE the top ten.
- Exception report: filter rows where status is not Complete or the variance exceeds a threshold.
- Unique list: wrap FILTER in UNIQUE to build a dynamic dropdown source.
- Dashboard input: point a chart or pivot at the spilled range so it updates with the filter.
Dynamic arrays pair naturally with VSTACK when you need to combine several filtered tables into one list.
Before You Submit a FILTER Formula
Test four cases: a normal match, no match, a blank criterion and a criterion that should match everything. A filter that only works on the sample row is a common source of lost marks.
Paste the formula into the Formula Checker, run the whole workbook through the Workbook Health Check, then use the 23-point Assignment Readiness Check for the brief, file names and layout. If the source data is messy, the Data Cleaner trims spaces and fixes text-numbers before you filter.
Need the formula built, tested and explained before the deadline? Send the workbook to an Excel expert for a fixed quote.
