What FILTER Does
FILTER returns only the rows (or columns) from a range that meet one or more conditions. Unlike QUERY, it uses standard spreadsheet syntax rather than SQL, making it quicker to write for straightforward filtering tasks. The output is a dynamic array that updates as your data changes.
Syntax
=FILTER(range, condition1, [condition2, ...])
| Parameter | Description |
|---|---|
| range | The range of data to filter. |
| condition1 | A column or row of TRUE/FALSE values, or an expression that produces them (e.g., B2:B > 100). |
| condition2, ... | Additional conditions. All conditions must be true for a row to appear (AND logic). |
Basic Examples
Example 1: Filter by a Single Condition
Show only orders over $500:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Customer | Amount |
| 2 | 1001 | Acme Co | 750 |
| 3 | 1002 | Globex | 320 |
| 4 | 1003 | Initech | 1200 |
=FILTER(A2:C4, C2:C4 > 500)
Result:
| Order | Customer | Amount |
|---|---|---|
| 1001 | Acme Co | 750 |
| 1003 | Initech | 1200 |
Example 2: Filter by Text Match
Show only rows where the status is "Pending":
=FILTER(A2:D100, D2:D100 = "Pending")
Example 3: Multiple Conditions (AND)
Show East region orders over $500:
=FILTER(A2:D100, B2:B100 = "East", C2:C100 > 500)
Listing multiple conditions as separate arguments applies AND logic — every condition must be met.
Advanced Examples
OR Logic
FILTER uses AND by default. For OR logic, combine conditions with +:
=FILTER(A2:D100, (B2:B100 = "East") + (B2:B100 = "West"))
The + operator treats the conditions as OR — rows from either East or West are included.
FILTER with SORT and UNIQUE
Build a sorted, deduplicated list of customers who placed orders above $1,000:
=SORT(UNIQUE(FILTER(B2:B500, C2:C500 > 1000)))
This chains three functions: FILTER narrows to high-value orders, UNIQUE removes duplicate customer names, and SORT alphabetizes the result.
Common Mistakes
- No matching rows. When no rows meet the conditions, FILTER returns a #N/A error. Wrap it in IFERROR to handle this gracefully:
=IFERROR(FILTER(...), "No results"). - Condition range size mismatch. Each condition must have the same number of rows as the filtered range.
FILTER(A2:C100, D2:D50 > 100)will error because the condition range is shorter. - Confusing AND and OR. Separate arguments to FILTER are AND conditions. For OR, you need to combine them with
+inside a single argument, wrapped in parentheses.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "show only East region orders over $500" — and the formula is generated for you. Install SheetAI to try it free.