The FILTER function is a Dynamic Array function that extracts data from a range based on specific conditions.
It automatically returns all matching results and updates when the data changes.
Syntax
=FILTER(array, include, [if_empty])
Arguments:
- array → The range to filter
- include → Condition to apply
- if_empty (optional) → Value to return if no match found
Basic Example
If A2:A10 contains numbers:
=FILTER(A2:A10, A2:A10>50)
This returns all numbers greater than 50.
Filtering Multiple Columns
If you have:
| Name | Department | Salary |
To filter IT department:
=FILTER(A2:C10, B2:B10="IT")
Returns all rows where Department = IT.
Multiple Conditions (AND Logic)
Use * (multiplication):
=FILTER(A2:C10, (B2:B10="IT")*(C2:C10>50000))
Returns rows where:
- Department = IT
AND - Salary > 50000
Multiple Conditions (OR Logic)
Use + (addition):
=FILTER(A2:C10, (B2:B10="IT")+(B2:B10="HR"))
Returns IT OR HR department data.
Using if_empty Argument
=FILTER(A2:A10, A2:A10>100, "No Data Found")
If no value is greater than 100, Excel shows:
No Data Found
FILTER with Tables (Recommended)
If your data is in a table named SalesTable:
=FILTER(SalesTable, SalesTable[Department]="IT")
Structured references make formulas cleaner and dynamic.
Common Errors
#SPILL!
Occurs when output range is blocked.
#CALC!
Occurs when no results and no if_empty value provided.
Important Notes
- Available in Excel 365 and Excel 2021+
- Automatically spills results
- Updates instantly when source data changes
- Works well with SORT and UNIQUE
Example: FILTER + SORT
=SORT(FILTER(A2:C10, B2:B10="IT"))
Filters IT department and sorts results automatically.
Benefits of FILTER
- No need for AutoFilter
- Dynamic results
- Cleaner formulas
- Powerful reporting
- Ideal for dashboards
Conclusion
The FILTER function is a powerful tool for extracting specific data dynamically. It simplifies complex filtering tasks and makes Excel reports more flexible and professional.