Use an Excel AutoFilter to hide some of the data in your worksheet. For example, you can focus on sales to a specific customer, or print a list of your largest orders
1. Set up the Excel Table
In Excel 2007, and later versions, you should create an Excel Table from your data, and use its built in filtering features. If you are using an Excel Table, you can skip to the next section, Filter One Column.
Note: For Excel 2003 AutoFilter instructions, please go to Excel 2003 AutoFilter Basics
If your data is not in an Excel Table, follow these steps to prepare your list for an AutoFilter.
2. Turn on Filtering
In Excel 2007 and later, if you're using an Excel Table, it has built-in filtering features. If your data is not in an Excel Table, follow these steps to add an AutoFilter.
A dropdown arrow appears at the right side of each column heading.
With the Excel Table, or AutoFilter, in place, you can start filtering the individual columns. You can filter on just one column, such as Customer, or two or more columns.
For example, to filter for a specific customer, follow these steps:
In the filtered worksheet, you'll see some changes:
After you have filtered one column, you can refine the filtered results, by filtering two or more columns.
For example, after you filter for a specific customer, follow these steps to add a product filter:
After you have applied one or more filters, you can:
To save time, you can
Clear a Specific Filter
Filter With Search Box
Instead of scrolling through a long list of items, when filtering an Excel table column, use the Search Box feature, to find specific text strings.
Watch this short video to see how to filter a column by using the built-in Search Box. This feature was introduced in Excel 2010. There are written steps below the video.
Use the Search Box
In this example, we'll use the Search Box to look for product names that contain "am".
To use the Search Box when filtering, follow these steps:
If there are any blank cells in a column, the drop down list in the heading cell will show one additional item -- (Blanks) -- at the end of the item list.
To show only the rows with blank cells in that column, remove the check marks from the other items in the list.
If a column contains numbers, you can filter to find the highest
or lowest numbers that column. In this example, you'll filter for
the 5 lowest values in the Total column.
Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items.
In addition to filtering for text or numbers, you can filter by the cell fill colour, even if that colour was applied by conditional formatting.
Filter for Date Range Colour
You can filter a list of dates for a date range, by using the Between date filter, as shown in the next section. With that method, you'll have to type the dates in the filter settings, each time you want to use a different date range.
This video shows how to add Conditional Formatting to the date cells, so the font colour changes slightly, based on the start and end dates entered on the worksheet. Then, you can use the Filter by Color option, to show only the dates in the specified range.
This technique is the second method shown in this video, starting at the 2:00 mark
If a column contains dates, you can filter to find a specific date
or date range, or a dynamic date range. In this example, you'll filter
for the orders received in January 2011.
If a column contains dates, instead of finding a specific date range,
you can filter for a dynamic date range, such as Last Week, Next Month,
or Tomorrow. In this example, you'll filter for the orders received
in January 2011.
When you open the workbook at a later date, the AutoFilters don't refresh automatically. For example, if you used a dynamic date filter, such as Yesterday, the correct day's results won't show in the filtered table when you open the file the next day. Or, if new data was added to the table, it won't be automatically filtered.
To see the updated filtered results, you can reapply the filter.
This will update any dynamic filters in the table, and will also revise the results for any new or changed data.
You could create a macro to update the filter, and set it to run when the file opens.
After you apply a filter, you can delete the filtered rows.
Check the Results
As soon as you delete the rows, clear the filter, and check the results:
Some of the AutoFilter options in the drop down lists end with three dots, such as Begins With...
These filters, as well as the Custom Filter option, open the Custom
AutoFilter window. In there, you can enter one or two criteria, to
create a customized filter.
To filter for one criterion:
In the screen shot below, the Custom Filter will find records where the customer name begins with "M".
To filter for two criteria:
In the screen shot below, the Custom Filter will find records where:
Basic AutoFilter: Click here to download a zipped workbook with the basic examples for Excel AutoFilter techniques. . The zipped file is in xlsx format, and does not contain macros
Date Range: Download the sample file for the AutoFilter for Date Range example. The zipped file is in xlsx format, and does not contain macros
Last updated: October 20, 2023 3:12 PM