In Excel 2003, you can add a couple of buttons to the toolbar to make it easy to filter a table. For example, in the table below, the East region is selected. With one click of a button, and no programming, you can add an AutoFilter and filter the table to show only the East region orders. Thanks to Roger Govier for sharing this tip.
[Update: For Excel 2007 instructions, see Easy Filtering in Excel 2007]
Add Buttons to the Toolbar
You can add two buttons to the toolbar, to make filtering easy. One button will filter the table for the selected item, and the other button will show all the records.
To add the buttons:
- On the menu bar, click Tools, then click Customize.
- In the Customize dialog box, click the Commands tab.
- Click the Data category, then drag the AutoFilter command to an existing toolbar.
- Drag the Show All command to an existing toolbar.
- Close the Customize dialog box.
Apply the AutoFilter
Now you can use the new buttons to filter the table, or to show all the records. The table doesn't need to have an AutoFilter currently applied.
- In a table in Excel, select a cell that contains the criterion you'd like to use. For example, to filter for the East region records, select an East cell in the Region column.
- On the toolbar, click the AutoFilter button
The table is filtered, and shows only the East region records.
Remove the Filter
To remove the filter, and show all the records again:
- Select any cell on the worksheet
- On the toolbar, click the Show All button.
The filter is removed, but the AutoFilter feature is still turned on, and all the records are visible.
For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.
Watch the Video
To see the steps, you can watch the short video below.
_________________________
That's a nice time-saving feature. Too bad it doesn't work for lists.
This is so cool. As long as I've been autofiltering, I never knew this! I'm presenting Excel tips at the American Bar Association's TechShow in a couple of weeks and I will demo this. Thanks Debra and Roger!
To turn the Autofilter off a simple macro will do the job
Sub filter_off()
ActiveSheet.AutoFilterMode = False
End Sub
You can assign this macro to a button and put it in a toolbar.
I posted a simple technique to do it in my blog (in Spanish)
Why would this not work for me? Turns out that I had copied the identical looking AutoFilter button from the Data>Filter>AutoFilter menu selection. The filter on/off works, but filtering on the selection does not.
A little further testing shows that the Tools Menu item is button ID 899, while the item you demo from the customization list is ID 458.
Is there a way to account for the difference?
[...] couple of weeks ago I described how you could select a cell in a table, and automatically filter the list based on that cell's value. The same feature is available in Excel 2007, using a different [...]