Printer Friendly

AutoFilter secrets in Excel.

With every new version of Excel, the AutoFilter command becomes more powerful. It's simple to do basic AutoFiltering: Enable the filter dropdowns, and choose an item from the dropdown to filter a data set. This month's article, however, will cover three lesser-known AutoFilter features.

The Basics of AutoFilter

You can enable the AutoFilter dropdowns on the headings of your data set in a number of ways. In Excel 2003, you can go to the Data menu and choose Filter, AutoFilter. In Excel 2007, choose the large Filter icon on the Data tab of the ribbon. The dropdowns will also appear if you use Ctrl+L in Excel 2003 or Ctrl+T in Excel 2007.

Once you've enabled the dropdown arrows on your headings, you can choose a value from the list as a filter to see only the records that match your selection. Filters are additive. If you filter the Region column to only "East" and filter the product column to only "ABC," you'll see only the records that match both conditions.

In Excel 2003, you can filter to show two different values in one column by choosing the Custom column and joining up to two conditions with an "Or." In Excel 2007, you can now use the checkboxes to choose as many options as you want from the dropdown list.

Filter by Selection

Microsoft Access has a feature called "Filter by Selection," which is a beautiful feature. Select a particular field in a column, click the Filter by Selection icon, and Access automatically filters that column to the selected value. I always wondered why Excel doesn't offer similar functionality. Are you ready for an amazing secret? Both Excel 2003 and Excel 2007 offer Filter by Selection icons. I can't blame you for not finding them since Microsoft mislabeled the icon! Instead of calling the icon "Filter by Selection," they erroneously labeled it "AutoFilter."

Here's how to find the icon in Excel 2003: In the Tools menu, select Customize. Click on the Commands tab. In the left list box, choose Data. In the right list box, the fourth icon is labeled "AutoFilter." Drag this icon to any visible toolbar, and close the Customize dialog box.

In Excel 2007, right-click the Quick Access Toolbar, then choose Customize Quick Access Toolbar. In the left dropdown, choose All Commands. Scroll down until you see the AutoFilter icon. Choose the icon, and then click the Add>> button. Click OK.

You can now choose any cell in your data set. Click the AutoFilter icon (see Figure 1), and Excel will turn on the AutoFilter dropdowns and choose the selected cell's value from the dropdown at the top of the column. You've now filtered by selection in Excel!

[FIGURE 1 OMITTED]

The Secret Life of the AutoSum Icon: Ad Hoc Totals of Filtered Records

Regular Excel users are familiar with the AutoSum button, the Greek letter Sigma icon in the toolbar. Select cells beneath your numeric columns, and the AutoSum button will insert =SUM() functions to total the values in that column. Simple enough.

Here's an amazing trick. Turn on the AutoFilter dropdowns. Apply a filter to at least one column. Now select the blank cells in the visible row beneath your data. When you click the AutoSum icon, you won't get SUM formulas. Instead, you get formulas that will add up the visible cells. You can now change the filters, and the total row will always show the total of the selected cells.

To make this trick a little better, insert two blank rows above your headings. Cut the new total formulas from below your data set, and paste them in the top blank row above your data. You will now have ad hoc totals at the top of your data set.

AutoFiltering a Pivot Table

Unfortunately, Microsoft grays out the AutoFilter commands when you are in a pivot table. There are times when you might want to see all customers who have total revenue between $50,000 and $75,000. A quick AutoFilter would solve this question, but Excel disables the feature.

Ready to be amazed? In Figure 2, the headings of the pivot table run from A4 to D4. Move the cell pointer directly to the right (cell E4). This is the first cell outside the pivot table. Hold down the shift key while pressing the left arrow key to highlight the cells back to cell A4. You've now selected the range E4:A4, with E4 as the active cell. Because the active cell is outside the pivot table, Microsoft forgets to disable the AutoFilter! Choose Data, Filter, AutoFilter, and Excel will turn on the AutoFilter dropdowns in your pivot table.

[FIGURE 2 OMITTED]

To see customers with revenue between two values, open the Revenue dropdown, and choose (Custom). In the Custom AutoFilter dialog, choose "is greater than or equal to" on the left, and enter 50,000 on the right. In the second row of options, choose "is less than or equal to" on the left, and enter 75,000 on the right. Excel will filter the results of the pivot table to show only customers with revenues in the appropriate range.

I'm fairly sure that this is taking advantage of a bug in Excel. Since Microsoft didn't expect you to be able to filter a pivot table, Excel doesn't recalculate the filter when you refresh the pivot table. You should only use the AutoFilter trick when you've reached the final pivot table.

Bill Jelen is the author of 23 books about Microsoft Excel and the host of MrExcel.com. Send questions for future articles to IMA@MrExcel.com.
COPYRIGHT 2008 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2008 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:Excel; Microsoft Corp. introduced excel software
Author:Jelen, Bill
Publication:Strategic Finance
Article Type:Column
Date:Dec 1, 2008
Words:937
Previous Article:SOX, ERP, and BPM: a trifecta that can make your business run better.
Next Article:Introduction to Access database software.
Topics:

Terms of use | Privacy policy | Copyright © 2019 Farlex, Inc. | Feedback | For webmasters