Introduction
If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.
Watch the video below to learn more about filtering data in Excel.
What Is Filtering in Excel?
Filtering in Excel allows you to isolate and view specific data rows while hiding the rest based on parameters you define. Unlike deleting data, filtering is non-destructive—your hidden rows remain safely intact in the background and will reappear as soon as the filter is removed.
Filter vs Sort
While both tools help organize data in Excel, they serve fundamentally different purposes:
- Sorting: Rearranges the order of your dataset based on alphabetical, numerical, or date order (e.g., A–Z or lowest to highest value) without hiding any rows.
- Filtering: Hides rows that do not meet specific criteria, temporarily isolating only the exact information you wish to see.
How to Filter Data
In our example, we'll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout.
- In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row 1: ID#, Type, Equipment Detail, and so on.
- Select the Data tab, then click the Filter command.
- A drop-down arrow will appear in the header cell for each column.
- Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment.
- The Filter menu will appear.
- Uncheck the box next to Select All to quickly deselect all data.
- Check the boxes next to the data you want to filter, then click OK. In this example, we will check Laptop and Projector to view only these types of equipment.
- The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and projectors are visible.
How to Apply Multiple Filters
Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it down further to only show laptops and projectors that were checked out in August.
- Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view information by date.
- The Filter menu will appear. Check or uncheck the boxes depending on the data you want to filter, then click OK. In our example, we'll uncheck everything except for August.
- The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August.
How to Clear a Filter
After applying a filter, you may want to remove—or clear—it from your worksheet so you'll be able to filter content in different ways.
- Click the drop-down arrow for the filter you want to clear. In our example, we'll clear the filter in column D.
- Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From "Checked Out".
- The filter will be cleared from the column. The previously hidden data will be displayed.
Advanced Filtering
If you need a filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes several advanced filtering tools, including search, text, date, and number filtering, which can narrow your results to help find exactly what you need.
Search Filters
Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we'll use this feature to show only Saris brand products in our equipment log.
- Select the Data tab, then click the Filter command.
- Click the drop-down arrow for the column you want to filter. In our example, we'll filter column C.
- The Filter menu will appear. Enter a search term into the search box. Search results will appear automatically below the Text Filters field as you type. In our example, we'll type saris to find all Saris brand equipment. When you're done, click OK.
- The worksheet will be filtered according to your search term. In our example, the worksheet is now filtered to show only Saris brand equipment.
Text Filters
Advanced text filters can be used to display more specific information, like cells that contain a certain number of characters or data that excludes a specific word or number. In our example, we'd like to exclude any item containing the word laptop.
- Click the drop-down arrow for the column you want to filter. In our example, we'll filter column C.
- The Filter menu will appear. Hover the mouse over Text Filters, then select the desired text filter from the drop-down menu.
- In our example, we'll choose Does Not Contain... to view data that does not contain specific text.
- The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, then click OK. In our example, we'll type laptop to exclude any items containing this word.
- The data will be filtered by the selected text filter. In our example, our worksheet now displays items that do not contain the word laptop.
Number Filters
Advanced number filters allow you to manipulate numbered data in different ways. In this example, we'll display only certain types of equipment based on the range of ID numbers.
- Click the drop-down arrow for the column you want to filter. In our example, we'll filter column A to view only a certain range of ID numbers.
- The Filter menu will appear. Hover the mouse over Number Filters, then select the desired number filter from the drop-down menu. In our example, we'll choose Between to view ID numbers between a specific number range.
- The Custom AutoFilter dialog box will appear. Enter the desired number(s) to the right of each filter, then click OK. In our example, we want to filter for ID numbers greater than or equal to 3000 but less than or equal to 6000.
- The data will be filtered by the selected number filter. In our example, only items with an ID number between 3000 and 6000 are visible.
Date Filters
Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates.
- Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
- Click the drop-down arrow for the column you want to filter. In our example, we'll filter column D to view only a certain range of dates.
- The Filter menu will appear. Hover the mouse over Date Filters, then select the desired date filter from the drop-down menu. In our example, we'll select Between... to view equipment that has been checked out between July 15 and August 15.
- The Custom AutoFilter dialog box will appear. Enter the desired date(s) to the right of each filter, then click OK. In our example, we want to filter for dates after or equal to July 15, 2015, and before or equal to August 15, 2015, which will display a range between these dates.
- The worksheet will be filtered by the selected date filter. In our example, we can now see which items have been checked out between July 15 and August 15.
Common Filter Problems
Sometimes filtering doesn't work as expected. Here are a few common issues and how to fix them:
- Empty Rows or Columns: Blank rows in your dataset can break Excel's auto-selection range. Ensure your data range is continuous or highlight the entire range before clicking Filter.
- Filtered Out Data Not Appearing: Always check the bottom status bar to see if a filter is active or look for the small funnel icons on column headers.
- Merged Cells: Merged cells cause unpredictable filtering results. Avoid using merged cells in data ranges.
Excel Filtering Tips
- Keyboard Shortcut: Press
Ctrl + Shift + L(orCmd + Shift + Fon Mac) to quickly toggle filters on or off. - Filter by Selected Cell Value: Right-click any cell, hover over Filter, and select Filter by Selected Cell's Value for instant single-click filtering.
- Use Excel Tables: Converting your data to an Excel Table (
Ctrl + T) automatically enables dropdown filter controls that adjust dynamically as you add data.
Practice Example
Try applying a simple text or number filter on your dataset to isolate specific records. For instance, filter an invoice list to display only unpaid orders exceeding $500, or filter an employee directory by department.
Challenge!
- Open our practice workbook.
- Click the Challenge tab in the bottom-left of the workbook.
- Apply a filter to show only Electronics and Instruments.
- Use the Search feature to filter item descriptions that contain the word Sansei. After you do this, you should have six entries showing.
- Clear the Item Description filter.
- Using a number filter, show loan amounts greater than or equal to $100.
- Filter to show only items that have deadlines in 2016.
- When you're finished, your workbook should look like this:
FAQs
How do I filter by color in Excel?
Click the filter drop-down arrow on the column header, hover over Filter by Color, and select the cell color or font color you want to display.
Why is the filter button grayed out?
The filter button may be disabled if you have grouped sheets selected. Right-click a sheet tab and select Ungroup Sheets. It can also happen if the worksheet is protected.
Can I filter multiple columns at the same time?
Yes. Filters are cumulative. You can filter Column A, then filter the remaining results in Column B to narrow down your data even further.
How do I remove all filters at once?
Go to the Data tab on the Ribbon and click the Clear button in the Sort & Filter group. This removes all active filters but keeps the filter arrows.
What is the difference between "Filter" and "Advanced Filter"?
Basic filtering uses dropdowns to hide rows. Advanced Filter allows for complex criteria (like OR conditions between columns) and can copy the filtered results to a new location.
Conclusion
Filtering is one of the most essential skills in Excel for processing large volumes of information efficiently. By applying basic filters, combining criteria across multiple columns, and leveraging advanced options like date and search filters, you can save significant time analyzing datasets.
