Excel 2003: Use These Three Techniques to Turbocharge Filtering

By David Ringstrom, CPA

Did you know that you can streamline the AutoFilter feature in Excel 2003 and earlier versions? And that there are two ways to sum only 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 2003.

AutoFilter's Hidden Powers

Most users choose Data, Filter, and then AutoFilter to enable filtering in Excel. However, you can give yourself one-click access to the AutoFilter command:

 

  1. Choose Tools, Customize, and then click on the Commands tab.
     
  2. Choose Data from the Categories list, and then drag AutoFilter onto one of your toolbars, or to the right of the Help command.
     
  3. Drag the Show All command and place it to the right of the AutoFilter icon that you just dragged.
     
  4. Click Close to dismiss the Customize dialog box.


Figure 1: AutoFilter takes on special powers when you add it to a toolbar or your Excel menu.

You can now instantly filter a list based on the contents of the currently selected cell:

 

  1. Copy and paste the sample data from Table 1 into a blank worksheet, starting in Cell A1.
     
  2. Click on cell B14 — which contains the word green — and then click the AutoFilter button that you added above. As shown in Figure 2, your list should now show only green T-shirt sales.
     
  3. You can click the Show All command to keep the filtering arrows in place, but display all rows.

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. However, you can filter on one column at a time, such as green in column B, and then Medium in column A.


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

Important: Although the toolbar-based AutoFilter button turns on the arrows, you must still choose Data, Filter, and then AutoFilter to turn off the 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 2003 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. Alternatively you can choose to display another statistic instead: Average, Count, Numerical Count, Minimum, and Maximum.


Figure 3: The status bar generally gives you an instant total for the selected cells.

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. Consult Excel's online help file for the other numeric codes, although I will mention that 1 will give you an average, while 4 gives you the maximum, or largest value. SUBTOTAL is similar to SUM, but has a special capability of only tallying visible rows.

For comparison, enter this formula in cell C24:

=SUM(C1:C21)


 

Figure 4: The filtered list.

Now, filter the list for Orange T-shirts. As shown in Figure 4, 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.

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...

Financial institutions often turn to technology to meet increases in regulatory and reporting demands, dedicating large and ever-increasing portions of their budgets each year to investments in tools and solutions that...
Foreign filers and XBRL reporting requirementsIn a recent blog for research firm Audit Analytics, Liam Sullivan noted that as of June 15, 2011, all US Securities and Exchange Commission (SEC) registrants that use US...
One thing that hasn't changed over the years is that accountants have to work with lots of documents. Sure, the method has gone from parchment to paper to computer files to the cloud, but the need to organize, access,...

Already a member? log in here.

Editor's Choice

Upcoming CPE Webinars

Nov 24This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.
Dec 3The materials discuss the concepts and principles in the AICPA’s new special purpose framework.
Dec 9A key component to improving your firm’s workflow efficiency while enhancing your profitability at the same time is how you leverage emerging technologies.
Dec 9Kristen Rampe will cover how to diffuse the tension in challenging situations in this one-hour webinar.