# How to filter rows based on a condition with a formula in Excel

Use =FILTER(table, condition). It returns every matching row and spills the result below the formula.

**Excel:** `=FILTER(A2:C10,B2:B10=G1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Customer | Region | Amount |  |  | Region | West |
| Northwind | West | 1840 |  |  | At least | 1000 |
| Contoso | East | 420 |  |  |  |  |
| Fabrikam | West | 395 |  |  |  |  |
| Litware | North | 1720 |  |  |  |  |
| Adatum | West | 2210 |  |  |  |  |
| Proseware | East | 1955 |  |  |  |  |
| Wingtip | West | 610 |  |  |  |  |
| Tailspin | South | 380 |  |  |  |  |
| Lucerne | West | 1100 |  |  |  |  |

Formula in G4: `=FILTER(A2:C10,B2:B10=G1)`

Result (spills 5 × 3): Northwind, West, $1,840; Fabrikam, West, $395; Adatum, West, $2,210; Wingtip, West, $610; Lucerne, West, $1,100

## How it works

FILTER keeps the rows of `A2:C10` where the condition `B2:B10=G1` is TRUE. The result spills: one formula fills as many rows as there are matches, and it updates when the data or the region in `G1` changes.

## Other ways

- `=FILTER(A2:C10,B2:B10=G1,"No matches")`: The third argument is shown when nothing matches, instead of #CALC!.

## Common mistakes

- The spill range must be empty. If anything is typed below or beside the formula, you get #SPILL!.
- The condition must be the same height as the table: `B2:B10` for `A2:C10`.
- FILTER needs Excel 2021, Microsoft 365 or Google Sheets. Older Excel shows #NAME?.

## Questions

**How do I return only some columns?**

Filter just those columns: `=FILTER(A2:A10,B2:B10=G1)` returns the customers. In Excel 365, CHOOSECOLS picks columns that aren't next to each other.

**How do I sort the filtered result?**

Wrap it in SORT: `=SORT(FILTER(A2:C10,B2:B10=G1),3,-1)` sorts by amount, largest first.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/filter-rows-based-on-criteria