The Excel FILTER function is one of the most powerful additions to Microsoft 365 — it extracts matching rows from a dataset in a single formula, with results that update automatically whenever your data or criteria change. No more Advanced Filter dialogs, no helper columns, no manual copy-paste. One formula, placed anywhere on your sheet, pulls exactly the data you need and spills it into the surrounding cells. This guide covers the syntax, the logic system behind it, and six real-world examples that go from the basics to combining FILTER with SORT, UNIQUE, and partial matching.
FILTER Function Syntax
| Argument | Required? | What it means |
|---|---|---|
| array | Required | The range or table to filter — the full dataset you want to extract rows from |
| include | Required | A logical condition (or combination of conditions) that returns TRUE/FALSE for each row. Rows where include is TRUE are returned. |
| if_empty | Optional | What to return when no rows match. Without this, FILTER returns a #CALC! error on no results. Use "" for blank or "No results" for a message. |
AND Logic vs OR Logic in FILTER
Understanding how to combine conditions is the single most important concept in FILTER. The operator you use between conditions determines whether both must be true (AND) or just one (OR):
- Use
*between conditions - TRUE × TRUE = 1 → row included
- TRUE × FALSE = 0 → row excluded
- All conditions must be met
- Use
+between conditions - TRUE + FALSE = 1 → row included
- FALSE + FALSE = 0 → row excluded
- At least one condition must be met
Example 1: Single Condition Filter
The simplest use of FILTER — extract all rows where one column matches a specific value. Here you extract every order from the "North" region from a sales table.
Source data (A1:D6):
Example 2: Two Conditions — AND Logic
Multiply two conditions with * to apply AND logic — only rows where both conditions are true will be returned. This is the equivalent of filtering on two columns simultaneously.
Example 3: OR Logic — Return Either Condition
Add conditions with + instead of multiplying them to apply OR logic — rows where any condition is true are returned. This is how you filter for multiple values in the same column.
Example 4: Filter by Number or Date Range
Use comparison operators inside the include argument to filter by numeric thresholds or date ranges. Apply two conditions to the same column — one for the lower bound, one for the upper — and multiply them together for AND logic.
Example 5: Partial Text Match (Contains)
FILTER does not natively support wildcards. However, nesting ISNUMBER(SEARCH(...)) inside the include argument produces a "contains" match — returning every row where the search string appears anywhere in the target column.
SEARCH is case-insensitive ("lap" matches "Laptop" and "LAP"). FIND is case-sensitive. Use SEARCH for most partial-match filters, and FIND only when case matters.
=FILTER(data, ISNUMBER(SEARCH("Lap",C2:C100)) * (B2:B100="North"), "")
Example 6: FILTER + SORT + UNIQUE — Chaining Dynamic Functions
FILTER becomes even more powerful when chained with other dynamic array functions. Wrap FILTER inside SORT to return filtered results in sorted order, or use UNIQUE to extract a distinct list from the filtered data — all in one formula.
4, -1 means "sort by column 4 in descending order." Change -1 to 1 for ascending. All three results spill dynamically and update whenever source data changes.
SORT(UNIQUE(FILTER(...))) as the source for a Data Validation list — it automatically grows or shrinks as your data changes, making dropdown menus fully self-maintaining.
Quick-Reference: FILTER Patterns
| Pattern | Use case | Formula skeleton |
|---|---|---|
| =FILTER(data, col=val, "") | Single condition | Basic row extraction |
| (cond1)*(cond2) | AND logic | All conditions must match |
| (cond1)+(cond2) | OR logic | Any condition matches |
| col>=low * col<=high | Numeric / date range | Between two values |
| ISNUMBER(SEARCH(term,col)) | Partial text match | "Contains" filter |
| SORT(FILTER(...)) | Filter + sort | Sorted extraction |
| UNIQUE(FILTER(...)) | Filter + deduplicate | Distinct filtered values |
| if_empty="" | Handle no results | Blank instead of #CALC! |
Troubleshooting FILTER Errors
#SPILL! — cells in the spill range are not empty
FILTER needs a clear block of empty cells below and to the right of the formula cell to output its results. If any cell in that range contains data (even a space), it shows #SPILL!. Clear the spill range and the error resolves. Merged cells also block spilling.
#CALC! — no rows matched and if_empty is missing
When no rows satisfy the include condition and you have not provided the third argument, FILTER returns #CALC!. Always include the if_empty argument: =FILTER(data, condition, "No results").
#VALUE! — include array has wrong dimensions
The include argument must have exactly the same number of rows as the array argument. If your data is A2:D100 (99 rows), the condition range must also be 99 rows — e.g. B2:B100.
Results do not update when new rows are added
FILTER is dynamic for data changes within the defined range, but the range itself does not auto-expand. To make it truly dynamic, convert your data to an Excel Table (Ctrl+T), then reference the table column instead: =FILTER(SalesTable, SalesTable[Region]="North", ""). Table references expand automatically.
=IFERROR(FILTER(data, condition, "No results"), "Error in filter")
Frequently Asked Questions
-
What does the FILTER function do in Excel?The FILTER function extracts rows (or columns) from a dataset that match one or more conditions you specify. The results spill automatically into neighbouring cells and update dynamically whenever the source data or criteria change — no manual re-filtering is required.
-
Which Excel versions support the FILTER function?FILTER is available in Microsoft 365, Excel 2024, and Excel 2021. It is not available in Excel 2019 or earlier versions. If you need similar functionality in older Excel, use the Advanced Filter feature or helper columns with COUNTIF and manual row extraction.
-
How do I use FILTER with multiple criteria (AND logic)?Multiply the conditions inside the include argument:
=FILTER(data, (range1=crit1)*(range2=crit2), ""). Multiplication creates AND logic because TRUE×TRUE=1 (include) while TRUE×FALSE=0 (exclude). Add more conditions by multiplying more parenthesised expressions. -
How do I use FILTER with OR logic?Add the conditions instead of multiplying:
=FILTER(data, (range=crit1)+(range=crit2), ""). Addition creates OR logic — a row is included when at least one condition returns TRUE. When mixing AND and OR, wrap the OR group in parentheses before multiplying:((A=1)+(A=2))*(B=3). -
What does the if_empty argument do in FILTER?The optional third argument controls what FILTER returns when no rows match the criteria. Without it, FILTER returns a #CALC! error when there are no results. Use
""for a blank cell,"No results"for a message, or0for a zero value. -
Why is my FILTER returning a #SPILL! error?A #SPILL! error means the cells where FILTER wants to output its results are not empty. Clear the cells in the spill range below and to the right of the formula cell. Also check for merged cells — FILTER cannot spill into a merged cell range. Convert your source data to a Table to make the formula range auto-expanding.