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.