Save time with quick auto-filtering in Excel 2003

 By Susan Harkins , TechRepublic on July 8, 2010

Microsoft Excel

---

Save time with quick auto-filtering in Excel 2003

Excel's AutoFilter feature lets you view records that match specific criteria. All your data's still there, but Excel hides all rows that don't match the chosen criteria. You're probably already familiar with the feature and agree with me that it's easy to use.
The only complaint I have with the feature is the list--Excel creates a unique list of values and you choose the filtering criteria from that list. If the column contains lots of unique values, the list is a bit unwieldy--it just isn't as convenient as it can be.
A more convenient filtering solution makes use of what's already available:

• You add a set of filtering buttons to any toolbar—probably the one you use most. If you're creating a custom application, you might create a custom toolbar just for filtering.
• To use the buttons, you select your criteria right in the sheet and click a filtering button.
• To view all your data, click another filtering button.

First, let's add those buttons to a toolbar, as follows:

1. Make sure the toolbar you're going to add the buttons to is visible.
2. Choose Customize from the Tools menu.
3. Click the Commands tab.
4. In the Categories list, select Data to update the Commands list.
5. From the Commands list, drag the AutoFilter command to a toolbar.
   
6. From the Commands list, drag the Show All command to the same toolbar--you'll want them right next to each other.
7. Click Close to close the Customize dialog box.

Now you’re all set. To use the new toolbar buttons, just select a value and click AutoFilter (the button to the right of the Help button in the above toolbar). For example, in the sheet below, I selected A5 and clicked the AutoFilter button. Excel responded by displaying only those records that contain the same value, AT6-02, in column A. To remove the filter, click Show All. (This feature works similar to the Filter By Selection feature in Access.)


Adding these buttons helps avoid perusing a long list of criteria values. When lists are short, having the buttons available really doesn't save you any time or aggravation, unless you're building a custom filtering toolbar for a template. Or, you might just prefer this method--I think filtering by selection is less prone to mistakes than the list selection method.
You can't easily customize 2007's ribbon, but you can add these buttons to the Quick Access Toolbar.