By David Ringstrom, CPA
Filtering is a powerful, but often overlooked feature in Excel that enables you to hide unneeded data within a list. In Excel 2003 and earlier, this feature is known as AutoFilter, while Excel 2007 refers to it as simply Filter. In this article I’ll provide a quick overview of how to use this helpful feature.
Filtering Basics
Filtering enables you to collapse a spreadsheet down to display only relevant rows.
1. Copy the data from Table 1 into a blank worksheet, starting at cell A1.
| Size | Color | Quantity |
| Small | Blue | 8,038 |
| Medium | Blue | 4,245 |
| XL | Blue | 5,241 |
| XXL | Blue | 8,797 |
| XXXL | Blue | 5,145 |
| Small | Orange | 2,571 |
| Medium | Orange | 5,595 |
| XL | Orange | 8,825 |
| XXL | Orange | 1,997 |
| XXXL | Orange | 6,169 |
| Small | Green | 6,192 |
| Medium | Green | 3,380 |
| XL | Green | 4,024 |
| XXL | Green | 2,020 |
| XXXL | Green | 8,295 |
| Small | Black | 2,841 |
| Medium | Black | 4,705 |
| XL | Black | 4,202 |
| XXL | Black | 4,846 |
| XXXL | Black | 2,431 |
2. Select cell A1, and then turn on the filtering feature:
Excel 2007: click Sort & Filter in the Editing section of the Home ribbon, and then choose Filter. You can also click the Filter button in the Sort & Filter section of the Data ribbon.
Excel 2003 and earlier versions: Choose Data, Filter, and then AutoFilter.
3. Drop-down arrows will appear in cells A1 through C1, as shown in Figure 1.
Expert technique: The drop-down lists display one of each item within that column. This is a helpful auditing tool that eliminates the need to scroll down through a spreadsheet in search of a specific item.
Figure 1: Filtering places an arrow in the first cell of each column within a list.
4. You can now use any of these techniques to filter the spreadsheet:
- View Small T-Shirt Sales
Excel 2007: Click the drop-down list in cell A1, choose Select All to clear the existing arrows, and then choose Small. Your list should look similar to Figure 2.
Excel 2003: Click the drop-down list in cell A1, and then choose Small. Your list should look similar to Figure 2.
Figure 2: The sample data from Table 1, filtered to show only small t-shirt sales.
- View Orange T-Shirt Sales
Excel 2007: Click the drop-down list in cell A1, and then choose Clear Filter From Size. Next, click the drop-down list in cell B1, choose Select All, and then Orange. Your list should now look similar to Figure 3.
Excel 2003: Click the drop-down list in cell in cell A1, and then choose (All). Next, click the drop-down list in cell B1, and then choose Orange. Your list should now look similar to Figure 3.
Figure 3: The sample data from Table 1, filtered to show on orange t-shirt sales.
- Create a list of top sellers
Excel 2007: Click Sort & Filter, and then Clear to eliminate any existing filters. Click the arrow in cell C1, choose Number Filters, and then Greater Than or Equal To. As shown in Figure 4, enter 5000, and then click OK. Your spreadsheet should now look like Figure 5.
Excel 2003: Choose Data, Filter, and then Show All to eliminate any existing filters. Click the arrow in cell C1, and then choose (Custom…). As shown in Figure 4, choose Greater Than or Equal To, enter 5000, and then click OK. Your spreadsheet should now look like Figure 5.
Figure 4: Custom filters allow you to specify your own criteria.
Figure 5: The sample data from Table 1, filtered to show only t-shirts that sold at least 5,000 units.
Custom filtering: If you dig deeper into the custom filter choice you’ll find that you can craft some sophisticated filters, such as Begins With, Ends With, Contains, Does Not Contain, and so on.
New Filtering Capabilities in Excel 2007
Filtering underwent a significant revamp in Excel 2007. Excel 2003 (and earlier versions) only enable you to choose one item from a drop down list, unless you use custom filtering. Conversely, Excel 2007 allows you to choose as many items as you wish from the drop-down list. Excel 2007 allows you to filter by cell color or font color, as shown in Figure 5. Such filtering is not possible in earlier versions of Excel.
Figure 6: Excel 2007 allows you to filter a list based on cell color or font color.
Now that you’re up to speed on filtering, you may want to read a related article that shows how to make filtering even easier.
A previous version of this article first appeared on www.accountingweb.com .
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
