How to filter rows based on a condition with a formula in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:C10,B2:B10=G1)Use =FILTER(table, condition). It returns every matching row and spills the result below the formula.
- Result on the example
- a 5-row list that spills from G4 in
G4 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G4. Change any input and the result updates, or edit the formula to experiment.
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.
=FILTER(A2:C10,B2:B10=G1)Result{5 rows × 3 cols} "Northwind", "West", 1840, "Fabrikam", …
A2:C109 rows × 3 cols: "Northwind", "West", $1,840, "Contoso", …A range: 9 rows × 3 columns.
B2:B109 rows × 1 col: "West", "East", "West", "North", …A range: 9 cells in one column.
G1"West"The value in G1.
B2:B10=G1{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:C10,B2:B10=G1){5 rows × 3 cols} "Northwind", "West", 1840, "Fabrikam", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C109 rows × 3 cols: "Northwind", "West", $1,840, "Contoso", … - condition1
B2:B10=G1{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:C10,B2:B10=G1). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:C10,B2:B10=G1,"No matches") | spills 5 × 3 | 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:B10forA2:C10. - FILTER needs Excel 2021, Microsoft 365 or Google Sheets. Older Excel shows #NAME?.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Filter rows based on a condition with a formula
G4, write one formula that returns every row of A2:C10 for the region in G1.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.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.