Excel 2007: Use these three techniques to turbo charge filtering

By David Ringstrom, CPA

Did you know Excel 2007 has a hidden command that enables you to filter a list with just one mouse click? And that there are two ways to sum just the visible rows in your filtered list? Read on to discover these time saving techniques.

Not sure how to filter? Read Excel Filtering 101.



 

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 in Excel 2007.

 

Filter versus Autofilter

As you're probably aware, the Filter command appears on both the Home and Data tabs of the Excel 2007 ribbon. However, going this route to filter a list usually means multiple mouse clicks. Instead, I recommend that you add the AutoFilter command to your Quick Access Toolbar:

 

  1. Right-click anywhere on the Excel 2007 ribbon and, then choose Customize Quick Access Toolbar.
  2. As shown in Figure 1, choose Commands Not In the Ribbon, and then add AutoFilter to your Quick Access toolbar. Close the dialog box shown in Figure 1.

 

 

Figure 1: AutoFilter is one of many secret commands that you can add to your Quick Access Toolbar. (Click on image to expand.)

New to the QAT? Learn more about the Quick Access Toolbar via the Microsoft Professional Accountant's Network.

Although it's similar in name to the Filter command, AutoFilter has a special capability: it instantly filters your list based on the contents of the currently selected cell:

 

  1. Copy and paste the sample data from Table 1 into a blank worksheet.
  2. Click on cell B14 — which contains the word green — and then click the AutoFilter button that you added to your Quick Access toolbar. As shown in Figure 2, your list should now show only green T-shirt sales.

Caveat: AutoFilter only allows you to filter for a single value, such as the word green, so you'll still need to use the filter drop-down lists for more complex filtering.

 

 

Figure 2: Use AutoFilter to create a summary with just two mouse clicks.

Important: Although AutoFilter turns on the arrows, you must still choose Sort & Filter, and then Filter to turn off the filtering arrows.

Analyze Visible Rows

Now let's look at two ways to analyze data as you filter it. First, many users overlook the fact that Excel 2007 automatically displays the total of selected cells in the status bar at the bottom of the screen, as shown in Figure 3. If you don't see this total, right-click on the status bar and enable the Sum option. Excel usually also displays these statistics, too: Average, Count, Numerical Count, Minimum, and Maximum.

 

 

Figure 3: The status bar generally gives you an instant total for the selected cells. (Click on image to expand.)

You can also accomplish this by using the SUBTOTAL function. To do so, enter this formula in cell C23:

=SUBTOTAL(9,C1:C21)

The 9 instructs SUBTOTAL that we want to sum. As shown in Figure 4, Excel automatically displays the additional options as you type the formula. SUBTOTAL is similar to SUM, but has a special capability of only tallying visible rows.

 

 

Figure 4: The SUBTOTAL function has several capabilities.

For comparison, enter this formula in cell C24:

=SUM(C1:C21)

Now, filter the list for Orange T-shirts. As shown in Figure 5, SUBTOTAL should return 25,157, while SUM always returns 99,559, regardless of how the list is filtered. SUM always tallies all rows, regardless of their hidden status. SUBTOTAL has other interesting capabilities, which I'll explore soon in a separate article.

 

 

Figure 5: SUBTOTAL tallies just the visible rows, while SUM tallies all rows.


A previous version of this article first appeared on www.accountingweb.com .

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