Excel FILTER Function: Dynamic Data Extraction
FILTER extracts rows that match your criteria and spills the results automatically. No helper columns, no manual filtering.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Show all orders over $1000 from the East region"
=FILTER(A2:D100, (C2:C100>1000) * (B2:B100="East"), "No matching orders")FILTER returns rows from A2:D100 where column C (amount) is over 1000 AND column B (region) is East. The asterisk (*) acts as AND between conditions. The third argument provides a default value if no rows match.
Step-by-step breakdown
- FILTER(array, include, [if_empty]) takes the data range and criteria
- C2:C100>1000 creates a TRUE/FALSE array for the amount condition
- B2:B100="East" creates a TRUE/FALSE array for the region condition
- Multiplying (*) the arrays gives AND logic (both must be TRUE)
- "No matching orders" displays when zero rows match — prevents #CALC! error
Edge cases & warnings
- Without the if_empty argument, FILTER returns #CALC! when no rows match
- Results spill automatically — make sure there's enough empty space below
- Use + instead of * for OR logic between conditions
- Requires Excel 365 or Excel 2021+
Examples
"Region: East, Amount: $1500"
Included in results"Region: West, Amount: $2000"
Excluded (wrong region)"No matches found"
"No matching orders"Frequently asked questions
How do I filter with OR instead of AND?
Replace * with +. Example: =FILTER(data, (region="East")+(region="West")) returns rows where region is East OR West.
Can I sort the filtered results?
Yes. Wrap FILTER in SORT: =SORT(FILTER(data, criteria), column_number, 1) where 1 = ascending, -1 = descending.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.