Technology

Spreadsheets: Rein In the Dueling Pivot Table Problem

Stumped by how to link two pivot tables so updating data on both is a snap? Here–s how to use Excel–s –slicer– function to get the job done without...
Bill JelenOctober 28, 2010


Editor’s Note:

If you would likek Bill Jelen to answer your Excel question in an upcoming column, send an e-mail note to

billjelencfo.com

. If he chooses your question as a topic for his column, you’ll win a book from CFO and MrExcel.com

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.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

Figure 1 (below) shows two pivot tables created from the same data. Both pivot tables have Product and Customer in the page area. Both total $6.7 million dollars.

Fig. 1

MrExcel 9-28-2011 Fig1

After choosing a product in the left pivot table, the total for that filtered pivot table is now $2.2 million. However, the second pivot table continues to show the unfiltered result of $6.7 million. The goal is to get both pivot tables to filter together, without having to re-select the same choices in each pivot table.

Fig. 2

MrExcel 9-28-2011 Fig2

Select one cell in the first pivot table. From the PivotTable Options tab, choose Insert Slicer. Select slicers for all of the fields that you have in the page area. In this example, it is Product and Customer.

Fig. 3

MrExcel 9-28-2011 Fig3

Use the formatting on the Slicer Tools ribbon to format and arrange the slicers. In Figure 3 (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.

Fig. 4

MrExcel 9-28-2011 Fig4

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.

Fig. 5

MrExcel 9-28-2011 Fig5

In the Slicer Connections dialog box, you are specifying that certain existing slicers should also control the second pivot table. Choose all of the slicers and click OK.

Fig. 6

MrExcel 9-28-2011 Fig6

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

MrExcel 9-28-2011 Fig7

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. E-mail your question using the link I the byline of this article, or directly to

billjelencfo.com

.

CFO will be hosting Bill’s complete 3-hour Power Excel webcast on October 18, 2011. Click here for more information.