Module 5 - Lesson 14
Filtering Data in Excel: AutoFilter, Text Filters, Number Filters, and Date Filters
Learn how to display only the data you need using Excel's powerful filtering capabilities. Master AutoFilter to quickly find specific values, apply text and number filters for precise criteria, filter by date ranges, filter across multiple columns, and filter by cell color. Find exactly what you are looking for in any dataset.
In This Lesson
What Is Filtering
Filtering is the process of temporarily hiding rows in your spreadsheet that do not match specific criteria while displaying only the rows that do match. Unlike sorting, which rearranges all your data, filtering selectively shows and hides rows without changing their position or deleting any information.
Consider a spreadsheet containing sales data for five hundred products. Without filtering, finding all products from a specific category requires scrolling through every row. With filtering, you click a dropdown, select the category you want, and instantly see only those products. Everything else temporarily disappears from view but remains safely in your spreadsheet.
Filtering is one of the most frequently used features in Excel for data analysis. It allows you to focus on specific subsets of data, answer targeted questions, and explore different aspects of your information without creating separate worksheets or modifying your original data.
Filtering vs Sorting
| Characteristic | Sorting | Filtering |
|---|---|---|
| What it does | Rearranges all rows in a specific order | Hides rows that do not match criteria |
| Data visibility | All data remains visible | Only matching data is visible |
| Row positions | Changes permanently until resorted | Unchanged, rows are only hidden |
| Reversibility | Requires resorting or undo | Clear filter to show all data instantly |
| Best for | Organizing data in order | Finding and analyzing specific data |
Filtering never deletes or modifies your data. Hidden rows are still present in your worksheet and are included in calculations unless you use special functions. When you clear a filter, all hidden rows reappear exactly where they were. This makes filtering completely safe for exploring your data.
Enabling AutoFilter
Before you can filter data, you need to enable AutoFilter, which adds dropdown arrows to your header row. These dropdowns provide access to all filtering options for each column.
How to Enable AutoFilter
- Click any cell within your data range. You do not need to select the entire range.
- Go to the Data tab on the Ribbon
- Click the Filter button in the Sort and Filter group
- Dropdown arrows appear in each cell of your header row
Keyboard Shortcut
Press Ctrl + Shift + L to toggle AutoFilter on or off. This is the fastest way to enable or disable filtering.
Understanding the Filter Dropdown
Once AutoFilter is enabled, small dropdown arrows appear in each header cell. These arrows indicate that filtering is available for that column. When you click a dropdown arrow, you see several options including sorting commands, a search box, a list of all unique values in the column, and access to advanced filter options based on the data type.
Excel automatically uses your first row as headers when you enable AutoFilter. If your data does not have headers, Excel will still add filter dropdowns to the first row, treating those values as headers. For best results, always include a clear header row with descriptive column names.
Basic Value Filtering
The simplest form of filtering is selecting specific values from the dropdown list. Every unique value in a column appears as a checkbox, and you choose which values to display by checking or unchecking them.
How to Filter by Specific Values
- Click the dropdown arrow in the column you want to filter
- Uncheck Select All to deselect all values at once
- Check the specific values you want to display
- Click OK to apply the filter
Visual Example
| Name | Department | Salary |
|---|---|---|
| Alice | Marketing | 55,000 |
| Bob | Sales | 62,000 |
| Carol | Finance | 58,000 |
| David | Sales | 67,000 |
| Eve | IT | 72,000 |
| Frank | Sales | 59,000 |
Green rows are visible. Strikethrough rows are hidden by the filter but still exist in the worksheet.
Selecting Multiple Values
You can check multiple values to display rows matching any of those values. For example, if you want to see both Sales and Marketing departments, check both boxes. All rows belonging to either department will be displayed.
When a filter is active, several visual indicators appear. The dropdown arrow changes to a funnel icon in filtered columns. Row numbers on the left turn blue and may skip numbers indicating hidden rows. The status bar at the bottom shows how many records are visible out of the total. Always check for these signs to know when data is filtered.
Text Filters
For columns containing text, Excel provides specialized text filters that let you apply criteria based on text patterns rather than exact values. These filters are accessible from the dropdown menu under Text Filters.
Available Text Filter Options
| Filter Option | What It Does | Example |
|---|---|---|
| Equals | Shows cells matching exact text | Equals "Manager" |
| Does Not Equal | Shows cells not matching text | Does Not Equal "Inactive" |
| Begins With | Shows cells starting with text | Begins With "Dr." |
| Ends With | Shows cells ending with text | Ends With "Jr." |
| Contains | Shows cells containing text anywhere | Contains "software" |
| Does Not Contain | Shows cells not containing text | Does Not Contain "test" |
Using Text Filters
- Click the dropdown arrow in a text column
- Hover over Text Filters to see the submenu
- Select your filter type such as Contains or Begins With
- Enter your criteria in the dialog box that appears
- Click OK to apply the filter
Using Wildcards
Text filters support wildcard characters for flexible matching. The asterisk character matches any sequence of characters, so typing sm*th matches both Smith and Smooth. The question mark matches any single character, so b?t matches bat, bet, bit, bot, and but.
Text filters in Excel are not case sensitive by default. Searching for smith will find Smith, SMITH, and smith. If you need case-sensitive filtering, you will need to use advanced filtering with custom formulas.
Number Filters
For columns containing numbers, Excel provides specialized number filters that let you apply mathematical criteria. These are invaluable for finding values within ranges, above or below thresholds, or matching specific numeric conditions.
Available Number Filter Options
| Filter Option | What It Does | Example Use |
|---|---|---|
| Equals | Shows cells with exact value | Find orders of exactly 100 units |
| Does Not Equal | Shows cells not matching value | Exclude zero values |
| Greater Than | Shows cells above a value | Sales greater than 10000 |
| Greater Than Or Equal To | Shows cells at or above a value | Scores of 70 or higher |
| Less Than | Shows cells below a value | Inventory less than 50 |
| Less Than Or Equal To | Shows cells at or below a value | Prices of 100 or less |
| Between | Shows cells within a range | Values between 1000 and 5000 |
| Top 10 | Shows highest or lowest values | Top 5 salaries |
| Above Average | Shows values above average | Above average performers |
| Below Average | Shows values below average | Below average scores |
Using Number Filters
- Click the dropdown arrow in a numeric column
- Hover over Number Filters to see the submenu
- Select your filter type such as Greater Than or Between
- Enter your criteria in the dialog box
- Click OK to apply the filter
Combining Number Conditions
The Custom AutoFilter dialog allows you to combine two conditions using And or Or logic. For example, you can filter for values greater than 1000 And less than 5000, which is equivalent to the Between filter. Or you can filter for values less than 100 Or greater than 900 to see both extremes.
Despite its name, the Top 10 filter is flexible. You can change the number to show the top 5, top 20, or any quantity you need. You can also switch between Items and Percent to show the top 10 percent instead of top 10 items. And you can toggle between Top and Bottom to see lowest values instead of highest.
Date Filters
For columns containing dates, Excel provides specialized date filters with convenient time-based options. These filters make it easy to find dates in specific periods without calculating exact date ranges yourself.
Available Date Filter Options
Year Filters
This Year, Last Year, Next Year, or specific years
Quarter Filters
This Quarter, Last Quarter, specific quarters
Month Filters
This Month, Last Month, specific months
Week Filters
This Week, Last Week, Next Week
Day Filters
Today, Yesterday, Tomorrow
Range Filters
Between, Before, After specific dates
Dynamic Date Filters
Many date filters are dynamic, meaning they update automatically based on the current date. If you filter for This Month and open the spreadsheet next month, you will see different results because This Month now refers to a different period. This is extremely useful for reports that need to show current data.
Using Date Filters
- Click the dropdown arrow in a date column
- Hover over Date Filters to see the submenu
- Select your time period or choose a comparison filter
- For custom ranges, select Between and enter start and end dates
- Click OK to apply the filter
Date filters only work correctly when Excel recognizes your values as actual dates. If your dates are stored as text, Date Filters will not appear in the menu, and you will only see Text Filters. Ensure your date column contains properly formatted dates. Properly recognized dates right-align in cells by default.
Filter by Color
If your data includes cell colors or font colors, either applied manually or through conditional formatting, you can filter to show only cells with specific colors. This is particularly useful when colors represent categories, status levels, or priorities.
How to Filter by Cell Color
- Click the dropdown arrow in a column containing colored cells
- Hover over Filter by Color to see available colors
- Click on the color you want to display
- Only rows with that color in that column will be shown
Filtering by Font Color
The same menu provides options to filter by font color. If you have text in different colors, you can show only rows where the text in a particular column is red, blue, or any other color used in your data.
Filtering by Icon
If you have applied conditional formatting icon sets to a column, you can filter by those icons. This allows you to show only rows with green checkmarks, red arrows, or any other icon from your conditional formatting rules.
You can only filter by one color at a time in a single column. If you need to see multiple colors, you would need to apply the filter, copy the visible results, clear the filter, apply a filter for the next color, and so on. For more complex color-based filtering, consider using additional helper columns.
Multiple Column Filters
One of the most powerful aspects of filtering is the ability to apply filters to multiple columns simultaneously. When you filter multiple columns, the filters work together with And logic, showing only rows that meet all the criteria.
How Multiple Filters Work
Imagine you have employee data with Department and Status columns. If you filter Department for Sales and filter Status for Active, you will see only Active employees in the Sales department. A row must meet both criteria to be displayed.
| Name | Department | Status |
|---|---|---|
| Alice | Marketing | Active |
| Bob | Sales | Active |
| Carol | Sales | Inactive |
| David | Sales | Active |
| Eve | IT | Active |
Only Bob and David appear because they are in Sales AND have Active status.
Applying Multiple Filters
- Apply your first filter to any column using the dropdown
- Click OK to activate that filter
- Go to the next column and click its dropdown arrow
- Apply your second filter with its own criteria
- Repeat for additional columns as needed
When you apply a filter to one column, the value lists in other columns update to show only values that appear in the currently visible rows. This helps you understand what combinations of data exist and prevents selecting values that would result in zero matching rows.
Search Box Filtering
At the top of every filter dropdown, you will find a search box. This is one of the fastest ways to filter when you know exactly what you are looking for. Simply type your search term, and the value list updates instantly to show only matching items.
How to Use the Search Box
- Click the dropdown arrow in the column you want to filter
- Click in the Search box at the top of the dropdown
- Type your search term and watch the list filter in real time
- Check the values you want to display from the filtered list
- Click OK to apply the filter
Search Box Behavior
The search box performs a Contains search by default. If you type john, you will see Johnson, Johnston, John Smith, and any other value containing those letters. The search is not case sensitive, so john matches John, JOHN, and john.
As you type, checkboxes next to matching values are automatically selected, and non-matching values are deselected. When you click OK, only the matching values are displayed.
When you search and only one value matches, that value is automatically selected. Just press Enter or click OK to instantly filter to that single value. This makes finding specific records in large datasets extremely fast.
Clearing Filters
After filtering your data, you will often need to remove filters to see all your data again or to apply different criteria. Excel provides several ways to clear filters depending on whether you want to clear one column or all columns.
Clearing Filter from One Column
- Click the filter dropdown for the column you want to clear. Look for the funnel icon indicating an active filter.
- Click Clear Filter From [Column Name] at the top of the dropdown menu
- The filter is removed from that column, but filters on other columns remain active
Clearing All Filters at Once
- Go to the Data tab on the Ribbon
- Click the Clear button in the Sort and Filter group
- All filters are removed from all columns simultaneously
Removing AutoFilter Entirely
To remove the filter dropdowns completely, click the Filter button on the Data tab or press Ctrl+Shift+L. This toggles AutoFilter off, removing all dropdown arrows and clearing any active filters.
| Action | Method | Result |
|---|---|---|
| Clear one column filter | Dropdown menu in that column | That column shows all values, other filters remain |
| Clear all filters | Data tab, Clear button | All columns show all values, dropdowns remain |
| Remove AutoFilter | Data tab, Filter button or Ctrl+Shift+L | Dropdown arrows removed, all data visible |
Working with Filtered Data
When data is filtered, you often need to work with just the visible rows. Excel provides ways to copy, calculate, and modify filtered data while ignoring hidden rows.
Copying Filtered Data
When you select and copy filtered data, Excel copies only the visible cells by default. This means if you filter a list to show only active employees and copy that range, you will paste only the active employees, not the hidden inactive ones.
Calculations on Filtered Data
Standard functions like SUM, AVERAGE, and COUNT include hidden rows in their calculations. If you sum a filtered column, you get the total of all values, not just visible ones. To calculate only visible values, use the SUBTOTAL function instead.
| Function | Syntax | Behavior with Filters |
|---|---|---|
| SUM | =SUM(A1:A100) | Includes hidden rows |
| SUBTOTAL (Sum) | =SUBTOTAL(109,A1:A100) | Ignores hidden rows |
| AVERAGE | =AVERAGE(A1:A100) | Includes hidden rows |
| SUBTOTAL (Average) | =SUBTOTAL(101,A1:A100) | Ignores hidden rows |
Status Bar Calculations
When you select cells in a filtered range, the status bar at the bottom of Excel shows calculations for the visible cells only. You can see Sum, Average, and Count for just the visible selection, giving you quick calculations without writing formulas.
Be careful when making changes to filtered data. If you delete visible rows, you delete those rows permanently. If you enter values in visible cells and drag to fill, you might unintentionally affect hidden rows. Always double-check your changes after clearing filters to ensure you modified only what you intended.
Practice Exercise
Apply everything you have learned by completing this comprehensive hands-on exercise covering all major filtering techniques.
- Create a new workbook and save it as Filtering_Practice
- Set up headers in row 1: Order ID, Customer, Product, Category, Order Date, Quantity, Price, Region
- Enter data for 20 orders with various customers, products in 3-4 categories, dates spanning several months, quantities from 1-100, prices from 10-500, and 3-4 different regions
- Enable AutoFilter using Data tab or Ctrl+Shift+L
- Filter by a single value: Show only orders from one specific region
- Clear the filter and apply a text filter: Show customers whose names contain a specific letter
- Apply a number filter: Show orders with quantity greater than 20
- Apply a date filter: Show orders from This Month or a specific month
- Apply multiple filters: Show orders from a specific category AND a specific region
- Use the search box: Type a product name to find it quickly
- Apply conditional formatting to the Price column and then filter by cell color
- With filters active, select the Quantity column and observe the status bar calculations
- Use SUBTOTAL function to sum visible quantities: =SUBTOTAL(109,F2:F21)
- Clear all filters using the Clear button on the Data tab
- Remove AutoFilter completely by clicking the Filter button again
Congratulations on completing Module 5: Data Management. You have now mastered sorting and filtering, the two fundamental skills for organizing and analyzing data in Excel. These techniques will serve you in virtually every spreadsheet you create. You are ready for Module 6: Charts and Visualization, where you will learn to present your data visually through compelling charts and graphs.
Key Takeaways from Lesson 14
- Filtering temporarily hides rows that do not match your criteria while keeping all data intact
- Enable AutoFilter from the Data tab or by pressing Ctrl+Shift+L to add dropdown arrows to headers
- Basic filtering lets you check and uncheck specific values from the value list in each column
- Text Filters provide options like Contains, Begins With, and Ends With for text pattern matching
- Number Filters provide options like Greater Than, Between, Top 10, and Above Average for numeric criteria
- Date Filters include dynamic options like This Month, Last Quarter, and Year to Date that update automatically
- You can filter by cell color, font color, or conditional formatting icons
- Multiple column filters work with And logic, showing only rows meeting all criteria
- The search box in filter dropdowns provides fast text searching within column values
- Use the Clear button to remove all filters at once, or clear individual columns through their dropdowns
- The SUBTOTAL function calculates only visible cells, unlike SUM and AVERAGE which include hidden rows
- Always verify changes after clearing filters when you have edited filtered data