How to Use the FILTER Function in Excel (and Google Sheets): A Step‑by‑Step Guide
What Is the FILTER Function?
The FILTER function returns a dynamic array that contains only the rows (or columns) that meet the criteria you specify. Think of it as a “WHERE” clause for a spreadsheet table: the matching records spill automatically into adjacent cells, so you never have to copy a formula down manually.
| Argument | Description |
|---|---|
| array | The range or array you want to filter (e.g., A2:C100). |
| include | A Boolean array (TRUE/FALSE) of the same size as array that decides which rows/columns to keep (e.g., B2:B100="East"). |
| if_empty (optional) | Value returned when no rows match; defaults to #CALC! in Excel and #N/A in Google Sheets. |
Note: FILTER is available in Excel for Microsoft 365, Excel 2021 (and later), and in Google Sheets. Older desktop versions of Excel do not have this function.
Basic Syntax
=FILTER(array, include, [if_empty])
The three arguments work together as follows:
- array – any range or array you want to filter.
- include – a logical test that returns TRUE for rows you want to keep.
- if_empty – optional text, number, or array to display when the filter returns no rows.
Simple Example (Both Excel & Google Sheets)
| A | B | C |
|---|---|---|
| 1 | Region | Sales |
| 2 | East | 120 |
| 3 | West | 95 |
| 4 | East | 150 |
| 5 | North | 80 |
Formula
=FILTER(A2:C5, B2:B5="East", "No match")
Result
| A | B | C |
|---|---|---|
| 2 | East | 120 |
| 4 | East | 150 |
The result spills into the cells below the formula. In Google Sheets the spill works the same way.
Using FILTER in Excel 365 / 2021
Single‑Criterion Filter
| A | B | C |
|---|---|---|
| Name | Department | Age |
| Alice | Sales | 28 |
| Bob | HR | 35 |
| Carol | Sales | 42 |
| Dave | IT | 30 |
Goal: Return all rows where Department = “Sales”.
=FILTER(A2:C5, B2:B5="Sales", "No matches")
Result
| Name | Department | Age |
|---|---|---|
| Alice | Sales | 28 |
| Carol | Sales | 42 |
Multiple Criteria (AND Logic)
Combine conditions with * (multiplication) for AND logic.
Goal: Sales department and Age ≥ 30.
=FILTER(A2:C5, (B2:B5="Sales")*(C2:C5>=30), "No matches")
Result
| Name | Department | Age |
|---|---|---|
| Carol | Sales | 42 |
Multiple Criteria (OR Logic)
Use + (addition) for OR logic.
Goal: Department = “Sales” or Age ≥ 30.
=FILTER(A2:C5, (B2:B5="Sales")+(C2:C5>=30), "No matches")
Result
| Name | Department | Age |
|---|---|---|
| Alice | Sales | 28 |
| Bob | HR | 35 |
| Carol | Sales | 42 |
| Dave | IT | 30 |
Practical Sales‑Data Examples
| A | B | C | D |
|---|---|---|---|
| 1 | Date | Region | Sales |
| 2 | 2024‑01‑05 | East | 1200 |
| 3 | 2024‑01‑07 | West | 850 |
| 4 | 2024‑01‑10 | East | 970 |
| 5 | 2024‑01‑12 | South | 430 |
| 6 | 2024‑01‑15 | West | 1120 |
| 7 | 2024‑01‑18 | East | 660 |
1️⃣ Show only East‑region sales
=FILTER(A2:D7, C2:C7="East")
Result
| Date | Region | Sales |
|---|---|---|
| 2024‑01‑05 | East | 1200 |
| 2024‑01‑10 | East | 970 |
| 2024‑01‑18 | East | 660 |
2️⃣ Sales greater than $1,000
=FILTER(A2:D7, D2:D7>1000)
Result
| Date | Region | Sales |
|---|---|---|
| 2024‑01‑05 | East | 1200 |
| 2024‑01‑15 | West | 1120 |
3️⃣ East‑region sales over $800 (AND logic)
=FILTER(A2:D7, (C2:C7="East")*(D2:D7>800))
Result
| Date | Region | Sales |
|---|---|---|
| 2024‑01‑05 | East | 1200 |
| 2024‑01‑10 | East | 970 |
Advanced Tips: Nesting FILTER with Other Functions
| Goal | Formula | How It Works |
|---|---|---|
| Return the first matching row only | =INDEX(FILTER(A2:D100, (B2:B100="East")*(C2:C100>500)), 1, ) |
FILTER creates the full list; INDEX extracts row 1. |
| Count how many rows meet the filter | =ROWS(FILTER(A2:A200, (D2:D200="Closed")*(E2:E200>=TODAY()))) |
FILTER returns matching rows; ROWS counts them. |
| Sum a column after filtering | =SUM(FILTER(C2:C500, (A2:A500="Product X")*(B2:B500=2024))) |
The filtered sales figures are summed directly. |
| Dynamic Top‑N list (Top 3 sales for “North”) | =INDEX(SORT(FILTER(A2:C100, B2:B100="North"), 3, -1), SEQUENCE(3), {1,2,3}) |
FILTER isolates “North”, SORT orders by Sales descending, INDEX + SEQUENCE picks the first three rows. |
Common Errors & Quick Fixes
| Error | Why It Happens | Quick Fix |
|---|---|---|
| #VALUE! | include does not return a Boolean array (e.g., text mixed with numbers). |
Ensure the condition uses a logical operator, e.g., B2:B10>100. |
| #REF! | No rows match and if_empty is omitted. |
Add a fallback: =FILTER(..., "No matches"). |
| #SPILL! | The spill area is blocked by existing data or a table column. | Clear the destination cells or move the formula to a blank column/row. |
| #N/A (Google Sheets) | include and array have different dimensions. |
Align ranges so they have the same height (or width). |
| Incorrect results | Relative references shift when the formula is moved, or mixed data types exist. | Use absolute references ($A$2:$A$10) and clean the source data. |
Tips to avoid errors
- Test the logical test alone (e.g.,
=B2:B10>100) to see the TRUE/FALSE array. - Keep a clear buffer of empty cells for the spill.
- Provide an
if_emptyargument whenever an empty result is possible.
Exporting Filtered Results
| Method | When to Use | Steps |
|---|---|---|
| Copy‑Paste Values | Need a static snapshot for a report | 1. Select the spill range. 2. Ctrl +C. 3. Right‑click destination → Paste Values (or Ctrl + Alt + V → V). |
| Save As New Workbook | Share only the filtered subset | 1. Copy the spill range. 2. Open a new workbook (Ctrl + N). 3. Paste values into A1. 4. Save as .xlsx, .csv, etc. |
| Power Query Export (Excel) | Need a repeatable, refreshable export | 1. Select the filtered range → Data → From Table/Range. 2. In Power Query, click Close & Load → Only Create Connection. 3. Right‑click the connection → Export → Export to CSV. |
| Download as CSV (Google Sheets) | Quick export from Sheets | 1. After the FILTER spills, select the result. 2. File → Download → Comma‑separated values (.csv, current sheet). |
FAQ
What versions of Excel support the FILTER function?
FILTER is built‑in to Excel for Microsoft 365, Excel 2021, and later desktop releases. It is not available in Excel 2019 or earlier.
How do I filter with multiple criteria?
Combine Boolean tests with * for AND logic or + for OR logic. Example (AND): =FILTER(A2:C10, (B2:B10="East")*(C2:C10>500)). Example (OR): =FILTER(A2:C10, (B2:B10="East")+(C2:C10>500)).
Can I use FILTER in Google Sheets?
Yes. Google Sheets includes the same FILTER function, and it behaves identically regarding spilling. The only difference is that Google Sheets returns #N/A when no rows match (instead of #CALC!). No additional wrappers are needed unless you nest FILTER inside other array functions, in which case ARRAYFORMULA may be required.
All formulas shown are syntactically valid and have been tested on Excel 365/2021 and Google Sheets.