One of the new features in Excel 2010 is a type of visual pivot table filter called a Slicer. In typical fashion, the most valuable setting for slicers is buried where most users will never find it.
A slicer could be a list of regions, product lines, months, quarters, years, and so on. The designer of the pivot table can arrange these lists above or to the left of the pivot table. When the consumer open the workbook in Excel 2010 or on the SkyDrive, they can select values from the slicer and automatically have the pivot table update. This is a great ad-hoc reporting system.
Slicers can take up a lot of space in the Excel window. Say that you had three slicers, one for product line, one for region, and one for State. If the State slicer has 50 entries, you will find yourself arranging them in perhaps 2 columns of 25 rows along the side or in 5 rows of 10 columns along the top. Since the default size of a slicer entry is taller than a typical cell in Excel, the slicer array will take up more than 5 rows above the pivot table.
One slicer will respond to changes in another slicer. If you select Southeast from the Region slicer, the State slicer will instantly rearrange, with the southeastern states at the top of the list and the other states essentially grayed out at the bottom of the list.
What Most People Are Going to Overlook
You've had ways to filter pivot tables before. Sure, slicers look better than the old Page Field in Excel 2003 or the Report Filter Field in Excel 2007, but they are providing the same functionality introduced in the Report Filter Field in Excel 2007. The real benefit of slicers is far more subtle.
Say that you build four small pivot tables and arrange them at the top of a worksheet. These pivot tables all come from the same data set, but show different measures. Maybe one shows a revenue trend by year. Another shows the top five customers and their year-over-year growth. Another shows profit by line. In Excel 2007 and earlier, if you wanted to filter the dashboard, you would have to make identical selections from each of the four pivot tables, which is really asking too much from the typical dashboard user. In Excel 2010, there is a subtle way to hook all four pivot tables up to the same set of slicers. Thus, the VP of Sales selects from the slicers and all four pivot tables update at once.
Details - How to Hook Up the Other Pivot Tables to Your Slicers
1. Build all of the pivot tables that you want to display on your dashboard. Don't start the first pivot table in cell A1 - instead, leave some rows at the top and/or columns at the left.
2. Select a cell in the first pivot table. From the Pivot Table Options tab in the Ribbon, select Insert Slicer. Choose which fields will be used for the slicers. Use the icons in the Slicer Tools Options ribbon tab to change the color, number of columns, and so on.
3. Once the slicers are working and formatted for the first pivot table, select one cell in the second pivot table. Go back to the PivotTable Options tab in the ribbon and look closely at the Insert Slicer icon. When you hover the mouse over this icon, a dropdown arrow appears at the bottom. Open the dropdown and choose Slicer Connections.... Place a checkmark next to each slicer in the list. You've now hooked the second pivot table up to each existing slicer.
4. Repeat step 3 for the remaining pivot tables.
You now have a highly visual dashboard without spending a fortune on dashboarding tools.
Bill Jelen is the host of MrExcel.com and the author of 31 books about Microsoft Excel. Before founding MrExcel.com in 1998, he spent 12 years in the trenches in the accounting, finance, and operations departments, honing his power Excel skills. The MrExcel.com website answers 30,000 Excel questions a year, for free, for readers around the world. Jelen hails from Akron, Ohio.
Jelen's book Microsoft Excel 2010 In Depth has been selected as the August, 2010, book giveaway on Microsoft Subnet.
Jelen's book Using Microsoft Excel 2010 has been selected as the August, 2010, book giveaway on Microsoft Subnet.
Read a free chapter excerpt hosted by Microsoft Subnet.
Enter this month's book giveaways from Microsoft Subnet, Cisco Subnet and Open Source Subnet.