Pasting Pivot Chart Format or using slicers Changes Pivot Table data Reference

**Xcelguy** · 05-24-2013, 02:51 PM

So I have a very wonky problem and have been looking for a fix for this issue and havent located it yet. I registered specifically to ask this question.

I work for a large retailer and have built a large spreadsheet to work as a functional dashboard using data that is prepared in a number of formats using pivot charts and slicers. The problem is that some of these graphs appear unstable. Basically some of them when they update format via slicer or pasting a chart format will break their reference to the pivot table that drives the pivot chart and instead remaps the chart to pivot table 1. I usually create my pivot tables based off of just copying and pasting the pivot table next to it and wondered if the chain of tables may affect it and created a pivot table from the root data table and it had no impact on the problem.

Does anyone know why pasting a chart format or using a slicer would cause a pivot chart to change the pivot table data reference? The problem is I can build each chart manually instead of pasting the format but those charts where pasting the format is an issue also have problems with slicers so when users use the file the charts corrupt and start showing information from another table. IE my pivot chart will reference pivot table 12, but when i slice or paste a format the chart then references pivot table 1. The chart i copied the format from wasn't even referencing pivot table 1.

Any ideas?

Excel 2010

**Xcelguy** · 05-24-2013, 03:38 PM

Update: It is not the format / slicer that is causing this. I just created a chart based off of pivot tables 12 and 14 that accurately represent the data in those pivots. I then looked at the data source and it shows pivot table 1 for the charts that are accurately representing data that is different than pivot table 1. This is why the format change is causing this to reformat to pivot table 1 because it refreshes it back to the datasource. How do i get excel to stop making every pivot chart reference pivot table 1?

**Xcelguy** · 05-24-2013, 03:58 PM

So one more update. The last update was found to be somewhat untrue. I found that when I create a chart based off of the pivot table the pivot chart does accurately reflect the pivot table. The problem is if i even hover over (without even clicking it) the paste option button two things happen. The pivot chart changes it's pivot table data reference to pivot table 1 and the axis fields change... without even pressing a button. I click away and click back and these changes stay true without even actually pressing the paste format button.