FILTER by One Condition in Excel and Google Sheets
Purpose
FILTER is the function that changed how I extract subsets of data from a table. Before FILTER, getting just the…
FILTER is the function that changed how I extract subsets of data from a table. Before FILTER, getting just the North region rows meant either a manual AutoFilter, a helper column with IF, or a complex array formula. FILTER does it in one formula that spills the matching rows directly into the sheet and updates automatically whenever the source data changes. For more complex extracts, filter by multiple conditions stacks conditions with * and +, and UNIQUE and SORT can be wrapped around FILTER to deduplicate or order the results. Write it once and the filtered result is always current. The condition can be anything that evaluates to TRUE or FALSE for each row — a text match, a number comparison, a date check, or a combination using multiplication for AND logic or addition for OR logic. The third argument handles the case where no rows match, so reports never show a raw error. FILTER is available in Excel 365, Excel 2021, and all versions of Google Sheets. If you are on an older Excel version, helper-column approaches or advanced filtering are the alternatives.
Syntax
✓ Excel 365 / 2021
✓ Google Sheets
= Same syntax
=FILTER(array, include, [if_empty])
Arguments
| Argument | Required | Description |
|---|---|---|
| array | Required | The range or table to filter — all columns you want in the output, e.g. A2:C100. |
| include | Required | A logical expression that produces TRUE or FALSE for each row in array. Rows where include is TRUE are returned. For example B2_B100="North" or C2:C100>500. |
| if_empty | Optional | What to return when no rows match — a text string like "No results" or an empty string. Without this, FILTER returns #CALC! when nothing matches. |
How it works
FILTER evaluates the include argument for each row in the array. The include argument must be a logical expression that produces TRUE or FALSE for every row — for example, B2_B11="North" produces an array of TRUE and FALSE values, one per row. FILTER returns only the rows where the include argument is TRUE and spills them into adjacent cells automatically. The spill range expands or contracts as the number of matching rows changes. If no rows match, FILTER returns a #CALC! error in Excel or an error in Google Sheets — the third if_empty argument provides a fallback for this case. The condition in include can be any logical expression including comparisons (>500), text matches (="North"), IS functions (ISBLANK), or combinations using * for AND and + for OR between boolean arrays. To return only specific columns from the matched rows, narrow the array argument to those columns rather than the full table range.
Examples
1
Extract all rows from a sales table 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 four rows where Region equals North: Alice 4200, Dan 3800, Finn 5100, Hugo 4700. All three columns spill into the output range.
The formula goes in one cell and spills the results automatically. Do not type anything in the cells below and to the right of the formula — FILTER needs those cells empty to spill into them. If they are occupied, a #SPILL! error appears.
2
Filter the same table to return only rows where Sales are greater than 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.
The include condition C2:C11>4000 evaluates each Sales value and returns TRUE for the five rows above the threshold. Dan at 3800, Bob at 3100, Gia at 2800, and Iris at 3600 are excluded.
3
Use a cell reference as the filter condition so the region can be changed interactively without editing the formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Filter Region | Rep | Region | Sales |
| 2 | South | 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 updates to the three East rows.
This pattern — a cell reference as the filter value — turns a static formula into an interactive report filter. Combine with a data validation dropdown in E1 to let users pick the region from a list and see filtered results update in real time.
Common use cases
1. Extract all rows for a specific region from a multi-region sales table
=FILTER(A2:D100,B2:B100="North","No results")
2. Show only orders above a revenue threshold for a high-value transaction report
=FILTER(A2:C100,C2:C100>1000,"No results")
3. Filter by a dropdown cell value to build an interactive single-condition search
=FILTER(A2:D100,B2:B100=G1,"No results")
4. Return only non-blank rows from a column that has gaps
=FILTER(A2:C100,A2:A100<>"","No results")
5. Extract rows where a date column falls within the current month
=FILTER(A2:C100,MONTH(B2:B100)=MONTH(TODAY()),"No results")
Common errors
#SPILL! error
The cells below or to the right of the FILTER formula are occupied. FILTER cannot spill its results into those cells.
Fix: Clear the cells in the spill range. FILTER needs a blank area equal to the maximum number of rows it might return across all columns.
#CALC! error
No rows matched the condition and no if_empty argument was provided.
Fix: Add a third argument to FILTER: =FILTER(A2:C100,B2:B100="North","No results"). The if_empty argument replaces the error with a friendly message or empty string.
#NAME? on the FILTER function
FILTER is not available in Excel 2019 or earlier. The function name is not recognised in those versions.
Fix: Use Excel 365 or Excel 2021 for the FILTER function. For older Excel versions, use helper columns with IF and COUNTIF, or use Advanced Filter from the Data menu.
Tips and variations
Always add the if_empty argument to prevent #CALC! errors
When no rows match the condition, FILTER returns an error without the third argument. Adding "No results" or "" as the third argument ensures the formula always returns something clean, which is essential for shared reports.
=FILTER(A2:C11,B2:B11="North","No results")
Use a cell reference for the condition to build a live filter
Point the condition at a cell containing the filter value. Combine with a dropdown list in that cell and the filtered output updates instantly when a user picks a different option — no macros, no VBA.
=FILTER(A2:C11,B2:B11=E1,"No results")
Combine two conditions with * for AND and + for OR
Multiply boolean arrays for AND: (B2:B11="North")*(C2:C11>4000). Add them for OR: (B2:B11="North")+(B2:B11="South"). This is how FILTER scales to filter-by-multiple-conditions without changing the function.
=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 is identical in Excel 365 and Google Sheets. The if_empty argument behaves the same in both applications.
Related reading
Frequently asked questions
FILTER returns a dynamic array of rows from a range that match a condition. Unlike manual filtering, the results spill automatically into adjacent cells and update whenever the source data changes. You can filter by any condition — text match, number comparison, date range, or any logical expression.
No. FILTER was introduced in Excel 365 and Excel 2021. It is not available in Excel 2019, 2016, or earlier. In those versions, use advanced filtering, helper columns with IF, or pivot tables to achieve similar results. FILTER is available in all modern versions of Google Sheets.
FILTER returns a #CALC! error in Excel or an empty array error in Google Sheets when no rows match. To handle this gracefully, use the third argument: =FILTER(A2:C100,B2:B100="North","No results") returns the text No results instead of an error when there are no matching rows.
Yes. Use multiplication (*) between conditions for AND logic — both must be true: =FILTER(A2:C100,(B2:B100="North")*(C2:C100>1000)). Use addition (+) between conditions for OR logic — either can be true: =FILTER(A2:C100,(B2:B100="North")+(B2:B100="South")).
FILTER returns entire rows from the source range by default. =FILTER(A2:C100,B2:B100="North") returns all three columns A, B, and C for every matching row. To return only a specific column, narrow the first argument: =FILTER(A2:A100,B2:B100="North") returns only the values in column A for matching rows.