Chart Templates Stop Retrieving Data


New member
Feb 19, 2014
Reaction score
Hi all,

I am encountering a repeated issue with chart templates. I take the following steps when creating the template:

  • Insert Pivot Chart>Format as desired>Save as template

I then copy paste the pivot tables, filter them differently and create charts from template.

The issue i encounter is that, after having created the 8th chart, the template starts using the first pivot table (the one i created the template with) as data source. I took the following steps a none of them have worked:

  • Saving the template from a non-pivot chart
  • Creating a new template after the 8th chart is created and deleting the old one, in that case the graph uses the info of the 9th chart for all the following charts

It looks like there's a finite amount of pivot tables from which a template can retrieve information within a workbook.

I attached a document with a maxed out template so you can see what i'm talking about (issue starts happening with the pivot table on sheet "PT" cloumn AW)View attachment Rank Automatization Tryout.xlsx

Any ideas? This has been really frustrating since i have to create a lot more graphs and the only solution is to copy paste the data in the pivot table into normal cells and then create the chart form there.

Thanks a lot,



Super Moderator
Staff member
Dec 16, 2012
Reaction score
Excel Version(s)
I think this is an excel bug. If you right-click a chart and choose Select Data… you can see the pivot table name the chart is attached to in the Chart Data range field at the top of the dialogue box.
If, while the active cell is in a pivot table, I create a new chart using a saved chart template (that I created from one of your other charts), all the charts created from pivot tables named PivotTable10 to PivotTable19 data sources were PivotTable1. If you change the name of the pivot table to something entirely different first, the data source remains correct while creating the chart.
If I create a built-in line chart from a pivot table named PivotTable10 to PivotTable19, the source data remains the correct pivot table, but when I change the chart type of that of the saved template chart type, the source data becomes PivotTable1!
So change the pivot table names before you create the chart. You can change a pivot table's name by selecting some part of the pivot table, then in the ribbon, in the Options section of the PivotTable Tools, in the leftmost section called PivotTable, you will see the Pivot Table name, just overwrite this.