FILTER: Dynamic Array Filtering (2025)
Quick Answer: FILTER extracts matching data: =FILTER(A2:C100, B2:B100>50) returns rows where B>50. Dynamic array - spills automatically. Multiple conditions: use * for AND, + for OR. =FILTER(data, (col1='X')*(col2>10)) filters by both. Include if_empty: =FILTER(..., 'No results') avoids errors.
What is FILTER?
FILTER is a dynamic array function in Excel 365 that extracts rows or columns from a range that meet specified criteria. Results update automatically when source data changes - no manual refresh needed.
⚠️ Excel 365 Only
FILTER requires Excel 365 subscription with dynamic arrays support. Not available in Excel 2019, 2016, or earlier versions.
Alternative for older Excel: Use SUMIFS, COUNTIFS, or Advanced Filter (manual refresh required).
Visual Example:
Source Data (A1:C7):
| Name | Sales | Region |
|---|---|---|
| John | 45 | East |
| Sarah | 82 | West |
| Mike | 38 | East |
| Lisa | 95 | North |
| Tom | 41 | South |
| Emma | 67 | West |
FILTER Result (Sales > 50):
=FILTER(A2:C7, B2:B7>50)
| Name | Sales | Region |
|---|---|---|
| Sarah | 82 | West |
| Lisa | 95 | North |
| Emma | 67 | West |
3 rows automatically extracted
Why Use FILTER?
✓ FILTER Advantages:
- • Automatic updates when data changes
- • No manual filter refresh needed
- • Spills to adjacent cells automatically
- • Combine with other dynamic arrays
- • Multiple criteria with simple syntax
- • Returns entire rows/columns
✗ Traditional Filters:
- • Manual refresh required
- • Hidden rows, not extracted
- • Affects entire worksheet
- • Can't use in formulas
- • Complex multi-criteria setup
- • Not formula-based
Common Use Cases:
✓ Dynamic Dashboards
Extract data by date range, region, or status for real-time reporting
✓ Top Performers
Show only records above threshold automatically
✓ Multi-Criteria Reports
Combine conditions: Region="West" AND Sales>1000
✓ Search/Lookup Alternative
Replace complex VLOOKUP with readable FILTER
💡 Dynamic Array Spilling
FILTER spills results: Formula in one cell, results fill multiple cells automatically. Ensure cells below/right are empty or you'll get #SPILL! error.
Dynamic size: Result range changes automatically as filtered data grows/shrinks. No need to adjust ranges manually.
FILTER Syntax
=FILTER(array, include, [if_empty])
Arguments
array (required)
Range or array to filter
include (required)
Boolean array indicating which rows/columns to include (TRUE) or exclude (FALSE)
Single condition: B2:B100>50
AND logic: (B2:B100>50)*(C2:C100="Active")
OR logic: (B2:B100="US")+(B2:B100="UK")
if_empty (optional)
Value to return if no rows match criteria
With: returns custom message like "No results found" or 0
Common Patterns
Simple Filter (Single Condition)
=FILTER(A2:C100, B2:B100>50)
Show rows where column B > 50
AND Logic (Multiple Conditions)
=FILTER(A2:C100, (B2:B100>50)*(C2:C100="Active"))
Column B > 50 AND column C = "Active"
OR Logic (Either Condition)
=FILTER(A2:C100, (B2:B100="US")+(B2:B100="UK"))
Column B = "US" OR "UK"
With Error Handling
=FILTER(A2:C100, B2:B100>50, "No records found")
Returns message if no matches
AND vs OR Logic
AND: Use * (multiplication)
ALL conditions must be TRUE. Example: Sales>100 AND Region="West" AND Status="Active"
OR: Use + (addition)
ANY condition can be TRUE. Example: Region="US" OR Region="UK" OR Region="CA"
Combined: Use () for grouping
(Condition1 OR Condition2) AND Condition3
⚠️ Important Notes
- • Dynamic array only: Requires Excel 365 subscription
- • Array sizes must match: Include array must be same height/width as filter array
- • Spills automatically: Results fill multiple cells - ensure space is clear
- • #SPILL! error: Blocked by data in spill range - clear cells or move formula
- • #CALC! error: No matches found (use if_empty to avoid)
- • Updates automatically: Changes to source data refresh results instantly
- • Can't edit spill: Edit source formula only, not spilled cells
- • Use with SORT/UNIQUE: Combine for powerful data manipulation
12+ FILTER Examples
Basic Filtering
1. Filter by Number
=FILTER(A2:C100, B2:B100>1000)
Show rows where sales > 1000
2. Filter by Text
=FILTER(A2:C100, C2:C100="Active")
Show only active records
3. Filter by Date
=FILTER(A2:C100, D2:D100>=TODAY())
Show future dates only
Multiple Conditions (AND)
4. Two Conditions
=FILTER(A2:C100, (B2:B100>500)*(C2:C100="West"))
Sales > 500 AND region = West
5. Three Conditions
=FILTER(A2:D100, (B2:B100>500)*(C2:C100="West")*(D2:D100="Active"))
All three conditions must be TRUE
6. Date Range
=FILTER(A2:C100, (D2:D100>=DATE(2025,1,1))*(D2:D100<=DATE(2025,12,31)))
Filter by year 2025
Multiple Conditions (OR)
7. Multiple Regions
=FILTER(A2:C100, (B2:B100="US")+(B2:B100="UK")+(B2:B100="CA"))
Show US, UK, or CA records
8. Status Options
=FILTER(A2:C100, (C2:C100="Active")+(C2:C100="Pending"))
Active or Pending only
9. Combined AND/OR
=FILTER(A2:D100, ((B2:B100="US")+(B2:B100="UK"))*(C2:C100>1000))
(US or UK) AND sales > 1000
Advanced Applications
10. With Error Handling
=FILTER(A2:C100, B2:B100>1000, "No high-value sales found")
Custom message if no matches
11. Top N with SORT
=SORT(FILTER(A2:C100, B2:B100>500), 2, -1)
Filter then sort by column 2 descending
12. Dynamic Search
=FILTER(A2:C100, ISNUMBER(SEARCH(E1, A2:A100)))
Find text in column A (E1 = search term)
🎯 Pro Tips
- • Always include if_empty: Prevent #CALC! errors with user-friendly messages
- • Use named ranges: =FILTER(SalesData, ...) more readable than cell references
- • Combine with SORT: =SORT(FILTER(...)) for sorted filtered results
- • Use UNIQUE: =UNIQUE(FILTER(...)) to remove duplicates from results
- • Dynamic criteria: Reference cells for flexible filtering: B2:B100>E1
- • Check spill space: Ensure cells below/right are empty before creating FILTER
- • Excel Tables: Use structured references: =FILTER(Table1, Table1[Sales]>1000)
Frequently Asked Questions
Why doesn't FILTER work in my Excel?
FILTER requires Excel 365 subscription with dynamic arrays. Not available in Excel 2019, 2016, 2013, or earlier. Check: File → Account → About Excel. If you see 'Microsoft 365', you have it. If 'Excel 2019' or older, FILTER won't work. Alternative: Use Advanced Filter (Data tab) or SUMIFS/COUNTIFS for criteria-based results.
What does #SPILL! error mean?
#SPILL! means FILTER results are blocked by data in cells where results should spill. Fix: (1) Clear cells below/right of formula, (2) Move formula to area with empty space, (3) Delete blocking data. FILTER needs space to expand dynamically - can't overwrite existing data.
How do I filter with partial text match?
Use SEARCH or FIND with ISNUMBER: =FILTER(A2:C100, ISNUMBER(SEARCH('text', A2:A100))). Example: =FILTER(data, ISNUMBER(SEARCH('John', A:A))) finds 'John', 'Johnson', 'Johnny'. SEARCH is case-insensitive, FIND is case-sensitive. Wrap in ISNUMBER because SEARCH returns position or error.
Can I filter by multiple columns at once?
Yes, multiply conditions for AND logic: =FILTER(A2:D100, (B2:B100='West')*(C2:C100>1000)*(D2:D100='Active')). All conditions must be TRUE. For OR logic, use addition: =FILTER(A2:C100, (B2:B100='US')+(B2:B100='UK')). Combine: =FILTER(data, ((col1='A')+(col1='B'))*(col2>10)) for (A or B) AND col2>10.
How do I filter by current month or week?
Use date functions in include argument. Current month: =FILTER(A2:C100, (MONTH(D2:D100)=MONTH(TODAY()))*(YEAR(D2:D100)=YEAR(TODAY()))). Current week: =FILTER(A2:C100, (D2:D100>=TODAY()-WEEKDAY(TODAY())+1)*(D2:D100<=TODAY()-WEEKDAY(TODAY())+7)). Updates automatically each day.
Can FILTER extract specific columns only?
Yes, two approaches: (1) Specify columns in array: =FILTER(A2:C100, ...) only columns A-C. (2) Use CHOOSECOLS after: =CHOOSECOLS(FILTER(A2:E100, ...), 1, 3, 5) extracts filtered data then picks columns 1, 3, 5. Or INDEX: =INDEX(FILTER(...), , {1,3,5}) for specific columns from result.