How to Use FILTER Function in Excel + Tips

Hello everyone! Today i will show you how to use FILTER function in Excel step by step. FILTER is available in Excel 365 and Excel 2021+.

What is FILTER Function in Excel?

The FILTER function help you extract matching rows or columns from your table automatically based on criteria you set. When your source data change, the filtered result update automatically! No need click Data Filter button or copy paste manually anymore.

📚 Related Tutorial: Want to sort your filtered results automatically? Check out How to Use SORT and SORTBY in Excel!

---

🛠️ Formula Syntax

=FILTER(array, include, [if_empty])

• array: The full data range you want to filter (example: A2:C5).
• include: The rule or condition to decide which rows to keep (example: B2:B5="IT").
• [if_empty] (Optional): Message to show if no matching row is found (example: "No results found"). Prevent #CALC! error!

---

📋 Sample Data Table (Follow Along)

To test all examples below, create this simple employee table in cell range A1:C5:

Row	Column A (Name)	Column B (Department)	Column C (Salary)
1	Name	Department	Salary
2	Sok	IT	$800
3	Dara	HR	$700
4	Lina	IT	$900
5	Sam	Sales	$750

---

📊 Simple Examples

Example 1: Basic Filter by Text (Filter IT Department)

=FILTER(A2:C5, B2:B5="IT")

Result: Excel automatically spills matching rows:

Name	Department	Salary
Sok	IT	$800
Lina	IT	$900

---

Example 2: Filter Using Cell Reference

Instead of typing "IT" inside formula, put your target department in cell E1 = "IT":

=FILTER(A2:C5, B2:B5=E1)

Now whenever you change cell E1 to "HR" or "Sales", your filtered table update automatically!

---

Example 3: Filter Numbers (Salary > 750)

=FILTER(A2:C5, C2:C5>750)

This extracts all employees with salary strictly greater than 750 (Sok & Lina).

---

Example 4: Multiple Conditions with AND (* Operator)

To filter employees in IT AND Salary > 750:

=FILTER(A2:C5, (B2:B5="IT") * (C2:C5>750))

💡 Remember: In FILTER formula, * (asterisk) means AND.

---

Example 5: Multiple Conditions with OR (+ Operator)

To filter employees in IT OR HR:

=FILTER(A2:C5, (B2:B5="IT") + (B2:B5="HR"))

💡 Remember: In FILTER formula, + (plus sign) means OR.

---

Example 6: Custom Message if No Result Found

If you search department "Finance" which does not exist in table:

=FILTER(A2:C5, B2:B5="Finance", "No results found")

Instead of showing #CALC! error, Excel cleanly displays: "No results found".

---

Example 7: Filter Text Containing a Specific Word (Partial Search)

Use ISNUMBER + SEARCH to match partial text inside name:

=FILTER(A2:C5, ISNUMBER(SEARCH("Da", A2:A5)), "Not found")

This filters all rows where employee name contains "Da" (matches "Dara").

---

⚡ Dynamic Array Functions Comparison

Function	What It Does	Example
FILTER	Extract data rows matching condition	=FILTER(A2:C5, B2:B5="IT")
SORT	Sort range by column index	=SORT(A2:C5, 3, -1)
UNIQUE	Extract unique distinct items	=UNIQUE(B2:B5)
SORTBY	Sort range by another array	=SORTBY(A2:A5, C2:C5, -1)
XLOOKUP	Lookup single matching item	=XLOOKUP(E1, A2:A5, B2:B5)

---

💡 Quick Tips for FILTER Function:

1. Avoid #SPILL! Error: FILTER returns dynamic array. Make sure cells below and to the right of your formula are completely empty!
2. Combine with SORT & UNIQUE: You can nest formulas together, like =SORT(FILTER(A2:C5, B2:B5="IT")) to filter AND sort at the same time!

---

📚 Related Resources & Tutorials

• How to Use SORT and SORTBY Functions in Excel
• VLOOKUP vs XLOOKUP in Excel: When to Use Each
• SUMIF vs SUMIFS in Excel: Easy Guide
• Top 15 Basic Excel Formulas for Beginners ↗