Changing Pivot Chart Affects Excel Pivot Table

Changing Pivot Chart Affects Excel Pivot Table

After you create a pivot table, you can insert a pivot chart, based on that pivot table. However, if you change the layout of the pivot chart, it will also change the pivot table’s layout.

For example, if I remove City from the chart in the screen shot below, the City field will also be remove from the pivot table.

pivotchartchange01

Fix the Problem

Unfortunately, there isn’t any setting you can change that will disconnect the two. A pivot chart will always have the same layout as the pivot table on which it is based.

As a workaround, you can make a copy of the pivot table, and set it up the way you need to. Then, hide the original pivot table – the one that is connected to the pivot chart.

When you change the visible pivot table, it won’t affect the chart, and vice versa. When you refresh the visible pivot table, the hidden pivot table will update too, because they are based on the same pivot cache.

In the screen shot below the pivot chart has both the Region and City fields in the Row Labels area, and the pivot table only has City.

pivotchartchange02

For more pivot table tips, you can visit the Pivot Table FAQs page on my website.

Watch the Video

To see the problems with a connected chart and pivot table, please watch this short video tutorial.

______________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.