FILTER Function in Google Sheets
Use FILTER to return only the rows that meet one or more conditions — a live, self-updating subset of your data without touching the original.
=FILTER(A2:C, B2:B>100)
Returns every row of A2:C where the matching value in column B is greater than 100. The result updates automatically when the data changes.
How it works
FILTER takes a range to return and one or more conditions that are the same height as that range. Each condition is a TRUE/FALSE array; a row is kept only where its condition is TRUE. Stack conditions with commas to require all of them (AND), or multiply/add arrays for finer control. FILTER spills its result into the cells below and to the right, so leave that space empty.
Variations
Two conditions (both must hold)
=FILTER(A2:C, B2:B>100, C2:C="Paid")
Commas act like AND — a row survives only if every condition is TRUE.
Either condition (OR logic)
=FILTER(A2:C, (B2:B>100)+(C2:C="VIP"))
Adding conditions with + keeps a row when at least one is TRUE.
Show a message when nothing matches
=FILTER(A2:C, B2:B>100, "none found")
The last text argument is returned instead of #N/A when no rows match.
Filter then sort the result
=SORT(FILTER(A2:C, B2:B>100), 2, FALSE)
Wrap FILTER in SORT to order the surviving rows — here by column 2, descending.
Examples
| Scenario | Formula |
|---|---|
| Orders above a value | =FILTER(Orders!A2:D, Orders!C2:C>=500) |
| Rows from one region | =FILTER(A2:C, A2:A="West") |
| Exclude blanks in a column | =FILTER(A2:A, A2:A<>"") |
FAQ
Why does FILTER return #N/A?
No rows matched the conditions. Add a final text argument like "none found" to show a message instead, or check that your condition ranges are the same height as the data range.
How do I use OR with FILTER?
Add the conditions together with + inside FILTER, e.g. FILTER(A2:C, (B2:B>100)+(C2:C="VIP")). A comma means AND; a plus means OR.
Why do I get a #REF! spill error?
FILTER needs empty cells below and to the right to place its results. Clear anything in the way of the spill range.