There are many occasions when it would be valuable to have two (or or three or possibly more) Excel pivot tables respond to changes in a single slicer filter. This is easy to do when one understands the inner workings of pivot tables.
Under normal circumstances if you create several Excel pivot tables each will get its own separate pivot table cache. The pivot table cache is a data structure created in memory from a query, and it is this pivot table cache that is altered when a slicer filter is applied. The key to having two pivot tables controlled by the same slicer is to get two pivot tables to share the same pivot table cache. This can be achieved by simply copying and pasting a pivot table.
Let’s step through an example. We will imagine that we would like to examine sales by year for both product categories and countries. First we must identify a the pivot table dimension that will be shared by both pivot tables. Often, this will be a dimension not affected by the slicer. For our demonstration, we will query a Contoso Retail database running on the Analysis Services in tabular mode. Sample data in this database covers the years 2007, 2008, and 2009. We would like to view the sales data for individual countries, but we might also like to group the countries by continent or by some other attribute.
In my opinion, it is best to choose a pivot table that will not change its number of rows for the top position on our worksheet. In this example, this will be a pivot table that shows sales of product categories by year.
Once we have created this pivot table, we highlight it and hit <ctrl>C. We then paste a copy underneath the first. Once we have pasted this copy, we can remove the product category from the first column of the pivot table and replace it with RegionCountryName from the Geography dimension. Although most Excel users accept the default names assigned by Excel for their pivot tables, I think it’s a good idea to give them more descriptive names. In this case, I have dubbed the pivot tables CategoryYear and CountryYear.
After pasting a copy of the first pivot table into the worksheet, we can go the pivot table field list and replace the rows dimension with RegionCountryName.
With the second pivot table (CountryYear) selected, we’ll insert a slicer.
If we select just “North America” from the slicer, we see the second pivot change, but the totals in the first pivot table still represent the totals for all countries, not just those in North America. Furthermore, we notice an unpleasant formatting problem. By default, a pivot table will adjust column widths automatically to match the data in that column. Since the numbers in the second pivot table we a bit smaller. Excel narrowed the column just a bit, but that was enough to prevent data in the first pivot table from displaying correctly. All we see for the Grand Total is “#######”.
This is easily corrected in the Pivot Table Options dialog; simply uncheck the box next to “Autofit column widths on update”.
Only one thing remains: set the slicer to control both pivot tables. Right-clicking on the slicer reveals the “Report Connections…” context menu choice. Clicking on this menu item brings up the Report Connections dialog.
There should be a checkbox for each pivot table. Checking both CategoryYear and CountryYear will accomplish out goal. Upon closing, the numbers in the upper pivot table will readjust. We are now looking at just the North American sales by category. As a quick check, we see that the grand totals of both pivot tables are the same for each year, as they should be.
Pivot tables, like people, are often better in pairs.