FILTER Function in Excel and Google Sheets
Purpose
Return all rows from a range that match a condition, spilling results dynamically
Return value
A dynamic array of matching rows that spills into adjacent cells automatically
FILTER is the function that changed how I extract subsets of data from a table. Before it existed, filtering to just the North region rows meant either applying AutoFilter (which hides rows rather than extracting them), building a helper column with IF to flag matching rows, or writing a complex array formula. FILTER does it in one formula that spills the matching rows directly into the sheet and updates automatically as data changes. Write it once, point it at your data range, and the filtered output is always live. The condition can be any expression that produces TRUE or FALSE per row — a text match, a number comparison, a date check, an ISBLANK test, or multiple conditions combined with * for AND or + for OR. The third argument handles the no-match case so reports never show a raw #CALC! error. FILTER is available in Excel 365, Excel 2021, and all versions of Google Sheets — if your file needs to work in older Excel versions, helper-column approaches or the FILTER formula workaround with array formulas are the alternatives.
Syntax
✓ Excel 365 / 2021
✓ Google Sheets
= Same syntax
=FILTER(array, include, [if_empty])
Arguments
| Argument | Required | Description |
|---|---|---|
| array | Required | The full range to filter — all rows and columns you want in the output. For example A2:C100 returns all three columns for matching rows. |
| include | Required | A boolean expression evaluated for each row in array. Rows where include is TRUE are returned. For example B2_B100="North" produces a TRUE/FALSE array of the same height as array. |
| if_empty | Optional | What to show when no rows match. A text string like "No results", an empty string, or any value. Without this, FILTER returns #CALC! when nothing matches. |
How it works
FILTER evaluates the include argument against every row in array. Each row where include is TRUE is included in the output; rows where include is FALSE are skipped. The results spill into adjacent cells automatically — the output range expands or contracts as data changes. Do not put anything in the cells below or to the right of the formula cell — FILTER needs those empty to spill into them. A #SPILL! error means those cells are occupied. For AND logic, multiply two boolean arrays: (B2:B100="North")*(C2:C100>1000) is TRUE only where both conditions are true. For OR logic, add them: (B2:B100="North")+ (B2:B100="South") is TRUE where either condition is true. To filter by a value in another cell rather than a hard-coded value, reference the cell directly in the include condition — change that cell and the filtered output updates instantly. Combine FILTER with SORT to return results in a specific order, or with UNIQUE to return only distinct rows from the filtered output.
Examples
1
Extract all sales rows where the Region is North.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
Row 2: Alice, North, 4200
— FILTER returns the four rows where Region equals North: Alice 4200, Dan 3800, Finn 5100, Hugo 4700. All three columns spill into the output.
Enter the formula in one cell only. FILTER spills the results into the cells below and to the right automatically. Those cells must be empty. A #SPILL! error means the spill range is blocked.
2
Extract only rows where Sales exceed 4000.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
Row 2: Alice, North, 4200
— Five reps have sales above 4000: Alice 4200, Carol 5500, Ella 4900, Finn 5100, Hugo 4700. All five rows spill into the output.
C2:C11>4000 evaluates each Sales value and returns TRUE or FALSE per row. Dan at 3800, Bob at 3100, Gia at 2800, and Iris at 3600 all evaluate to FALSE and are excluded from the output.
3
Use a cell reference as the filter value so the region can be changed without editing the formula — an interactive report filter.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | E1: South | Rep | Region | Sales |
| 2 | Filter → | Alice | North | 4200 |
| 3 | Bob | South | 3100 | |
| 4 | Carol | East | 5500 | |
| 5 | Dan | North | 3800 | |
| 6 | Ella | South | 4900 | |
| 7 | Finn | North | 5100 | |
| 8 | Gia | East | 2800 | |
| 9 | Hugo | North | 4700 | |
| 10 | Iris | South | 3600 | |
| 11 | Jo | East | 4100 |
Row 2: Bob, South, 3100
— E1 contains South. FILTER returns the three South rows: Bob 3100, Ella 4900, Iris 3600. Change E1 to East and the output instantly shows Carol, Gia, and Jo.
Combining a cell reference condition with a data validation dropdown in E1 creates a fully interactive region filter with no code or macros. Change the dropdown selection and the filtered table updates in real time.
Common use cases
1. Extract all rows for a specific region, department, or category from a large dataset
=FILTER(A2:D100,B2:B100="North","No results")
2. Show only orders above a revenue or quantity threshold for a high-value analysis view
=FILTER(A2:C100,C2:C100>1000,"No results")
3. Build an interactive filtered view using a dropdown cell as the filter condition
=FILTER(A2:D100,B2:B100=G1,"No results")
4. Extract only non-blank rows from a column that has gaps using a not-blank condition
=FILTER(A2:C100,A2:A100<>"","No results")
5. Filter for two regions at once using OR logic with the + operator
=FILTER(A2:C100,(B2:B100="North")+(B2:B100="South"),"No results")
Common errors
#SPILL! error
The cells below or to the right of the FILTER formula are not empty. FILTER cannot spill results into occupied cells.
Fix: Clear the spill range. FILTER needs a blank area equal to the maximum rows it might return across all output columns.
#CALC! error
No rows matched the condition and no if_empty argument was given.
Fix: Add the third argument: =FILTER(A2:C100,B2:B100="North","No results"). It replaces the error with a message or empty string.
#NAME? on the FILTER function
FILTER is not available in Excel 2019 or earlier. The function name is unrecognised in older versions.
Fix: Use Excel 365 or Excel 2021. For older Excel, use Advanced Filter from the Data menu or a helper column with IF and COUNTIF.
Tips and variations
Always add the if_empty argument to prevent #CALC!
Without the third argument, FILTER errors when no rows match. Adding "No results" or "" ensures the formula always produces a clean output — essential for any report that might legitimately have zero matching rows.
=FILTER(A2:C11,B2:B11="North","No results")
Combine with SORT to return filtered results in order
Wrap FILTER inside SORT to get a sorted filtered result in one formula: =SORT(FILTER(A2:C11,B2:B11="North","No results"),3,-1) sorts the North rows by Sales descending. The two functions compose cleanly because FILTER spills an array that SORT can sort directly.
=SORT(FILTER(A2:C11,B2:B11="North"),3,-1)
Use * for AND and + for OR to combine conditions
Multiplying two boolean arrays requires both to be TRUE. Adding them requires either to be TRUE. This is the pattern for filter by multiple conditions — it extends to as many conditions as needed without changing the FILTER function itself.
=FILTER(A2:C11,(B2:B11="North")*(C2:C11>4000),"No results")
Excel vs Google Sheets
Excel vs Google Sheets
FILTER is available in Excel 365, Excel 2021, and all versions of Google Sheets. It is not available in Excel 2019 or earlier. The syntax and spill behaviour are identical in Excel 365 and Google Sheets. The if_empty argument works the same in both applications.
Related reading
Related Functions
Frequently asked questions
FILTER returns a dynamic array of all rows from a range that match a condition. The results spill automatically into adjacent cells and update whenever the source data changes. Unlike manual AutoFilter or helper columns, FILTER is a live formula — add or change data and the filtered output updates instantly.
No. FILTER was introduced in Excel 365 and Excel 2021. It is not available in Excel 2019, 2016, or earlier versions. In those versions, use helper columns with IF and COUNTIF, Advanced Filter, or pivot tables. FILTER is available in all modern versions of Google Sheets.
Use the third argument — if_empty: =FILTER(A2:C100,B2:B100="North","No results"). Without it, FILTER returns #CALC! in Excel or an empty array error in Google Sheets when nothing matches. The if_empty argument can be a text string, an empty string, or any value you want to display when the filter finds no matches.
Yes. Multiply boolean arrays for AND logic — all conditions must be true: =FILTER(A2:C100,(B2:B100="North")*(C2:C100>1000)). Add boolean arrays for OR logic — either condition qualifies: =FILTER(A2:C100,(B2:B100="North")+(B2:B100="South")). These operators apply element-wise across the rows.
Narrow the array argument to include only the columns you want. =FILTER(A2:A100,B2:B100="North") returns only column A for matching rows. =FILTER(A2:B100,B2:B100="North") returns columns A and B. You cannot skip or reorder columns in the array argument — return the columns you want as a contiguous range.