The FILTER function in Excel is a powerful dynamic array function that extracts records from a range based on one or more conditions. The results automatically update when the source data changes, making FILTER especially useful for reports, dashboards, data analysis, and dynamic lists.
1. Basic Single-Condition Filter
BASIC
Suppose you have an employee table and want to display only employees who work in the Engineering department.
| Employee | Department | Salary |
|---|---|---|
| Alice Wang | Engineering | 85000 |
| Bob Smith | Marketing | 68000 |
| Carol Lee | Engineering | 92000 |
| David Park | Finance | 74000 |
| Eva Chen | Engineering | 79000 |
Formula:
=FILTER(A2:C6, B2:B6="Engineering")Result:
| Employee | Department | Salary |
|---|---|---|
| Alice Wang | Engineering | 85000 |
| Carol Lee | Engineering | 92000 |
| Eva Chen | Engineering | 79000 |
How it works: The condition B2:B6="Engineering" creates a TRUE/FALSE array. FILTER returns only the rows where the condition evaluates to TRUE.
2. Filter Using Multiple Conditions
INTERMEDIATE · AND logic
Now suppose you want to find Engineering employees whose salary is greater than 80000. This requires two conditions to be TRUE at the same time.
| Employee | Department | Salary |
|---|---|---|
| Alice Wang | Engineering | 85000 |
| Bob Smith | Marketing | 68000 |
| Carol Lee | Engineering | 92000 |
| David Park | Finance | 74000 |
| Eva Chen | Engineering | 79000 |
Formula:
=FILTER(A2:C6, (B2:B6="Engineering") * (C2:C6>80000))Result:
| Employee | Department | Salary |
|---|---|---|
| Alice Wang | Engineering | 85000 |
| Carol Lee | Engineering | 92000 |
How it works: The asterisk * acts as AND logic. A row must satisfy both conditions: the department must be Engineering and the salary must be greater than 80000.
3. Filter Using OR Logic
INTERMEDIATE · OR logic
FILTER can also return records that meet either of two conditions. For example, suppose you want employees from either the Marketing or Finance department.
| Employee | Department | Salary |
|---|---|---|
| Alice Wang | Engineering | 85000 |
| Bob Smith | Marketing | 68000 |
| Carol Lee | Engineering | 92000 |
| David Park | Finance | 74000 |
| Eva Chen | Engineering | 79000 |
Formula:
=FILTER(A2:C6, (B2:B6="Marketing") + (B2:B6="Finance"))Result:
| Employee | Department | Salary |
|---|---|---|
| Bob Smith | Marketing | 68000 |
| David Park | Finance | 74000 |
How it works: The plus sign + acts as OR logic in this type of FILTER formula. A row is returned when either the Marketing condition or the Finance condition is TRUE.
4. Use if_empty When No Results Are Found
INTERMEDIATE · Error handling
The third argument of FILTER allows you to specify what should be displayed when no records match the condition.
| Product | Category | Stock |
|---|---|---|
| Laptop | Electronics | 12 |
| Desk | Furniture | 5 |
| Monitor | Electronics | 8 |
Suppose you want to filter products belonging to the Office category. No such product exists in the source data.
Formula:
=FILTER(A2:C4, B2:B4="Office", "No products in this category")Result:
⚠️ No products in this category
How it works: The third argument, "No products in this category", is the if_empty argument. Instead of returning the #CALC! error when no matching records are found, FILTER displays the specified message.
5. Combine FILTER with SORT
ADVANCED · Dynamic dashboard example
FILTER becomes even more powerful when combined with other dynamic array functions. In this example, we will filter orders above 500 and then sort the results by order amount from largest to smallest.
| Order ID | Customer | Amount |
|---|---|---|
| 1001 | Acme Corp | 320 |
| 1002 | Beta Inc | 780 |
| 1003 | Gamma LLC | 150 |
| 1004 | Delta Co | 1250 |
| 1005 | Epsilon Ltd | 540 |
Formula:
=SORT(FILTER(A2:C6, C2:C6>500), 3, -1)Result:
| Order ID | Customer | Amount |
|---|---|---|
| 1004 | Delta Co | 1250 |
| 1002 | Beta Inc | 780 |
| 1005 | Epsilon Ltd | 540 |
How it works: First, FILTER extracts only the orders where the amount is greater than 500. Then SORT sorts the filtered results using column 3 in descending order. This combination is useful for dynamic dashboards, top-customer reports, sales analysis, and ranking reports.
FILTER Function — Quick Reference
Syntax:
=FILTER(array, include, [if_empty])| Argument | Description |
|---|---|
| array | The range or array containing the data you want to filter. |
| include | A logical test that determines which rows or columns should be returned. |
| if_empty | Optional. The value displayed when no records satisfy the filter condition. |
Key Points to Remember
- FILTER is a dynamic array function.
- The result automatically spills into surrounding cells.
- Use
*to combine conditions with AND logic. - Use
+to combine conditions with OR logic. - Use the optional
if_emptyargument when no matching records may exist. - FILTER can be combined with functions such as SORT to build dynamic reports and dashboards.
FILTER is available in Excel 365, Excel 2021, and Excel for the web. Because it is a dynamic array function, the results automatically expand into the required cells.
