Toggling AutoFilter

One of the handy features of Excel is AutoFilter. It allows you to quickly filter any list by the contents of a particular column. In versions of Excel prior to Excel 2007 you can use the Customize option from the Tools menu to add an AutoFilter tool to your toolbars. This tool uses an image of a funnel and an equal sign. (In Excel 2007 you can add the same tool to the Quick Access toolbar.)

The tool is a bit deceptive, however; it is not the same as the AutoFilter option available from the Data menu or the Filter option on the Data tab of the ribbon. The menu/ribbon option is a toggle condition. If you have a cell selected in a list, and you choose the menu option, then the AutoFilter controls appear at the top of each column in the list—there are no other changes to the list. If you use the AutoFilter tool, not only do the controls appear, but Excel filters the list based on the cell you had selected when you used the tool.

Another difference between the two is that the AutoFilter menu/ribbon option functions like a toggle—choose it once, and the AutoFilter is applied; choose it again and it is removed. The AutoFilter tool doesn't do that; it only applies the AutoFilter.

What if you want a toolbar option that is a real toggle, just like the menu option? There are two approaches you can use to solve this problem. The first involves the use of a simple macro:

Sub ToggleAutoFilter()
    On Error GoTo errMessage
    Selection.AutoFilter
    Exit Sub

errMessage:
    MsgBox "Select a cell in the range to be filtered.", vbOKOnly
End Sub

All you need to do is assign the macro to a toolbar button, the Quick Access toolbar, or to a shortcut key, and you can turn AutoFilter on and off, just as if you selected the option from the menus.

The second option may be even simpler. Just follow these steps if you are using a version of Excel prior to Excel 2007:

1. Choose Customize from the Tools menu. Word displays the Customize dialog box.
2. In the actual Excel menus (not the Customize dialog box), select the Filter option from the Data menu.
3. As you hold down the Ctrl key, drag the AutoFilter option from the menus and drop it someplace in a toolbar. (If you don't hold down the Ctrl key, the menu option is moved. You don't want to do this, so hold down the Ctrl key.)
4. Click on the Close button on the Customize dialog box.

The result is that the menu option is now accessible from the toolbar. You can click on it to turn AutoFilter on and off, at will. If you are using Excel 2007, follow these steps instead:

1. Click the Office button and then click Excel Options. Excel displays the Excel Options dialog box.
2. At the left of the dialog box, click Customize.
3. Using the Choose Commands From drop-down list, choose Data Tab.
4. In the list of commands, select Filter.
5. Click the Add button. The icon for the command appears at the right of the dialog box.
6. Click OK.

It is interesting that the icon for the Filter command (step 4) looks exactly the same as the icon for the AutoFilter command. Regardless, they are not the same, as already discussed. These steps add the tool to the Quick Access toolbar, and it works exactly the same as the Filter tool on the Data tab of the ribbon.