Learn how to use the FILTER function in Excel with real-life examples. Master dynamic filtering with conditions like text, numbers, dates, and more.
Have you ever wanted to extract a specific portion of data from a large Excel sheet — say, only the sales data for Mumbai or only products with prices above ₹500?
If you’re using Excel 365 or Excel 2021, there’s a powerful tool for this: the FILTER() function.
Unlike traditional methods like AutoFilter, VLOOKUP, or advanced filtering, the FILTER function allows you to extract data dynamically and with conditions, without needing macros or manual steps.
What is the FILTER Function?
Syntax:
=FILTER(array, include, [if_empty])
Arguments:
- array: The range you want to extract data from.
- include: The condition that must be met.
- [if_empty] (optional): What to show if no data matches.
Real-Life Use Cases & Examples
Example 1: Filter Sales Data by City
Goal:
Show only the sales data where City = Mumbai.
| Name | City | Sales |
|---|---|---|
| Ramesh | Delhi | 5000 |
| Suresh | Mumbai | 8000 |
| Anita | Mumbai | 6000 |
| Priya | Kolkata | 7000 |
Formula:
=FILTER(A2:C5, B2:B5="Mumbai", "No Sales in Mumbai")
Output:
| Name | City | Sales |
|---|---|---|
| Suresh | Mumbai | 8000 |
| Anita | Mumbai | 6000 |
Example 2: Filter Students Who Passed
| Student | Marks |
|---|---|
| Raj | 45 |
| Meena | 78 |
| Arjun | 67 |
| Komal | 34 |
Formula:
=FILTER(A2:B5, B2:B5>=40, "No Pass")
Output:
| Student | Marks |
|---|---|
| Raj | 45 |
| Meena | 78 |
| Arjun | 67 |
Example 3: Filter Products with Quantity > 0 and Price < ₹500
| Product | Qty | Price |
|---|---|---|
| Pen | 0 | 10 |
| Pencil | 50 | 5 |
| Marker | 30 | 550 |
| Eraser | 20 | 3 |
Formula:
=FILTER(A2:C5, (B2:B5>0)*(C2:C5<500), "No items found")
👉 The
*acts as AND logic in Excel.
Output:
| Product | Qty | Price |
|---|---|---|
| Pencil | 50 | 5 |
| Eraser | 20 | 3 |
Example 4: Filter Data Based on Drop-down Selection
| Name | City | Sales |
|---|---|---|
| Ramesh | Delhi | 5000 |
| Suresh | Mumbai | 8000 |
| Anita | Chennai | 6000 |
| Priya | Mumbai | 7000 |
Let’s say you have a drop-down list in cell F1 (with values like “Delhi”, “Mumbai”, “Chennai”) and want to filter employees by selected city.
Read More: Create a Dropdown List in Excel
Formula:
=FILTER(A2:C10, B2:B10=F1, "No match found")
👉 This is useful when you want dynamic reports that respond to user input.
Example 5: Filter by Month (Using Dates)
Suppose you have a date-wise sales list and want to filter data for March 2024.
| Date | Sales |
|---|---|
| 01/02/2024 | 4000 |
| 15/03/2024 | 7000 |
| 20/03/2024 | 6500 |
| 05/04/2024 | 8000 |
Formula:
=FILTER(A2:B5, TEXT(A2:A5, "mm-yyyy")="03-2024", "No sales")
Read More: TEXT Function in Excel
BONUS Tips
Use FILTER with SORT
To filter and sort in one step:
=SORT(FILTER(A2:B10, B2:B10>50), 2, -1)
Combine with UNIQUE
Want unique filtered values?
=UNIQUE(FILTER(A2:A20, B2:B20="Mumbai"))
When to Use FILTER Function?
Use FILTER() when you:
- Need live filtered data without using slicers.
- Want dynamic dashboards.
- Replace complex array formulas or helper columns.
- Automate reporting for specific departments, cities, users, etc.
Summary
| Feature | Description |
|---|---|
| Function Name | FILTER() |
| Introduced In | Excel 365, Excel 2021 |
| Replaces | AutoFilter, Advanced Filter, Helper Columns |
| Works With | Text, Numbers, Dates, Booleans |
| Best Use | Dashboards, Dynamic Reports, Conditional Data Extraction |
Final Thoughts
The FILTER() function is one of the most powerful Excel functions introduced in recent years. It can completely change the way you analyze, clean, and present your data — especially when working with large datasets.
Unlike traditional filtering using drop-downs or manual selection, FILTER() is dynamic, formula-based, and updates automatically when source data changes. This makes it an excellent tool for dashboards, automated reports, and interactive analysis.
Whether you’re creating sales reports, employee records, or student performance sheets, the FILTER() function helps you keep only what’s relevant, making your reports easier to read and more actionable.
Once you master it, you’ll save hours of manual effort and reduce errors significantly — a huge win for any MIS Executive, data analyst, or Excel enthusiast.
Download Sample Excel File
You can practice all the above examples using this downloadable file:
FAQs – FILTER Function in Excel
How to apply multiple conditions in FILTER?
Use * for AND and + for OR.
=FILTER(A2:C10, (B2:B10="Delhi")*(C2:C10>5000))
How to prevent error when no data found?
Use the 3rd argument:
=FILTER(..., ..., "No data available")
Can I filter data based on a partial text match?
Yes, you can use wildcard characters or the LEFT(....,LEN(...)) combination inside the filter logic.
=FILTER(A2:G31,LEFT(B2:B31),LEN(D1))=D1,"Not Found")
What’s Next?
In the next post, we’ll learn about the TEXTBEFORE & TEXTAFTER in Excel