TAGS: Excel, pivot tables, training, VBA, finance, report filters, macros
Dipesh M. will receive an e-book copy of Learn Excel 2007-2010 from MrExcel for this question: “I have set up two pivot tables. Each pivot table has some Report Filter fields. When I change the Report Filter field in the first pivot table, I want the page fields in the second pivot table to change as well. I’ve heard this can be done with VBA, but I don’t know VBA.”
It is correct that you need VBA to solve this problem. However, improvements in Excel 2010 make this a snap.
Figure 1 (below) shows two pivot tables created from the same data. Both pivot tables have Product and Customer in the page area, and the tables have the same total.
Fig. 1
After choosing a product in the left pivot table, the total for that filtered pivot table is now approximately $2.2 million (see Figure 2). However, the second pivot table continues to show the unfiltered result of approximately $6.7 million. The goal is to get both pivot tables to filter together, without having to reselect the same choices in each pivot table.
Fig. 2
Select one cell in the first pivot table. From the PivotTable Options tab, choose Insert Slicer (Figure 3). Select slicers for all of the fields that you have in the page area. In this example, it is Product and Customer.
Fig. 3
Use the formatting on the Slicer Tools ribbon to format and arrange the slicers. In Figure 2 (above), notice that the product slicer is already showing that product DEF is selected. The slicer will mirror whatever is chosen in the report filter dropdowns of pivot table 1 (Figure 4).
Fig. 4
Here is where the new magic begins. Select one cell in the second pivot table. Go back to the Insert Slicer icon, but do not choose Insert Slicer. Instead, open the dropdown and choose Slicer Connections (Figure 5).
Fig. 5
In the Slicer Connections dialog box (Figure 6), you are specifying that certain existing slicers should also control the second pivot table. Choose all of the slicers and click OK.
Fig. 6
You now have both pivot tables linked. Someone could change the product in B2, or the product in E1, or the slicer in F1:I5 and all three will automatically update. In Figure 7 (below), I had changed B2 to be ABC. Excel automatically updated the slicer and automatically updated E1.
Fig. 7
It is interesting to note that you can move the slicers out of view; they don’t need to be visible. Alternatively, you could use only the slicers and remove the Report Filter fields. The trick continues to work without the Report Filter fields. Select a product from the slicer and both pivot tables will update.
This functionality is a great improvement that was introduced in Excel 2010. Kudos to the Excel pivot table team at Microsoft for listening to customers like Dipesh who experienced similar problems in previous versions of Excel.
CFO contributor Bill Jelen is the founder of MrExcel.com and the author of 33 books about Microsoft Excel. You can win one of those books if your question is used in a future column. For a chance to win, send your question via e-mail using Bill’s byline link at the top of this article, or by sending it directly to [email protected].
CFO will be hosting Bill’s complete three-hour Power Excel webcast on October 18, 2011. Check the CFO webcast page for details as they become available.