Excel FILTER Function: Dynamic Data Extraction
FILTER Function (Excel) is a Excel function that filter returns rows from a2:d100 where column c (amount) is over 1000 and column b (region) is east. Formula Genius generates and validates this formula automatically from a plain-English prompt.
FILTER extracts rows that match your criteria and spills the results automatically. No helper columns, no manual filtering.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.