AutoFilter

Tips, Tricks, and Answers

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.

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.

More Than Two Conditions for Custom AutoFiltering
Using the AutoFilter feature of Excel, you can define a custom filter that controls what information you see from a data table. Custom AutoFilters allow you to specify one or two criteria to be applied in the filtering process. This tip describes ways you can actually use more than two criteria.

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.

Toggling AutoFilter
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.

Using AutoFiltering
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.