Skip Links

Dashboarding with Excel 2010 Slicers and Multiple Pivot Tables

Hooking up slicers to one pivot table in Excel 2010 provides a programming-free way to create dashboards.

By Bill Jelen on Sun, 08/15/10 - 8:48am.

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.

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

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

Blog Roll
MrExcel.com
http://www.mrexcel.com/articles.shtml