Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
If you need to filter data in your worksheet, Excel's AutoFilter tool is the easiest way to go about it. Learn how to use the AutoFilter tool to organize large amounts of data and display only what you need with the following ExcelTips.
The following articles are available for the 'AutoFilter' topic. Click the article's title (shown in bold) to see the associated article.
Changing AutoFilter Drop-Down Arrow Colors
The drop-down arrow used at the top of columns by AutoFilter can be difficult to see. Here's a way you can reduce the confusion about which columns are filtered in your worksheet.
Filtering to a Date Range in the Past
If you have a large number of data records, each with an associated date, you might want to filter that data so you see only the records from within the past month or so. You can do this easily by using the technique described in this tip.
Finding Rows with Values in Two Columns
When you use Excel to input and store information, you need to be concerned with whether the information meets your expectations. For instance, you may want to make sure that each row has a value in only one of two columns, and not in both columns. This tip examines ways you can find where the data doesn't meet this expectation.
Increasing the AutoFilter Drop-Down Limit
When you turn on AutoFiltering, Excel displays a drop-down list at the top of each column in your data table. This list contains the unique values within each column. This tip describes the 1000-entry limit imposed on the list by Excel and how you can work around it.
Limits to Filtering
Excel limits the number of items that can be shown in an AutoFilter drop-down list. Granted, it is a high limit, but it is a limit nonetheless. This tip explains the limit and provides some ideas about how you can better use filtering to find the data you need.
Macro Fails after AutoFilter
When developing a macro that others may use, you might want to test it out to make sure it works properly if an AutoFilter is in effect. If it doesn't, you can turn off the AutoFilter with a simple single-line command.
Printing Rows Conditionally
Need to only print out certain rows from your data? It's easy to do if you apply the filtering or sorting techniques presented in this tip.
Quickly Identifying Applied AutoFilters
Apply an AutoFilter to a worksheet, and you can quickly forget exactly what that AutoFilter entailed. Here are a couple of ways that you can figure out what an AutoFilter is doing to your data.
Setting Up Custom AutoFiltering
The filtering capabilities of Excel are very helpful when you are working with large sets of data. You can create a custom AutoFilter that will display only the information you want to see.
Suppressing Zero Values in PivotTables
PivotTables are great for digesting and analyzing huge amounts of data. But what if you want part of that data excluded, such as information that has a zero value? Here's a couple of quick ideas.
Want a tool that will help you toggle AutoFilter on and off? Excel provides some tools you can use, but you need to be careful that you choose the right ones.
Turning Off AutoFiltering
After you apply a filter to your data, you may wonder how to get rid of it so that you can once again see all your data. Use one of the simple methods described in this tip.
Excel's AutoFilter tool is a great way to make a long list of items much more manageable. This tip explains how to set up an AutoFilter and display just the information you need.