Copy a Pivot Table and Pivot Chart and Link to New Data

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:

  1. Make a copy of the worksheet with the old data and chart;
  2. Copy the new data;
  3. 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:

  1. Make a copy of the worksheet with the old pivot table and pivot chart;
  2. Change the pivot table’s data source to the new range;
  3. 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.

  1. Make a copy of the worksheet with the old pivot table and pivot chart in a different workbook;
  2. Move the copied worksheet back into the original workbook;
  3. Change the new chart’s source data to the new pivot table;
  4. Change the pivot table’s data source to the new range;
  5. 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.

Peltier Tech Chart Utility

Comments

  1. 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.

  2. 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.

  3. 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.)

  4. 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.

  5. Thanks a lot bill for the input and Jon for sharing this…this has really helped me save a lot of time.

  6. 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

  7. Mustafa Senkardesler says:

    Very much appreciated the manual solution.
    Thank you so much!

  8. 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?

  9. 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.

  10. 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.

  11. 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

  12. 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?

  13. 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?

  14. 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?

  15. Are the pivot tables all based on the same pivot cache?

  16. Yes they are, otherwise the file size would be to big (each pivot cache is around 7MB and i have more than 30 charts).

  17. Works well! Thanks!

  18. 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.

  19. 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?

  20. 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.

  21. 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!

  22. 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.

  23. 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?

  24. 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.

  25. 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?

  26. 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.

  27. Saved me a lot of work with this trick. Thank you very much.

  28. Thank you Jon and Bill very much.

  29. 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

  30. Catherine –
    What kind of error message do you get?

  31. 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

  32. 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.

  33. 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?

  34. 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.

Subscribe without commenting

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites