FILTER is the function that made dynamic arrays worth the upgrade. It takes a table, tests every row against a condition, and spills the matching rows into the cells below — all from a single formula that updates itself the moment the data changes.
This guide covers the syntax, spilling behaviour, multiple conditions with AND and
OR, text and date filtering, FILTER combined with SORT and UNIQUE, the
#SPILL! errors, and the ways FILTER replaces older tools like
Advanced Filter, helper columns, and PivotTable refreshes for interactive reports.
FILTER extracts the rows from a range that meet a condition and returns them as a spilled array, written as =FILTER(array, include, [if_empty]).
What Is the FILTER Function?
FILTER is a dynamic array function. It takes a source range, evaluates a logical test against each row, and returns every row where the test is TRUE. The result spills into the cells below and beside the formula, and it grows or shrinks automatically as the underlying data changes.
Before FILTER, you needed helper columns, Advanced Filter, or a PivotTable to answer "show me all the rows where X is true". FILTER does it in one formula, live, with no refresh button.
How FILTER evaluates a formula
Take the formula =FILTER(A2:C100, B2:B100="East", "No matches"). Excel does four things, in order:
- Build a TRUE/FALSE array. For every row in
B2:B100, it asks: is this cell "East"? The result is a column of TRUE and FALSE values, one per row. - Keep the TRUE rows. Excel walks through the source range and collects every row whose test returned TRUE.
- Spill the result. The collected rows appear in the cells starting at the formula and extending down and to the right. The blue outline around the result is Excel telling you "this is a spilled range, don't type inside it".
- Fall back to
if_empty. If nothing matched, the third argument is shown instead of#CALC!.
What the TRUE/FALSE test looks like
| A Region | B Include? | |
|---|---|---|
| 2 | East | TRUE |
| 3 | West | FALSE |
| 4 | East | TRUE |
| 5 | North | FALSE |
| 6 | East | TRUE |
A2:A6="East" → {TRUE;FALSE;TRUE;FALSE;TRUE}. Rows 2, 4, and 6 are kept; rows 3 and 5 are dropped.It is not available in Excel 2019 or earlier. On the web, Excel for the web supports it. Excel for iPad, iPhone, and Android also support it. If you open a FILTER workbook in Excel 2019, the formula shows as _xlfn._xlws.FILTER or a #NAME? error.
If you only need one number, use SUMIFS, COUNTIFS, or XLOOKUP. FILTER is for "give me the whole matching list".
Syntax and Arguments
The full signature has three arguments, and the last one is optional but strongly recommended.
=FILTER(array, include, [if_empty])
Walking through each argument
- array — the range you want to filter. Can be one column, several columns, or a whole table. This is the data Excel will return.
- include — a TRUE/FALSE array with the same number of rows as
array. Usually written as a comparison:B2:B100="East". - if_empty (optional but recommended) — what to return when no rows match. Without it, FILTER returns
#CALC!.
| Argument | Required? | Notes |
|---|---|---|
array | Required | Can be one column or many. Must have the same number of rows as the include test. |
include | Required | A TRUE/FALSE array. Usually a comparison against a column in the array. |
if_empty | Optional | If omitted, FILTER returns #CALC! when nothing matches. Supply "" or a message. |
What happens when the arguments line up vs. when they do not
| A array rows | B include rows | C Result | |
|---|---|---|---|
| 2 | 2–100 (99 rows) | 2–100 (99 rows) | Correct result |
| 3 | 2–100 (99 rows) | 2–50 (49 rows) | #VALUE! |
The include array must have exactly the same number of rows as array. If array is A2:C100, the test must be B2:B100="East", not B2:B50="East". Mismatch returns #VALUE!.
A blank-looking result is almost always better than a red #CALC! in a report. Use "" for a blank cell, or a message like "No rows match".
Dynamic Arrays and Spilling Explained
The first thing that surprises new FILTER users is that the result appears in more cells than the formula was entered in. That is spilling, and it is the feature that makes FILTER work.
What spilling looks like
| A Region | B Rep | C Amount | E Spilled result | F | G | |
|---|---|---|---|---|---|---|
| 2 | East | Ana | 1200 | East | Ana | 1200 |
| 3 | West | Ben | 900 | East | Carla | 1500 |
| 4 | East | Carla | 1500 | East | Ana | 1100 |
| 5 | North | Ben | 700 | |||
| 6 | East | Ana | 1100 |
=FILTER(A2:C6, A2:A6="East", "None")Rows 2, 4, and 6 of the source appear in E2:G4 with all three columns intact. The range is outlined in blue — that is the spilled range.
Rules of spilled ranges
Only the top-left cell holds the formula
Click any other cell in the spill and the formula bar shows a greyed-out version. You cannot edit those cells individually.
The range grows and shrinks automatically
Add a new matching row to the source and the spill expands down by one row. No re-running, no refresh.
Delete the top-left cell to delete the result
Pressing Delete on any other cell in the spilled range does nothing. The whole result is bound to the origin cell.
You cannot type inside a spilled range
Excel blocks typing and shows a #SPILL! warning, because the formula owns those cells.
What happens when the spill area is blocked
| E Formula cell | F Row 3 | G Row 4 | |
|---|---|---|---|
| 2 | #SPILL! | Existing data | Existing data |
| 3 | — | — |
If the FILTER formula is in E2, you can reference its entire result with E2#. That grows automatically as the spill grows. It is the cleanest way to feed a FILTER result into another formula, chart, or PivotTable.
Multiple Conditions — AND and OR
FILTER takes a single include argument, but you can combine as many conditions as you like inside it. The trick is that FILTER uses arithmetic, not the AND and OR functions.
The sample data these examples use
| A Region | B Rep | C Amount | D Status | |
|---|---|---|---|---|
| 2 | East | Ana | 1200 | Open |
| 3 | West | Ben | 900 | Open |
| 4 | East | Carla | 1500 | Pending |
| 5 | North | Ben | 700 | Open |
| 6 | East | Ana | 800 | Closed |
| 7 | West | Carla | 2100 | Open |
AND vs OR — side by side
=FILTER(A2:D7,
(A2:A7="East") * (C2:C7>1000),
"No matches")=FILTER(A2:D7,
(A2:A7="East") + (A2:A7="West"),
"No matches")Result of the AND filter
| A Region | B Rep | C Amount | D Status | |
|---|---|---|---|---|
| 2 | East | Ana | 1200 | Open |
| 4 | East | Carla | 1500 | Pending |
Result of the OR filter
| A Region | B Rep | C Amount | D Status | |
|---|---|---|---|---|
| 2 | East | Ana | 1200 | Open |
| 3 | West | Ben | 900 | Open |
| 4 | East | Carla | 1500 | Pending |
| 6 | East | Ana | 800 | Closed |
| 7 | West | Carla | 2100 | Open |
AND with three or more conditions
' East AND over 1000 AND open
=FILTER(A2:D7,
(A2:A7="East") * (C2:C7>1000) * (D2:D7="Open"),
"No matches")
| A Region | B Rep | C Amount | D Status | |
|---|---|---|---|---|
| 2 | East | Ana | 1200 | Open |
Combining AND and OR — (A OR B) AND C
=FILTER(A2:D7,
((A2:A7="East") + (A2:A7="West")) * (C2:C7>1000),
"No matches")
| A Region | B Rep | C Amount | D Status | |
|---|---|---|---|---|
| 2 | East | Ana | 1200 | Open |
| 4 | East | Carla | 1500 | Pending |
| 7 | West | Carla | 2100 | Open |
The AND and OR functions collapse a whole array down to a single TRUE/FALSE value, which FILTER cannot use. You must use * for AND and + for OR inside FILTER's include argument.
Exclusion — the "not" case
' Everything except East
=FILTER(A2:D7, A2:A7<>"East", "No matches")
' Not blank
=FILTER(A2:D7, A2:A7<>"", "No matches")
' Exclude a list of values
=FILTER(A2:D7,
(A2:A7<>"East") * (A2:A7<>"West"),
"No matches")
| A Region | B Rep | C Amount | D Status | |
|---|---|---|---|---|
| 5 | North | Ben | 700 | Open |
Real-World Examples
These patterns cover most day-to-day FILTER use. Each one shows the data, the formula, and what the spilled result looks like.
Example 1 — Show all rows for one region
The simplest FILTER: one text condition, all columns returned.
| A Region | B Rep | C Sales | E Result | F | G | |
|---|---|---|---|---|---|---|
| 2 | East | Ana | 1200 | East | Ana | 1200 |
| 3 | West | Ben | 900 | East | Carla | 1500 |
| 4 | East | Carla | 1500 | East | Ana | 1100 |
| 5 | North | Ben | 700 | |||
| 6 | East | Ana | 1100 |
=FILTER(A2:C6, A2:A6="East", "None")Three East rows spill into E2:G4. The West and North rows are excluded.
Example 2 — Over a threshold
One numeric condition, all columns returned. Useful for "top of the pile" reviews.
| A Invoice | B Customer | C Amount | E Result | F | G | |
|---|---|---|---|---|---|---|
| 2 | INV-1042 | Acme | 1250 | INV-1042 | Acme | 1250 |
| 3 | INV-1039 | Bravo | 820 | INV-1038 | Delta | 3400 |
| 4 | INV-1038 | Delta | 3400 | INV-1028 | Acme | 5600 |
| 5 | INV-1035 | Bravo | 450 | |||
| 6 | INV-1028 | Acme | 5600 |
=FILTER(A2:C6, C2:C6>1000, "None")Rows 2, 4, and 6 are returned. Rows 3 and 5 fall below the threshold.
' Every sale over $1000
=FILTER(A2:C100, C2:C100>1000, "No sales above 1000")
' Every sale between $500 and $2000 (AND)
=FILTER(A2:C100,
(C2:C100>=500) * (C2:C100<=2000),
"None in range")
Example 3 — Two columns, specific result
When you only want to return part of the source, hand FILTER a narrower array. Choose columns that are not adjacent by using CHOOSE.
| A Region | B Rep | C Sales | E Rep only | F Sales only | |
|---|---|---|---|---|---|
| 2 | East | Ana | 1200 | Ana | 1200 |
| 3 | West | Ben | 900 | Carla | 1500 |
| 4 | East | Carla | 1500 | Ana | 1100 |
| 5 | North | Ben | 700 | ||
| 6 | East | Ana | 1100 |
=FILTER(CHOOSE({1,2}, B2:B6, C2:C6), A2:A6="East", "None")The Region column is skipped entirely — only Rep and Sales appear in the spill.
' Return only the Rep column for East
=FILTER(B2:B100, A2:A100="East", "None")
' Return Rep and Sales (skip Region) with CHOOSE
=FILTER(CHOOSE({1,2}, B2:B100, C2:C100),
A2:A100="East",
"None")
' Return Sales, Rep order instead
=FILTER(CHOOSE({1,2}, C2:C100, B2:B100),
A2:A100="East",
"None")
Example 4 — Dates in a range
Date filtering is two AND conditions on the same column — one for the start, one for the end.
| A Date | B Region | C Amount | E Result | F | G | |
|---|---|---|---|---|---|---|
| 2 | 2026-01-05 | East | 1200 | 2026-01-05 | East | 1200 |
| 3 | 2026-02-18 | West | 900 | 2026-02-18 | West | 900 |
| 4 | 2026-04-02 | East | 1500 | 2026-03-22 | East | 1100 |
| 5 | 2026-03-22 | East | 1100 | |||
| 6 | 2026-07-19 | North | 700 |
=FILTER(A2:C6, (A2:A6>=DATE(2026,1,1))*(A2:A6<=DATE(2026,3,31)), "Nothing in Q1")Rows from April and July are excluded.
' Q1 2026 only
=FILTER(A2:D100,
(A2:A100>=DATE(2026,1,1)) * (A2:A100<=DATE(2026,3,31)),
"Nothing in Q1")
' Year-to-date — from Jan 1 to today
=FILTER(A2:D100,
(A2:A100>=DATE(YEAR(TODAY()),1,1)) * (A2:A100<=TODAY()),
"No YTD data")
' Overdue invoices
=FILTER(A2:D100, A2:A100
If the dates are stored as text (left-aligned in the cell, no formatting), the comparison against DATE(...) will never match and the filter returns the if_empty message. Verify with =ISNUMBER(A2).
Example 5 — Text match with wildcards
FILTER does not support wildcards inside =. Use ISNUMBER(SEARCH(...)) for contains, or LEFT/RIGHT for starts-with and ends-with.
| A Account | B Contact | C Amount | E Result | F | G | |
|---|---|---|---|---|---|---|
| 2 | Northwind | Ana | 1200 | Northwind | Ana | 1200 |
| 3 | Southgate | Ben | 900 | Northline | Carla | 1500 |
| 4 | Northline | Carla | 1500 | Northern | Ana | 1100 |
| 5 | Eastgate | Ben | 700 | |||
| 6 | Northern | Ana | 1100 |
=FILTER(A2:C6, ISNUMBER(SEARCH("North", A2:A6)), "None")Accounts that contain the letters "North" anywhere.
' Contains "North" anywhere (case-insensitive)
=FILTER(A2:C100,
ISNUMBER(SEARCH("North", A2:A100)),
"None")
' Starts with "North"
=FILTER(A2:C100, LEFT(A2:A100,5)="North", "None")
' Ends with "Ltd"
=FILTER(A2:C100, RIGHT(A2:A100,3)="Ltd", "None")
' One of three exact values (OR)
=FILTER(A2:C100,
(A2:A100="North") + (A2:A100="South") + (A2:A100="East"),
"None")
FILTER with SORT, SORTBY, and UNIQUE
FILTER returns rows in the order they appear in the source. That is rarely what a report needs. SORT, SORTBY, and UNIQUE are the three functions you wrap around FILTER most often.
Before and after SORT
| A Region | B Rep | C Sales | E Unsorted | F | G | I Sorted desc | J | K | |
|---|---|---|---|---|---|---|---|---|---|
| 2 | East | Ana | 1200 | East | Ana | 1200 | East | Carla | 1500 |
| 3 | West | Ben | 900 | East | Carla | 1500 | East | Ana | 1200 |
| 4 | East | Carla | 1500 | East | Ana | 1100 | East | Ana | 1100 |
| 5 | North | Ben | 700 | ||||||
| 6 | East | Ana | 1100 |
SORT vs SORTBY — when each one wins
=SORT(
FILTER(A2:C100, A2:A100="East"),
3, -1)=SORTBY(
FILTER(CHOOSE({1,2},
B2:B100, C2:C100),
A2:A100="East"),
FILTER(A2:A100,
A2:A100="East"), 1)UNIQUE — remove duplicates from a FILTER result
| A Region | B Rep | E Filtered | G Unique | |
|---|---|---|---|---|
| 2 | East | Ana | Ana | Ana |
| 3 | West | Ben | Carla | Carla |
| 4 | East | Carla | Ana | |
| 5 | North | Ben | ||
| 6 | East | Ana |
=UNIQUE(FILTER(B2:B6, A2:A6="East"))Ana appears twice in the filtered list, once in the unique list.
Counting and summing a FILTER result
| A Statistic | B Formula | C Result | |
|---|---|---|---|
| 2 | Count | =ROWS(FILTER(A2:C100,A2:A100="East")) | 3 |
| 3 | Total | =SUM(FILTER(C2:C100,A2:A100="East")) | 3800 |
| 4 | Average | =AVERAGE(FILTER(C2:C100,A2:A100="East")) | 1266.67 |
| 5 | Maximum | =MAX(FILTER(C2:C100,A2:A100="East")) | 1500 |
The two return the same total. SUMIFS is faster on very large ranges and works in older Excel. SUM(FILTER(...)) is easier to read when the condition is complex, and the same FILTER can be reused for COUNT, AVERAGE, MAX, and MIN.
FILTER vs Advanced Filter, PivotTable, and XLOOKUP
FILTER is not the only way to extract rows in Excel. It is the cleanest way in most cases, but each alternative has a place where it wins.
| Tool | Best for | Watch out for |
|---|---|---|
FILTER | A live, spilled list of matching rows | Excel 2021 / M365 only. Result cells are locked to the formula. |
Advanced Filter | One-off extraction to a fixed range | Not live. Needs a manual re-run when the data changes. |
PivotTable | Interactive slicing of many dimensions at once | Needs a refresh. Returns aggregates, not the underlying rows. |
XLOOKUP | Finding one value for one key | Returns a single match, not a whole filtered list. |
SUMIFS | A single number from many conditions | Aggregates only. No rows returned. |
AutoFilter | Hiding rows in place for inspection | Modifies the sheet visually. Not a formula. |
The source data all three tools would read
| A Region | B Rep | C Sales | |
|---|---|---|---|
| 2 | East | Ana | 1200 |
| 3 | West | Ben | 900 |
| 4 | East | Carla | 1500 |
| 5 | North | Ben | 700 |
| 6 | East | Ana | 1100 |
The same question, three tools
One formula, live, spills rows
=FILTER(A2:C6, (A2:A6="East") * (C2:C6>1000), "None")
Advanced Filter
Criteria in A1:C2 (East, >1000), source A1:C6, output E1. Re-run manually when data changes.
PivotTable
Region as a filter set to East, Sales as Values, then Value Filter > 1000.
Use FILTER for a clean list you want to see or export. Use a PivotTable when you need to slice by many dimensions quickly. Many real dashboards use both — a PivotTable for summary numbers, a FILTER for the detail behind them.
Build a Dynamic Report with FILTER
This is the pattern that replaces most "helper column + AutoFilter" workflows. A drop-down in one cell, a FILTER below it, and the report updates as the user chooses.
Step 1 — The source data
Convert the source to a Table with Ctrl+T so it grows automatically. In this example the Table is named Sales.
| A Date | B Region | C Rep | D Amount | |
|---|---|---|---|---|
| 2 | 2026-01-05 | East | Ana | 1200 |
| 3 | 2026-01-18 | West | Ben | 900 |
| 4 | 2026-02-02 | East | Carla | 1500 |
| 5 | 2026-02-19 | East | Ana | 800 |
| 6 | 2026-03-08 | North | Ben | 700 |
| 7 | 2026-03-22 | East | Carla | 1100 |
Sales from the Table Design tab. All formulas below use structured references.Step 2 — The interactive controls
- Region drop-down (cell H1). Data → Data Validation → List → Source:
East,West,North,South. - Minimum amount (cell H2). Type a starting number, e.g.
0. - Section heading (row 3). Add labels in E4:G4 for the columns you want to see.
Step 3 — The FILTER formula
One formula in cell E5 does all the work. The whole report appears below it.
' In cell E5 — filter rows by region and minimum amount
=FILTER(
Sales[[Date]:[Amount]],
(Sales[Region]=H1) * (Sales[Amount]>=H2),
"No rows match your filters")
What the user sees when they choose East and 1000
| E Date | F Region | G Amount | |
|---|---|---|---|
| 5 | 2026-01-05 | East | 1200 |
| 6 | 2026-02-02 | East | 1500 |
| 7 | 2026-03-22 | East | 1100 |
| 8 | |||
| 9 | Count: | 3 | |
| 10 | Total: | 3800 |
Step 4 — Summary numbers below
Because FILTER spills, the summary cells can reference the spilled range directly with the # operator.
' Number of matching rows
=ROWS(E5#)
' Total of the matching rows' Amount column
=SUM(INDEX(E5#, , 3))
' Average amount
=AVERAGE(INDEX(E5#, , 3))
' Highest single amount
=MAX(INDEX(E5#, , 3))
Wrap the FILTER in SORT(..., 1, -1) to show the newest or highest rows first. The spilled range still grows and shrinks correctly, and E5# still references the whole thing.
If the FILTER returns 50 rows and you only left 30 rows of blank space below, the range hits your summary cells and returns #SPILL!. Leave a large buffer, or place the summary on a separate sheet.
Common Errors and How to Fix Them
Most FILTER problems fall into a handful of patterns. Each one below has a known cause and a known fix.
What the errors look like
| A Error | B Cause | C Fix | |
|---|---|---|---|
| 2 | #SPILL! | Spill area is blocked | Clear the cells below/beside |
| 3 | #CALC! | No matches, no if_empty | Add a third argument |
| 4 | #VALUE! | Row counts do not match | Align both ranges |
| 5 | #NAME? | Excel 2019 or earlier | Upgrade or use a fallback |
| 6 | Empty | Data types do not match | Clean with TRIM and VALUE |
This is the most common FILTER error. The formula wants to write into cells that already contain data, or it hits a merged cell, or it goes off the edge of the sheet.
Fix: click the warning icon to see which cells are in the way, then clear them. Also check for merged cells in the spill area — they block spills even when visually empty.
The filter returned zero rows and you did not supply an if_empty argument. #CALC! is Excel's way of saying "the result would be empty".
Fix: add a third argument: =FILTER(A2:C100, B2:B100="East", "No matches").
The array and include arguments have different row counts. For example =FILTER(A2:C100, B2:B50="East").
Fix: make sure the two ranges span the same rows. If the source is a Table, structured references handle this automatically.
You are using Excel 2019 or earlier, or you opened the file in a version that predates dynamic arrays. FILTER requires Excel 2021 or Microsoft 365.
Fix: upgrade Excel, or use a PivotTable, Advanced Filter, or helper-column approach instead.
Almost always a data-type mismatch. Numbers stored as text, dates stored as text, or trailing spaces in text values. The test finds no TRUE rows because nothing actually matches.
Fix: clean the source with TRIM and VALUE, or verify with =ISNUMBER() and =ISTEXT().
That is by design. Excel shows a greyed-out formula in the formula bar for any cell except the top-left of the spill.
Fix: edit the formula in the top-left cell. To delete the whole result, select the top-left cell and press Delete.
What correct vs wrong logic looks like
| A Goal | B Wrong | C Right | |
|---|---|---|---|
| 2 | AND | AND(B2="East", C2>1000) | (B2="East") * (C2>1000) |
| 3 | OR | OR(B2="East", B2="West") | (B2="East") + (B2="West") |
| 4 | No matches | (blank 3rd argument) | "No matches" |
' #CALC! — add if_empty
=FILTER(A2:C100, B2:B100="East", "No matches")
' Wrong: AND() collapses the array
=FILTER(A2:C100, AND(B2:B100="East", C2:C100>1000))
' Right: multiply for AND
=FILTER(A2:C100, (B2:B100="East") * (C2:C100>1000))
' Wrong: OR() collapses the array
=FILTER(A2:C100, OR(B2:B100="East", B2:B100="West"))
' Right: add for OR
=FILTER(A2:C100, (B2:B100="East") + (B2:B100="West"))
Best Practices for FILTER
These habits keep dynamic reports reliable as the source data grows.
Always supply if_empty
A blank result beats a red #CALC!. Use "" or a short message.
Use Tables as the source
Structured references grow automatically and prevent row-count mismatches.
Put criteria in cells
A criterion buried in a formula is invisible. Reference a cell instead.
Use * for AND, + for OR
Do not use AND() or OR(). They collapse the array and break the filter.
Leave room for the spill
Blank rows below and columns to the right. Otherwise #SPILL!.
Reference the spill with #
If the formula is in E5, E5# refers to the whole result and grows automatically.
Wrap in SORT for reports
Raw FILTER output is in source order. SORT puts the most important rows first.
Check data types first
Text numbers and text dates cause FILTER to silently return nothing.
Avoid whole-column arrays
A:A forces FILTER to test a million rows. Use bounded ranges or Tables.
Document Excel version
FILTER is 2021+ only. If sharing with older Excel users, plan a fallback.
Practice Questions
Try these before opening the answers. They use the examples from earlier in the guide.
- Q1. What is the syntax of FILTER?
Answer:=FILTER(array, include, [if_empty]). - Q2. Write a FILTER that returns all rows where column A is "East".
Answer:=FILTER(A2:C100, A2:A100="East", "None"). - Q3. How do you combine two AND conditions in FILTER?
Answer: Multiply them:=FILTER(A2:C100, (A2:A100="East") * (C2:C100>1000), "None"). - Q4. How do you combine two OR conditions?
Answer: Add them:=FILTER(A2:C100, (A2:A100="East") + (A2:A100="West"), "None"). - Q5. What does
#SPILL!mean and how do you fix it?
Answer: The spilled range is blocked. Clear the cells in the way, or move the formula so it has space to expand. - Q6. What happens if FILTER finds no matches and no
if_emptyis supplied?
Answer: Excel returns#CALC!. - Q7. How do you sort a FILTER result by its third column descending?
Answer: Wrap it in SORT with a -1 order:=SORT(FILTER(A2:C100, A2:A100="East"), 3, -1). - Q8. What does the
#operator do after a cell reference?
Answer: It refers to the entire spilled range starting at that cell. If FILTER is in E5,E5#is the whole result.
Frequently Asked Questions
What is the FILTER function in Excel?
FILTER extracts the rows from a range that meet a condition and returns them as a spilled array. Its syntax is =FILTER(array, include, [if_empty]).
Which Excel versions have FILTER?
FILTER is available in Excel 2021, Excel for Microsoft 365, Excel for the web, Excel for iPad and iPhone, and Excel for Android. It is not available in Excel 2019 or earlier.
What does #SPILL! mean?
#SPILL! means something is blocking the range where the FILTER result needs to expand. Clear the cells below or beside the formula, and the result appears.
How do I combine multiple conditions in FILTER?
Multiply conditions for AND: (A2:A100="East")*(B2:B100>1000). Add conditions for OR: (A2:A100="East")+(A2:A100="West").
Can FILTER return just one column?
Yes. Set the array argument to a single column: =FILTER(C2:C100, A2:A100="East").
How do I sort a FILTER result?
Wrap FILTER inside SORT: =SORT(FILTER(A2:C100, B2:B100>1000), 1, -1).
Can FILTER return unique values?
Wrap it in UNIQUE: =UNIQUE(FILTER(A2:A100, B2:B100="East")).
What happens when FILTER finds no matches?
Without an if_empty argument, FILTER returns #CALC!. Supply a third argument, e.g. =FILTER(A2:C100, B2:B100>99999, "No matches").
Does FILTER work with dates?
Yes. Use real date values: =FILTER(A2:C100, (A2:A100>=DATE(2026,1,1))*(A2:A100<=DATE(2026,12,31))).
Can FILTER reference another sheet?
Yes. Prefix each range with the sheet name: =FILTER(Jan!A2:C100, Jan!B2:B100="East").
Why does FILTER return #VALUE!?
The array and include arguments have different row counts. Make sure both span the same rows.
Can I edit individual cells in a FILTER result?
No. A spilled range is owned by the top-left formula. To change a value, edit the source data or the FILTER formula itself.
Key Takeaways
- FILTER extracts the rows that match a condition and spills them into the cells below.
- The syntax is
=FILTER(array, include, [if_empty]). - FILTER requires Excel 2021 or Microsoft 365.
- Always supply
if_empty— otherwise you get#CALC!. - The
arrayandincludemust have the same number of rows. - Use
*for AND,+for OR. Do not use AND() or OR(). - Spilled ranges cannot be edited cell by cell — only the top-left cell holds the formula.
- Reference a spilled range with the
#operator:E5#. - Wrap in SORT for reports, SORTBY to sort by a hidden column, UNIQUE for distinct values.
SUM(FILTER(...))replaces SUMIFS when you want the whole list, not just the number.#SPILL!means something is blocking the result — clear the cells in the way.- Use real dates and clean text — text numbers and text dates cause silent empty results.
- Tables make FILTER formulas bulletproof: ranges grow automatically.
- Leave blank space below the formula so the spill has room to grow.
What to Learn Next
FILTER is the gateway to Excel's dynamic array family. Once it is comfortable, the natural next steps are the functions it works with most — the ones that sort, deduplicate, and reshape its output.
Start with the dynamic array functions that pair with FILTER — sort-function, unique-function, and sortby-function.
From there, move into the modern lookup and reshaping tools: xlookup-function, sequence-function, take-function, and drop-function.