FILTER Function in Google Sheets: Complete Guide
SheetHub4 min
You have a spreadsheet with 10,000 rows. You only want to see the rows where the region is East and the amount is over $500. You could sort, hide, or delete — but that is slow and destructive.
FILTER does it instantly in one formula. It returns only the rows that match your conditions, and it updates automatically when your data changes.
FILTER is one of the most practical functions in Google Sheets. Unlike QUERY, it uses plain English conditions — no SQL required. Unlike sorting and hiding, it is non-destructive. Your original data stays untouched.
FILTER spills the result automatically into the cells below and to the right.
Numeric filter — amount over 500:
Text filter — product is Widget A:
Date filter — orders after January 1, 2026:
Separate conditions with commas — all must be TRUE:
This returns rows where region is East AND amount > 500.
Use
This returns rows where region is East OR West.
Count filtered results:
Sum filtered amounts:
Average of filtered results:
Use FILTER for simple extractions. Use QUERY when you need grouping, sorting, or aggregation.
Return specific columns only. Instead of returning the whole range, specify just the columns:
Use with SORT to order results:
Handle empty results with IFERROR:
Range size mismatch. Every condition range must be the same size as the data range. If your data has 99 rows but your condition has 100, you get an error.
Using AND inside FILTER. FILTER conditions already use AND logic when comma-separated. You do not need to wrap them in an AND function.
Forgetting to lock ranges. When copying FILTER formulas, use absolute references ($A$2:$A$100) or named ranges to prevent shifting.
Does FILTER exist in Excel? Yes, FILTER is available in Excel for Microsoft 365. It works similarly but with slightly different syntax.
Can FILTER return data to another sheet? Yes. Just put the FILTER formula in any sheet. It references the original range wherever it is.
Why is my FILTER returning #N/A? The most common cause is range mismatch — check that every condition range is the same size as the data range.
Why This Matters
FILTER — The Basics
Syntax
=FILTER(range, condition1, [condition2], ...)| Argument | Description |
|---|---|
range | The data to return |
condition1 | A TRUE/FALSE array (your first filter) |
condition2 | Optional. Additional filters (AND logic) |
Simple Example
Return all rows where region is East:=FILTER(A2:D100, B2:B100="East")Single Condition Filters
=FILTER(A2:D100, C2:C100>500)=FILTER(A2:D100, A2:A100="Widget A")=FILTER(A2:D100, D2:D100>DATE(2026,1,1))Multiple Conditions (AND)
=FILTER(A2:D100, B2:B100="East", C2:C100>500)OR Conditions
+ between conditions for OR:
=FILTER(A2:D100, (B2:B100="East") + (B2:B100="West"))FILTER with Other Functions
=COUNTA(FILTER(A2:A100, B2:B100="East"))=SUM(FILTER(C2:C100, B2:B100="East"))=AVERAGE(FILTER(C2:C100, B2:B100="East"))FILTER vs QUERY
| Feature | FILTER | QUERY |
|---|---|---|
| Syntax | Simple, English-like | SQL-like |
| Multiple conditions | Comma-separated | WHERE clause |
| GROUP BY | Not supported | Supported |
| ORDER BY | Not supported | Supported |
| Learning curve | Low | Medium |
Pro Tips
=FILTER({A2:A100, C2:C100}, B2:B100="East")=SORT(FILTER(A2:D100, B2:B100="East"), 3, TRUE)=IFERROR(FILTER(A2:D100, B2:B100="East"), "No matching data")Common Mistakes
Related Functions
- QUERY Function — More powerful alternative for complex data extraction
- SUMIF vs SUMIFS — Sum values in filtered data
- VLOOKUP vs XLOOKUP — Look up individual values instead of filtering
FAQ
Topics
Topics in this article
Explore related topics and continue reading similar content.
Share this article
Discussion
Preparing the comments area...