Copy a Pivot Table and Pivot Chart and Link to New Data
by Jon Peltier
Thursday, July 15th, 2010
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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.
Related Posts:
- Make a Copied Chart Link to New Data
- Copy Chart and Data and Preserve Links
- Grouping by Date in a Pivot Table
- Dynamic Chart using Pivot Table and VBA
- Using Pivot Table Data for a Chart with a Dual Category Axis
- Preliminary Data Exploration with Excel Pivot Tables
Posted: Thursday, July 15th, 2010 under Data Techniques.
Comments: 9
Comments
Comment from Paulo Pessoa
Time: Monday, July 19, 2010, 3:34 pm
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.
Comment from Jon Peltier
Time: Monday, July 19, 2010, 10:03 pm
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.
Comment from DQKennard
Time: Monday, August 9, 2010, 3:47 pm
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.)
Comment from Jon Peltier
Time: Monday, August 9, 2010, 6:50 pm
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.
Comment from Narayanan
Time: Thursday, December 30, 2010, 8:15 am
Thanks a lot bill for the input and Jon for sharing this…this has really helped me save a lot of time.
Comment from RFENGR
Time: Saturday, April 2, 2011, 12:43 am
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
Comment from Mustafa Senkardesler
Time: Friday, August 12, 2011, 11:01 am
Very much appreciated the manual solution.
Thank you so much!
Comment from Kim Tran
Time: Wednesday, September 14, 2011, 11:56 pm
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?
Comment from David
Time: Tuesday, October 25, 2011, 9:15 am
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.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.