Excel filters are your best friends when you’re dealing with large datasets and need to quickly zoom in on relevant information. Whether you’re sorting invoices by date, isolating products under ₹500, or finding entries with missing data, filters make your job faster and cleaner.
Let’s break down the different types of filters, how to apply them, and the shortcut keys that save your time!
🧂 What Are Filters in Excel?
Filters temporarily hide rows that don’t meet certain criteria, making it easier to focus on the data you need—without deleting anything. You can filter by values, conditions, cell color, dates, blanks, and much more.
🧪 Types of Filters in Excel
🔹 1. AutoFilter (Basic Filter)
- Adds dropdown arrows to column headers.
- Allows you to filter by value, text, number, or date.
- Most commonly used filter.
🔹 2. Custom Filter
- Set conditions like “greater than ₹500” or “starts with ‘A’”.
- Use logical operators (AND/OR).
🔹 3. Advanced Filter
- Allows filtering using a criteria range separate from your table.
- Can extract unique values or copy results to another location.
🔹 4. Filter by Color
- Filter cells based on fill color, font color, or conditional formatting.
🔹 5. Date Filters
- Quickly segment data by month, year, quarter, or today/yesterday.
🔹 6. Number Filters
- Filter using numeric conditions like:
- Greater than / Less than
- Between
- Top 10 items
🔹 7. Text Filters
- Filter by phrases using:
- Contains
- Begins with / Ends with
- Does not contain
🔹 8. Blank/Non-Blank Filters
- Instantly show cells that are either blank or filled.
⚙️ Methods to Apply Filters
📌 1. Using the Ribbon
- Select your data range.
- Go to Home > Sort & Filter > Filter OR
Data > Filter.
📌 2. Using Shortcut Key
- Select any cell in your data.
- Press
Ctrl + Shift + L➜ Adds dropdown filter arrows.
📌 3. Using Table Format
- Convert your data to an Excel Table (
Ctrl + T)—filters are added by default.
📌 4. Applying Advanced Filter
- Go to Data > Advanced.
- Set criteria range, output range, and choose whether to extract unique records.
✏️ Short Example
Imagine this dataset:
| Product | Category | Price |
|---|---|---|
| Pen | Stationery | ₹20 |
| Mouse | Electronics | ₹500 |
| Paper | Stationery | ₹100 |
| Laptop | Electronics | ₹45,000 |
Scenario 1: Filter by category
Use dropdown in Category column → Select “Stationery”.
✅ You’ll see only “Pen” and “Paper”.
Scenario 2: Filter by Price > ₹1000
Use Number Filter → “Greater than ₹1000”
✅ You’ll see only “Laptop”.
🆚 Filter Types Compared
| Filter Type | Use Case | Flexibility | Best For |
|---|---|---|---|
| AutoFilter | Quick dropdown filtering | ⭐⭐⭐ | Everyday sorting & filtering |
| Custom Filter | Conditions like >, <, contains | ⭐⭐⭐⭐ | Specific value filtering |
| Advanced Filter | Multiple criteria + copy results | ⭐⭐⭐⭐⭐ | Reporting, unique values |
| Color Filter | Filter by formatting | ⭐⭐⭐ | Highlighted rows |
| Table Filter | Auto-applied filters + sorting | ⭐⭐⭐⭐ | Dynamic tables |
📝 Conclusion
Filters are more than just dropdowns—they’re data power tools. Whether you’re managing inventory, crunching survey results, or reviewing monthly performance, filters let you control the view without altering the data. Learn the shortcut keys, know your filter types, and you’ll be working smarter—not harder—in Excel.