Dynamic arrays
How to Use FILTER in Excel (Spill Results, with Examples)
FILTER returns only the rows from a range that meet the conditions you set, spilling the results into the cells below and to the right automatically. It replaces the old routine of copying, sorting, and deleting rows by hand, and it updates live as your data changes.
FILTER is a dynamic-array function, available in Excel 365, Excel 2021 and later, and Google Sheets. This guide covers the syntax, single and multiple conditions, AND and OR logic, and how to keep the formula from breaking when nothing matches.
FILTER syntax
=FILTER(array, include, [if_empty]) | Argument | Required | What it does |
|---|---|---|
array | Required | The range of data to return, such as A2:C100. These are the columns you want back. |
include | Required | A condition that evaluates to TRUE/FALSE for each row, like B2:B100="West". Rows that are TRUE are kept. |
if_empty | Optional | What to show when no rows match, such as "No results". Without it, an empty match returns #CALC!. |
Note: FILTER spills, so leave room below and to the right. The include range must be the same height as array.
Examples
Real, copy-paste-ready formulas.
1. Filter rows by one condition
=FILTER(A2:C100, B2:B100="West") Keeps only the rows where column B equals "West" and spills the matching rows into the grid. The include range B2:B100 must match the height of A2:C100.
2. Two conditions with AND
=FILTER(A2:C100, (B2:B100="West")*(C2:C100>500)) Multiplying two conditions acts as AND: a row is kept only when both are TRUE (1 times 1). Wrap each condition in parentheses.
3. Either condition with OR
=FILTER(A2:C100, (B2:B100="West")+(B2:B100="East")) Adding conditions acts as OR: a row is kept when at least one is TRUE. The plus sign is the array-formula way to say "or".
4. Handle no matches gracefully
=FILTER(A2:C100, B2:B100="West", "No results") The third argument supplies a fallback so an empty result returns "No results" instead of a #CALC! error, which is ideal for dashboards.
How to write FILTER step by step
- 1
Click the top-left cell where you want the results to appear and type =FILTER(
- 2
Select the full data range you want returned (the array), then type a comma.
- 3
Build the condition, such as B2:B100="West", using a column the same height as the array.
- 4
For multiple conditions, multiply them for AND or add them for OR, each in parentheses.
- 5
Optionally add a comma and a fallback like "No results", then close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#CALC! | No rows matched the condition and no if_empty argument was given. | Add a third argument such as "No results" to return a friendly value when nothing matches. |
#SPILL! | A cell in the spill range below or beside the formula is not empty. | Clear the cells the results need to fill, or move the formula to an open area. |
#VALUE! | The include range is a different height (or width) than the array. | Make the condition range exactly the same number of rows as the array, for example both rows 2 to 100. |
#NAME? | FILTER is not available in this version of Excel. | FILTER needs Excel 365 or Excel 2021. In older versions, use an Advanced Filter or a helper-column approach instead. |
Frequently asked questions
How do I filter on multiple conditions with FILTER?
Combine conditions inside the include argument: multiply them for AND, for example (B2:B100="West")*(C2:C100>500), or add them for OR, like (B2:B100="West")+(B2:B100="East"). Wrap each condition in parentheses.
Why does FILTER return #CALC!?
That means no rows matched and you did not supply a fallback. Add a third argument, such as =FILTER(array, include, "No results"), to return text instead of the error when nothing matches.
Can FILTER return just one column?
Yes. Set the array to a single column, such as =FILTER(A2:A100, B2:B100="West"), to spill only that column for the matching rows.
Does FILTER work in Google Sheets?
Yes. Google Sheets has FILTER and it spills the same way. The main difference is that Sheets lets you pass multiple conditions as separate arguments rather than multiplying them.