How to Use the FILTER Function in Excel
The FILTER function returns only the rows that meet a condition — live, no manual filtering needed. Here's the syntax, single and multi-condition examples, and how to combine it with SORT.
The FILTER function (available in Microsoft 365 and Excel 2021+) returns a filtered copy of your data based on conditions you define — without touching the original data or using the AutoFilter menu. The results are live: change the source data and the filtered output updates instantly.
The syntax
=FILTER(array, include, [if_empty])- array — the range to filter
- include — a logical test that returns TRUE/FALSE for each row
- [if_empty] — what to show if no rows match (optional but good practice)
Basic example
Return all rows where column B (Region) equals "West":
=FILTER(A2:D100, B2:B100="West", "No results")This spills the matching rows into the cells below. No need to set a range size — FILTER automatically expands to fit the results.
Multiple conditions with AND
Both conditions must be true — multiply the conditions together:
=FILTER(A2:D100, (B2:B100="West")*(C2:C100>1000), "No results")Returns rows where Region is West and Revenue is over 1000. The * acts as AND for arrays.
Multiple conditions with OR
Either condition is enough — add the conditions together:
=FILTER(A2:D100, (B2:B100="West")+(B2:B100="South"), "No results")Returns rows where Region is West or South. The + acts as OR.
✦ TIP
When combining AND and OR, use parentheses to control precedence: (condition1 * condition2) + condition3 means "(1 AND 2) OR 3".
Filter with a number threshold
=FILTER(A2:D100, C2:C100>=5000, "No deals above threshold")Combining FILTER with SORT
FILTER returns results in source order. Wrap in SORT to order the output:
=SORT(FILTER(A2:D100, B2:B100="West"), 3, -1)Returns West region rows sorted by column 3 (the 3rd column of the filtered result) descending.
FILTER vs AutoFilter
| AutoFilter | FILTER function | |
|---|---|---|
| Hides rows | ✓ (in-place) | ✗ (outputs copy) |
| Live updates | ✗ Manual refresh | ✓ Automatic |
| Works in formulas | ✗ | ✓ |
| Available in | All versions | 365 / 2021+ only |
→ NOTE
FILTER requires Microsoft 365 or Excel 2021+. If you're on an older version, use AutoFilter or a helper column with COUNTIFS for similar results.
The Griddy way
FILTER shines for dashboard-style reporting where you want a live summary view of a subset of your data. Just describe what you need:
"Show me a live table of all open deals over $10,000 from the Q2 pipeline, sorted by close date"
Griddy builds the FILTER + SORT combination and places it in the right spot on your sheet.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.