A very common task you may have is to take a chart you’ve painstakingly formatted and use it with new data. I described a few ways to handle this in Make a Copied Chart Link to New Data.
Most commonly you have a worksheet with a bunch of data and a corresponding chart, and you have another sheet of data you want to add a chart to. Copying and pasting the chart onto the new sheet requires you to change links in the chart, usually series by series. This is tedious and error-prone. But the article above describes an easier way:
- Make a copy of the worksheet with the old data and chart;
- Copy the new data;
- Paste the new data over the old data on the copied worksheet.
The chart on the new worksheet updates as soon as the new data is pasted into place. Works in Excel 2003 and earlier, and in Excel 2007 if you’ve installed the latest service packs.
What if the worksheet contains a pivot table and its sister pivot chart? Well, knowing the above protocol, you’d think you could copy the worksheet, change the data, and the chart would be fine. And in Excel 2003 this set of steps works great:
- Make a copy of the worksheet with the old pivot table and pivot chart;
- Change the pivot table’s data source to the new range;
- Refresh the pivot table.
The new pivot chart (on the copied sheet) retains its link to the pivot table on its parent worksheet, so it updates as soon as the pivot table is refreshed.
But in Excel 2007, these steps don’t work the same way. When you copy the worksheet with the pivot table and chart, not only does the new pivot table link to the same old data, the new chart also links to the old pivot table. You can easily enough change the data source of the new pivot table to the appropriate range; in fact, this is easier to do in Excel 2007 than in earlier versions. But the new chart cannot be linked to the new pivot table. It is permanently linked to the old pivot table.
My colleague Bill Manville wrote to me about this problem, citing an old forum post in which I doubted this could ever be solved. I’m glad to say that Bill has proved me wrong. He sent me a new protocol that makes this work.
- Make a copy of the worksheet with the old pivot table and pivot chart in a different workbook;
- Move the copied worksheet back into the original workbook;
- Change the new chart’s source data to the new pivot table;
- Change the pivot table’s data source to the new range;
- Refresh the pivot table.
The difference is that the worksheet is copied into a new workbook (or another existing workbook) rather than within the original workbook. When this happens, the pivot table still links to the original data, but the chart becomes unlinked from the pivot table. In fact, the chart changes back from a pivot chart to a regular chart. It is unlinked from any data range, and the series formulas have been converted to written-out arrays.
Since the chart has been unlinked in the process, it now can be relinked. Thanks, Bill, for saving us lots of time and effort.
Bill tells me that the familiar Excel 2003 behavior has been restored to Excel 2010.
Paulo Pessoa says
Hello,
I was trying to follow the steps listed in the “Copy a Pivot Table and Pivot Chart and Link to New Data” article, but after re-linking the copied pivotchart, excel 2007 simply remove the old pivotchart formating (colors, labels, captions, etc).
Is there any way to avoid excel to reset the pivotchart formating?
Monthly I have to copy a new sheet with pivottalbes/charts to generate weekly reports in my job, so having a way to workaround this issue would be really great.
Regards,
Paulo.
Jon Peltier says
Paolo –
This is one of those features that we’re stuck with. What I do, and what Microsoft even suggests, is to record a macro while you reformat the chart, then next time, just run the macro.
DQKennard says
I haven’t done the following with Pivots, just with charts and formatted tables, but it might work on Pivots, too. One sheet is my raw data (two years of quarterly data). Another sheet uses a variety of formulae (index/match, countifs, sumifs, etc) to pull the data from the raw data sheet. This is formatted to be a more useful/pretty set of tables, and structured usefully as a feeder to charts on another sheet. The charts link to this formula sheet. All I need to do is drop the data into the *raw* sheet, and the formatted tables and charts are good to go. I would think if a pivot pointed at the formatted tables, a simple refresh should pick up the new data, since nothing has happened to the Pivot’s references, only to the references’ references.
This wouldn’t work, of course, if a new set of data required a different structure or possibly if there were a change in the number of lines. Otherwise, a completely new set of data would just require me to do some minor cell edits in one of the columns so the formulae would know what to look for.
(In my application of this, the raw sheet contains two years of data, so the only change I have to make when the year changes is to change the years in two places so it grabs and labels stuff correctly. When I had to add a couple of lines to add a new reporting organization, I had to use some care in how I made the edits, but even the the charts did the Right Thing.)
Jon Peltier says
DQ – This should work for pivot tables as well. Paste new data, refresh the pivot. If the pasted data may have different numbers of rows, you can set up a dynamic range for the pivot data base, and use the name of this range instead of a cell address for the pivot table source data.
Narayanan says
Thanks a lot bill for the input and Jon for sharing this…this has really helped me save a lot of time.
RFENGR says
Here is a macro to automate the above including linking the new table.
Sub PivotTableChartClone()
‘
‘ PivotChartCopy Macro
‘ Copy entire worksheet over to new workbook to delink Pivot Chart from original Pivot Table
‘ Takes selected region, and copies any pivot chart or table in that region and returns it back to the original worksheet.
‘ Makes you select region to copy to in original worksheet.
‘ Automatically relinks the new Pivot Chart to the new Pivot Table
Dim rngCopy As Range, rngPaste As Range, wkbCurrent As Workbook, wksCurrent As Worksheet, wkbNew As Workbook
Set rngCopy = Selection
On Error Resume Next
Set rngPaste = Application.InputBox(“Select Cell to Paste Cloned Pivot Table and Chart to”, “Clone Pivot”, , , , , , 8)
If rngPaste Is Nothing Then End
If WorksheetFunction.CountA(rngPaste(1, 1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count)) > 0 Then
rngPaste(1, 1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Select
MsgBox “Paste region is not clear. Please make room and try again”, , “Error”
End
End If
‘Paste Column widths first
rngCopy.Copy
rngPaste.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Set wkbCurrent = ActiveWorkbook
Set wksCurrent = ActiveSheet
‘Copy entire worksheet to new workbook.
wksCurrent.Select
wksCurrent.Copy
Set wkbNew = ActiveWorkbook
Range(rngCopy.Address).Copy
wkbCurrent.Activate
rngPaste.Select
ActiveSheet.Paste
Dim chtLastChart As Chart, pvtLastPivot As PivotTable, rngSource As Range
Set pvtLastPivot = wksCurrent.PivotTables(1) ‘For some reason the first index is that last pivot that was added.
Set chtLastChart = wksCurrent.ChartObjects(ActiveSheet.ChartObjects.Count).Chart
chtLastChart.SetSourceData Source:=pvtLastPivot.TableRange1
wkbNew.Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub
Mustafa Senkardesler says
Very much appreciated the manual solution.
Thank you so much!
Kim Tran says
Hi Jon,
Topic Name:
Copy a Pivot Table and Pivot Chart and Link to New Data
I now have Excel 2010. My problem is exactly what the above guy described. I created a chart using the first pivot table and then re-copied the chart. I tried to change the data source of that chart to a different pivot table and if I read your comment correctly, Excel 2010 should have allowed me to select the data source range. However, that option is greyed out and I can’t select the data source. Please advise?
David says
Bill’s method (at the bottom of your article) worked great for me. Thanks so much!
A hint to that…associate the copied pivot chart to the new pivot table correctly the first time. After that it sticks and grays you out.
MegM says
Thank you SO much for this solution post! Today I needed to publish an analysis & dashboard on a really tight deadline, but all of a sudden my pivot charts wouldn’t behave. Fixed them with Bill Manville’s protocol above.
You guys saved my bacon. Jon Peltier, I appreciate this site more than ever.
Chris says
This is great thanks!
To get the formatting back, once you’ve relinked the chart to the new Pivot Table, you can copy your old chart and paste it over your new chart. Since it is locked and you can’t change the range, the only thing that changes is the formats!
Hope this helps.
Chris
Emma Manley says
I dont know if this is the same issue or not but i have created a pivot table eg( manager in rows, results in columns) I have then created a chart from that pivot table. I want a graph and table for each manager.
I thought i would just copy and paste then filter the new manager on the pivot table and the chart would refresh, but it doesn’t. I then go to change the data source and it is greyed out.
Is there any other way other than creating a new graph every time?
Colleen says
Emma, I’m getting the same problem – the data source field is grayed out on the chart.
What to do?
OK, here’s more detail.
I have a template workbook where I have 30 pivot tables on one sheet – fifteen topics, each with a point-in-time pivot and a cum-to-date pivot.
The charts related to the pivot tables are on separate tabs, one per topic.
All of the data comes from data connections, which are easily changed to fit the customer.
I look at the pivot tables for topic 11 – data looks great.
I then need to take two of the existing charts and point them at the pivots for topic 11.
But the data source box is grayed out. It points to the correct pivot table but it won’t refresh the charts.
Why is the data source box grayed out?
Jesus says
Hey this is a great tip that solves a problem I’m having, however may be one of you guys can tell me I’m having this problem to begin with.
I have a sheets with lots of charts, each one of them linked to a copy of a pivot (to me more precise, I created a pivot from an Access DB and from then one made copies to create all the charts), but there are times when some of the pivot charts change their source and then appear to be linked to Pivot Table 1 for no apparent reason. I wonder why this might happen. Any thoughts?
Jon Peltier says
Are the pivot tables all based on the same pivot cache?
Jesus says
Yes they are, otherwise the file size would be to big (each pivot cache is around 7MB and i have more than 30 charts).
NIkka says
Works well! Thanks!
Tom says
Not sure what I did differently following Bill’s instructions above but when I made changes to the connection string in the just copied worksheet now back in the original workbook those changes affected all of my existing worksheets (which I was trying to prevent). However, if I made changes to the connection string while the worksheet was in the new workbook and then copied it back to the original workbook, my changes did not affect existing worksheet.
manoj says
Hay guys….I am facing the same issue as is mentioned earlier by Jesus. Can you please help!
I have a sheets with lots of charts, each one of them linked to a copy of a pivot (to me more precise, I created a pivot from an Access DB and from then one made copies to create all the charts), but there are times when some of the pivot charts change their source and then appear to be linked to Pivot Table 1 for no apparent reason. I wonder why this might happen. Any thoughts?
Jon Peltier says
Manoj –
I don’t know why a pivot table would arbitrarily change its data source range in this way. I rarely use external data sources, so I haven’t had to deal with their associated problems.
Andrew says
Not sure if this is exactly the same problem. I’m trying to create multiple charts each showing different suppliers results from one data table in the workbook. The data is defined as a table. So if you have a sheet with a pivot table in it and a chart linked to that table – copy the sheet in the same workbook and change the settings in the pivot table and chart in the new sheet. If you want you can cut and paste the new chart and table into the old sheet. Seems to work. One thing I found out which is weird is that if you save the chart template and apply that to a new chart on a new pivot table – then it brings the old pivot table settings with it. Very frustrating! Fell out of love with Excel this morning!
Jon Peltier says
Andrew –
If you’re copying the sheet with the chart, doesn’t the chart maintain its formatting?
Chart styles have been problematic since ever, from the custom chart types of Excel 2003 and earlier to the custom chart templates in newer editions. And when combined with pivot tables, charts forget their styles, and you need to reapply their formats. This isn’t as traumatic as in Excel 2003 and earlier, but it’s still an adventure.
Andrew says
John
When I copy the sheet it does bring the formatting with it – which is fine. I was trying the chart formatting route as another way to achieve the same end – copying the pivot table, inserting a new chart and then applying the saved chart template to the new chart. In the end – copying the sheet within the same workbook seems to be the best solution for me.
Out of interest – does defining the data as a table make any difference?
Jon Peltier says
I don’t think defining the data as a table would make much difference. I have seen some issues with data in tables that I see with data in pivot tables, at least in terms of charts losing their formatting.
Jesus says
Hey John/Manoj
I still haven;t found how to solve this, however I think I’ve managed to identify two possible actions that creates this issue.
1. When I change the location of the Access Database, so when I click refresh it tries to find the Access file and then all pivot caches are reconnected to the Access Database, and then the issue appears. Some pivots datasources are changed to PivotTable 1.
2. When disconnecting a slicer that is linked to the vast majority of pivot tables I have in the file. Then the same issue arises.
Any thoughts John?
Jon Peltier says
Jesus –
I can’t comment on how pivot tables with external references (Access databases) will behave, nor how the use of slicers will affect the pivot tables.
Matt Johnson says
Saved me a lot of work with this trick. Thank you very much.
Gustavo says
Thank you Jon and Bill very much.
Catherine says
Hello,
I have an issue in Excel 2010 where I have a large data tab and several other TABS each containing multibple pivot tables for charting purposes. Somewhere in all those pivot table is a piece of code which is pointing to the incorrect data file (which no longer exits) I have gone through each pivot and changed the data source to correct data tab – Still will not refresh due to incorrect code somewhere — How do I find that piece of code – is there a special search
Many thanks for any possible help on this one
Jon Peltier says
Catherine –
What kind of error message do you get?
kris says
HI Jon,
I am really new at vba and amazed at your tutorials. i would liketo seek your advice on this situation i have.
I have a report that has charts created from a table (manually input from a pivot table).No vba is used at all for this excel report file.
I would like to use VBA to automate the data updating from the pivot table to the excel file table (which in turn gives the charts). I managed to combine multiple csv files to create a masterworksheet(using VBA). I also managed to create the pivot table using excel. Now i dont know how to proceed to ‘pull’ the data from the pivot table and having it ‘show’ up at my excel report file. Could you help to advise or point me in a direction?
Really appreciate your help
Jon Peltier says
Kris –
If the chart uses all the data in your pivot table, then all you need to do is make a pivot chart from the pivot table, and it will update when the pivot table updates.
Katie Newton says
Hi Jon,
I am new to excel and pivot tables so forgive me if I show ignorance/repeat any questions that others may have asked above – I have read all questions and answers and still am not clear.
I have very large sets of data (thousands) with many vertical columns too. I am using pivot tables to look at potential trends in the data. When I have a pivot table I am happy with, I want to keep this pivot table and then duplicate it onto a different sheet to then make more adjustments. I do not want to start my pivot table again from the data as the change I want to make may be very small. In my second pivot table, for example, I may only change one thing.
Here are the solutions that I have read about so far and the problems with each:
1. Copy and paste the pivot chart:
This does, as described, copy and paste the pivot chart into a new location, however you cannot then edit the pivot table and pivot the table. (It almost takes a picture of the table).
2. Moving it.
I have selected the entire pivot table and moved the pivot table onto a new sheet (through the options tab). However, this removes the original pivot table from the original sheet and moves it onto a different page.
How can I move it onto a different page, be able to edit it in the new sheet AND it still exist in the old sheet?
Jon Peltier says
Katie –
You should be able to select a pivot table, then click in an empty range on the same or a different sheet and paste, to get another working copy of the pivot table which can pivot independently. However, a pivot chart’s pivot state is linked to that of the pivot table based on which it was created. A copy of just the pivot chart will always look like the original chart; pivoting the new chart will pivot the old one in the same way.
DM says
Hi,
I am in position where I have more than 25 Pivots tables in one sheet and need to update these sheets from the data I get every day in excel sheet..
The challenge that I face is:- need to update every Pivot with changing the data source… Damn Painful.
I tried:-
I copy paste these pivot sheets to the daily report data sheet we get.. (as the name of the work sheet does not change ) … refreshed the data as file name is same… but it is not taking the data of the current sheet… its reflecting the new data.
Please suggest some solution to this even for charts… I am just pissed off.
Please suggest some help.
Jon Peltier says
Where is the data with respect to the pivot tables? Same sheet? Different sheet in same workbook?
Pasting the pivot tables into a different workbook is probably doing it backwards.
Instead of trying to make the pivot tables work in a different workbook, why not paste the new data into the workbook that contains the pivot tables, overwriting the previous data.
Catalin Bombea says
Hi Jon,
I developed a solution (mostly a hack, but it works) to solve this annoying problem, until Microsoft engineers fixes the bug.
I managed to narrow down the source of the problem, and the circumstances when this problem will occur.
The problem will appear under these circumstances:
-the excel file was received from another person, and it was created in a previous version of excel, this is one of the cases the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is checked.
-the file contains a pivot table, with or without a pivot chart.
-the pivot chart, or the pivot table, or both, are moved into another sheet (the chart with cut-paste, pivot with the option-Move Pivot Table)
This action, of moving the chart or pivot table will add an absolute path to the data source :
‘Book1 only pivot table.xlsx’!Table1
As you can see, the data source will contain the file name AND table name, not only table name, as it should normally be, at this moment, there is no way to use the file as before: when you rename the file, the data source will refer to the old name…
I spent 2 days to locate the problem and find the cure to return to normal. If the file has only a pivot table, it’s not a big deal, can be easily recreated, but when there are a lot of pivot tables, it’s very hard to recreate all the work.
The key is in the excel archive: if you right click the excel file, and open it with an archiver, this is our guilty folder:
xl\pivotCache\_rels , the problem is related to pivot cache relationships…
Inside this folder, there should be at least 1 file, named: pivotCacheDefinition1.xml.rels
If there are multiple caches, the rest of the files will be: pivotCacheDefinition2.xml.rels, pivotCacheDefinition3.xml.rels
The normal content of this file should look like:
After moving the pivot chart ot pivot table, if the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is checked, the problem will never fail to appear…
The pivotCacheDefinition1.xml.rels file will have this content:
Now, when you open the renamed file, you might get an error message saying that “Data Connections are disabled” you have to click to enable, even there there is no external connection.
It’s a mistery why excel considers that we have external content and adds a xlExternalLinkPath/xlPathMissing relationship, seems like a bug to me.
If you look in the archive, in xl\pivotCache folder, there are 2 files:
pivotCacheDefinition1.xml and
pivotCacheRecords1.xml
The pivotCacheDefinition1.xml will contain the following expression:
The red part will never be found in a normal file, the normal value should be:
And now, the solution:
Step 1:
Open the file in excel, uncheck the the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save”
Step 2:
From the pivotCacheDefinition1.xml.rels file, simply delete the following part, but ONLY that, otherwise excel will not be able to open it, it will become corrupted:
Type=”http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing” Target=”New%20Microsoft%20Excel%20Worksheet.xlsx” TargetMode=”External”/><Relationship Id="rId1"
(do not touch the value from pivotCacheDefinition1.xml, this will be solved automatically by excel)
Step 3:
open the file with excel, SAVE the file, and CLOSE it.
The problem is gone, you will be able to save the file with another name, the reference will be relative always .
Now, the value from pivotCacheDefinition1.xml will not contain the red reference ID, excel took care of that itself.
Note:
If the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save” is unchecked, the pivot chart and pivot tables can be moved, the problem will not appear.
Excel will check this option automatically if the file was created in a previous version, or if you use the Document Inspector to remove personal information, the next time you open the Privacy Options you will notice that the option is checked, even if it was not checked before using the Document Inspector. If the option is greyed, you can only activate it if you use the Document Inspector.
Users should do this on a copy of their file, it’s not bullet proof, if they make a mistake, they can corrupt the file irreversibly, excel will open with attempts to repair the corrupted file.
More details can be found on my article here:
http://www.excel-first.com/cannot-open-pivot-table-source-file/
This was a hard nut to crack… hope it will solve other users problems too.
Catalin Bombea
Simon says
Fantastic. Not the first time your blog has saved me a bunch of time doing something clever with graphs. And thanks to Bill. :-)
dario says
I tried it and it works only from excel to excel. But it does not work if you have copied a pivotchart in powerpoint presentation, and want to change its datasource.
The chart in powerpoint stays linked to the original pivot table in Excel even if you copy it in a new powerpoint presentation. This is very frustrating because I have spent a lot of time in formatting the powerpoint and now cannot unlink the charts. Can someone help?
Jon Peltier says
Dario –
Did you copy the chart into a new workbook, specifically the one with the new pivot table? The chart links to the original pivot table, with nothing to do with which presentation it resides within.
You must paste the chart into a different workbook to break the links to the original pivot table, then link it to the new pivot table, then copy it and paste it into the new presentation.
Dario Parziale says
Hi Jon,
Thanks so much for your response. I may not have explained myself well: I am able to do what you explained (and I tried to copy it into a new workbook unlinking it from the original pivot table), but my goal is not to “copy and paste the chart into a new presentation” (or into a new slide within the same presentation).
My goal is to copy the pivot-chart from excel, paste it into powerpoint, spend time formatting it in powerpoint (this is the time consuming part), and then duplicate the powerpoint slide with the chart in order to have the same chart already formatted in multiple slides. After duplicating the slide, I would like to unlink the chart from the original pivot table in excel, and link it to a new pivot table in excel (and this is what I am unable to do and would like to get guidance).
The reason why I need it is that I am doing a very large analysis for multiple clusters, and I need to show the same chart for each of the clusters. So ideally I would like to format the chart once in powerpoint, and then replicate it for each cluster.
If you have any clues that would be great. Thanks!
Jon Peltier says
Dario –
And what I suggested is what you need to do. After you copy the PowerPoint slide, both charts are still linked to the same pivot table. Copying the slide does not unlink the copied chart.
You need to copy the chart from the copied slide (or from either of the slides, really), paste it into a workbook which is not the one that contains the pivot table, and this is what you need to do to unlink the chart. This pasted chart in unlinked, then you can link it to another pivot table, then paste it into the copied slide.
Couldn’t you format the chart in Excel? This would make the tedious process somewhat less tedious than dealing with a chart you’re formatting in PowerPoint.
Could you use the larger data set with all of the clusters as the source for the pivot table? Keep the clusters as a filter field. When you filter on this, it’s the same chart that shows the data for the newly selected cluster.
dario says
Thanks a lot Jon, that works!
I still have the issue that I have to make some small adjustments for each chart I paste back in powerpoint (I need to reposition them because when I copy them back from Excel they are centered in the slide, change the color coding in some instances), but that is much better than before (I do the initial formatting in Excel, but still need to fix some more things in powerpoint).
And thanks for the tip with the pivot table filters. Yes indeed I am keeping an active filter for each cluster (so I have a pivot table for each cluster actively filtered) so that every time I refresh the dataset all the pivot table refresh and charts in powerpoint.
Thanks again!
L.Shaver says
Manual Workaround with easier/less steps
0. Create and format your Pivot Table and Chart.
1. Copy the Worksheet with the Pivot Chart and Pivot Table Twice (or more) within your workbook. (1 will replace the original, the others are your variations with different data / filters, etc.)
2. Delete the original worksheet
3. Rename Worksheets, Change Data source, filters, etc in the table as req’d.
4. In Each of the work worksheets (including the new original):
4a. Right Click on the Chart
4b. “Select Data”
4c. Click anywhere in the pivot table
4d. Click ok.
5. Celebrate.
Kim Swider says
THANK YOU! Your innovative thinking and solution to fix the quandary of pivot table charts being forever linked to the parent worksheet is BRILLIANT! Thank you soooo much for sharing your knowledge.
Raksha says
We have one “LegacyExcel” file. It is in “.xlsb” format and has one raw data sheet from which pivot charts are created. In one sheet there are around 8 pivot charts.
Now we to change some column names in the raw sheet, which are used as “Values” in the pivot chart.
We followed below steps:
1)Delete all the values from “LegacyExcel”
2)Rename the column names
(But when we renamed some column names, it started popping up the message “if we need to refresh the pivot sheets”.It asks every time when we open that excel)
3)When we loaded the data to this sheet, excel started getting hanged.
Can you please let us know how can we resolve this.
Please let me know if query is not clear
Raksha says
Just an addition to my above points:
I feel, in ““LegacyExcel” pivot charts are created using the option “Add this data to the data model”. Because in the sheet which consists of pivot charts, I can see just the “pivot chart” and not the “pivot table”
Jon Peltier says
Hi Raksha –
Let me look into it. I know pivot tables work differently in conjunction with the data model than with a regular pivot cache.
Jon Peltier says
When you use Power Query and Power Pivot to process data and generate pivot tables and charts, behaviors are different than if you simply use worksheet data. In any case, if you modify the header labels in pivot source data, you risk breaking the structure of the pivot table and chart.
Since I don’t know enough about Power Pivot and its output, I refer you to the Excel Guru forum, which covers Excel and the Power BI tools. Make sure you follow the practices in Getting Answers For Your Excel Questions. In your question, don’t forget to include your version and edition of Excel, because these are especially important due to rapid development of Power Pivot and its features.
Lukasz says
Thank You, You saved a lot of my time.
Ionut Enasoae says
Didn’t work for me, when linking the new chart to the new pivot table the format was messed up.
The BEST SOLUTION I could find is to save the existing pivot chart as a template: right click on the pivotchart -> save as template (make sure it’s saved in the templates folder, it will appear by default: C:\Users\YOUR USER NAME\AppData\Roaming\Microsoft\Templates\Charts – in my case). Now you can duplicate the pivot table as many times as you want. Select the new pivot table to which you want to connect the new chart and under PivotTable Tools-> Analyze click on PivotChart. It will ask you what kind of chart you want, select Templates in the dialog box, select the template chart you just selected and it will work like a charm EVERY SINGLE TIME – it will generate the same chart for the new pivot table, the format will be exactly the same as the existing previous chart. If the template doesn’t appear there it’s because it was not saved in the templates folder.
cherine says
Thank you so much for sharing. It worked!!!!! you only need to copy the chart to another workbook and bring it back.