Excel Filtering 101

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

Table 1: Use this sample t-shirt sales data to explore filtering.

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.

Read more articles by David Ringstrom. 
 
About the author:

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.

You may like these other stories...

Regulatory compliance, risk management and cost-cutting are the big heartburn issues for finance execs in the C-suite. Yet financial planning and analysis—a key antacid—is insufficient.That's just one of the...
Continuing its efforts to simplify accounting procedures, the FASB has issued a proposed Accounting Standards Update on customer fees paid in a cloud computing arrangement. The newly-proposed update (Intangibles—...
How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...

Already a member? log in here.

Upcoming CPE Webinars

Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 10
Transfer your knowledge and experience to prepare your team for the challenges and opportunities of an accounting career.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.
Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.