Make a Copied Chart Link to New Data
by Jon Peltier
Monday, March 30th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
You spend a lot of time and effort getting your chart to look just right, and now you want to use your beautiful chart to show similar data on other sheets. When you copy this chart from one sheet and paste it onto another, why doesn’t it link to the cells on the new sheet? How can you make a copy of this chart that references new data without having to change all of the links yourself?
1. Chart Embedded on Worksheet with its Data
When you start with data and a chart on the same worksheet, it looks like this. The chart series is selected, so the series formula in the formula bar shows the data used to create that series, and the worksheet ranges used in the series are highlighted.
Add a sheet with different data in exactly the same cells, copy the chart, and paste it into the new sheet. The series is selected, but the data in this sheet isn’t highlighted, and a peek at the series formula shows that the chart is still linked to the original data.
Why is this? I don’t know exactly, but I’m sure it has something to do with history and with the semi-independent nature of charts.
In the old days, like around Excel 4, Excel only had sheets, not workbooks, and each sheet existed as a standalone file. There were worksheets (xls files), chart sheets (xlc files), and macro sheets (xlm files). A chart had to fully reference its data source by worksheet name, which meant by file name.
Don’t ask about macro sheets; they were used to store procedures written in the ancient XLM language, and are obsolete but have not yet completely disappeared.
Nowadays, that is, from Excel 5 onwards, Excel has had workbooks that can contain any number of the different types of sheets. Charts can reside as standalone sheets within a workbook, but they can also be embedded in other sheets (worksheets, chart sheets, and dialog sheets). An embedded chart does not need to be embedded in the same worksheet, or even in the same workbook, that contains its data. Therefore, a chart still has to fully reference its data source by worksheet name, and perhaps also by workbook name.
Don’t ask about dialog sheets either; they are an antiquated way to generate dialogs in the first version of Excel VBA, and have been supplanted by VBA UserForms.
We saw this referencing in action when we copied the chart and pasted it into another sheet. The references in the chart did not change, so the pasted chart still referenced the original data.
Instead of copying just the chart, this time copy the sheet with its chart. The new sheet is now named ‘Sheet1 (2)’, meaning copy 2 of Sheet1, and we see from the highlighted range and series formula that the chart on the new sheet references the data on the new sheet.
A successful copy of the chart to a new sheet, using data on the new sheet, then requires this protocol:
- Copy the original worksheet with its data and embedded chart
- Replace the data on the copy of the sheet with the new data.
2. Chart on Chart Sheet
If you start with data on a worksheet and the chart on its own chart sheet, it will look like this. I am displaying two windows for the workbook, one showing each sheet. The chart series is selected, so the formula bar shows that the data comes from the worksheet.
If you simply copy the chart sheet, the copy will of course refer to the data on the original worksheet. However, if you copy both the worksheet and chart sheet in one operation, Excel is smart enough to link the copy of the chart to the copy of the data. The copied worksheet is named ‘Sheet1 (2)’ and the copied chart sheet is named ‘Chart1 (2)’. The series formula for the highlighted chart in ‘Chart1 (2)’ shows that the data it references is in ‘Sheet1 (2)’.
Again, the successful protocol for copying a chart sheet so that it references new data is:
- Copy the original worksheet with its data and the original chart sheet
- Replace the data on the copied worksheet with the new data.
3. Chart Embedded on Different Worksheet From its Data
When you have data on one worksheet and its chart embedded on another, it looks like this. The series formula for the selected series in the chart on Sheet2 references the data on Sheet1.
Learning the lessons from above, let’s copy the two sheets within the workbook. The copied sheets are ‘Sheet1 (2)’ and ‘Sheet2 (2)’. But to our chagrin, the chart in ‘Sheet2 (2)’ still references the data in ‘Sheet1′, not that in ‘Sheet1 (2)’.
Apparently the designers of Excel decided it was too complicated to keep track of the sheets that charts on other sheets reference. I’m not sure I blame them.
If the two sheets are copied to another workbook (in this case a new workbook named Book4), the same situation occurs. The chart in [Book4]!Sheet2 still references the original data, in [ChartData.xls]!Sheet1.
Our copying trick must take another tack. Below we see two windows each for two workbooks. The original workbook has been renamed ChartData1.xls, and a copy was made, named ChartData2. The copy could be made using SaveAs from the File menu, or by making a copy in Windows Explorer.
Move the two sheets from ChartData2.xls into ChartData1.xls. This can be done in one step, but if you do it in two steps, it’s okay, because the chart still references the data, even if it’s in the other workbook. The moved sheets are ‘Sheet1 (2)’ and ‘Sheet2 (2)’. The highlighted series in ‘Sheet2 (2)’ references the data in ‘Sheet1 (2)’ after the move.
A successful copy of a data worksheet and a chart embedded on another worksheet, using new data, requires this protocol:
- Copy the original workbook with its data worksheet and chart embedded on another sheet.
- Move the two sheets from the copied workbook to the original workbook.
- Replace the data on the copy of the data worksheet with the new data.
- Reopen the copied workbook and repeat steps 2 and 3 as needed.
This can be done a bit more easily by treating the copied workbook as a template. You can move the copied workbook into your templates directory
C:\Documents and Settings\[user name]\Application Data\Microsoft\Templates
and insert it by right clicking on the sheet tab, choosing Insert, and selecting the template in the dialog. If the template contains multiple sheets, all sheets are inserted into the open workbook.
Alternatively, you can store the template workbook anywhere, for example, in the same directory as the main workbook, and insert the template’s sheets using this simple VBA code:
ActiveWorkbook.Sheets.Add _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count), _
Type:=ActiveWorkbook.Path & "\" & "ChartData2.xls"
The inserted sheets are ‘Sheet1 (2)’ and ‘Sheet2 (2)’ and the highlighted series in ‘Sheet2 (2)’ references the data in ‘Sheet1 (2)’.
An alternative template-based technique to successful copy a data worksheet and a chart embedded on another worksheet, using new data, requires this protocol:
- Copy the original workbook with its data worksheet and chart embedded on another sheet.
- Manual:
- Move this copied workbook into the templates directory.
- Use Insert from the sheet tab’s right-click menu to add the template’s sheets.
- Programmatic:
- Use VBA Sheets.Add code to insert the template’s sheets.
4. Who cares, just change the links
A final approach, which is often the initial approach, is to simply copy the chart in any convenient way, then change the links. The chart in ‘Sheet2 (3)’ below refers to data in ‘Sheet1 (3)’, but we want to change that to ‘Sheet1 (4)’.
All of these links can be changed tediously, that is, manually. But after the second or third change, the boredom level increases rapidly, and the chance of error increases even more rapidly.
In Change Series Formulas I showed how to programmatically change the series formulas in your charts, and in How to Edit Series Formulas I provided a utility that does the task for you. Here is the dialog from this utility with the old and new text strings.
Using this utility eases much of the pain of juggling charts between different data sheets.
Related Posts:
- Copy a Pivot Table and Pivot Chart and Link to New Data
- Excel Templates Demystified
- How to Edit Series Formulas
- Adding Excel Chart Data
- Copy Chart and Data and Preserve Links
- Chart Series Data Highlighting
- Easier Interactive Multiple Line Chart
- Highlighted Chart Source Data
- Interactive Multiple Line Chart
- Dynamic Chart with Multiple Series
Posted: Monday, March 30th, 2009 under Charting Principles.
Comments: 29
Comments
Comment from Primo
Time: Tuesday, March 31, 2009, 4:08 am
In the example where you copy to a new workbook and it still refers back to the original, could you not use Edit..Links..Change Source to move the links to the new workbook?
(I use 2000, so I’ve no idea if this works in 2007)
Comment from Jon Peltier
Time: Tuesday, March 31, 2009, 6:14 am
Primo -
Good point. This works if the worksheet names are the same, which a copied worksheet and its original would have. Now I should work this into the article somehow.
Comment from simon
Time: Sunday, April 5, 2009, 2:38 pm
On Excel 2007, when I copy a sheet with a chart and it’s data to a new sheet, the chart still refers to the data on the original sheet, so the above method doesn’t work. Do you have any idea how to link a copied chart to new data on Excel 2007?
Comment from simon
Time: Sunday, April 5, 2009, 3:25 pm
In reference to the comment that I left above, I need to add that the chart contains error bars that refer to that chart. I know that I can copy a sheet with a chart by holding the CTRL key and dragging the sheet tab to the right (creating “sheet 1 (2)” from “sheet 1″, rather than just copying “sheet 1″ to a new sheet). Whilst the new chart refers to the new sheet, and changes by changing the new data, the error bars still refer to the original sheet. So my question is: is there a way that I can copy a sheet in Excel 2007 with a chart that contains error bars to a new sheet so that both the chart itself and the error bars both refer to the new sheet?
Comment from Jon Peltier
Time: Sunday, April 5, 2009, 6:06 pm
Whoa, there’s a bug I didn’t check for. Just one more issue with Error Bars in Excel 2007. All the rest of the links seem to change, but not the custom error bar values.
Here’s a technique that seems to work. Copy the sheet to a new workbook. Rename the sheet. Move the sheet into the original workbook.
If you do not rename the sheet, when you move it back into the original workbook, its name becomes something like “Original Sheet (2)”, but the error bar link is to “Original Sheet”.
Comment from Jeff Weir
Time: Thursday, April 16, 2009, 3:18 am
Hi John. Great blog – I’ve added you straight to my google reader feed.
I recently had a situation where I copied a worksheet with heaps of charts embedded in it, but the charts in the new copy of the worksheet all referenced the old worksheet, not the new copy.
This only happened with this particular workbook, for some reason. I believe that what should normally happen when you create a copy of a worksheet is that any embedded graphs reference the old worksheet. Indeed, if I opened a new book, and created a few graphs, then any embedded graphs in the new copy referenced the new sheet copy.
But on my original worksheet, not so. Granted, there are about a zillion graphs in the worksheet concerned, so this might be causing excel to do something different for this particular worksheet.
I found a few posts on the net with other people having a similar problem. Perhaps it only rears its head when there’s heaps of graphs involved, or some other factor.
Anyway, after much mucking about, I found that if you copied the sheet to a new book (i.e. right click on the tab you want to copy, then select ‘To new book’ under the “To book” list, then hit OK) the graphs in the new worksheet copy reference that new sheet, rather than the old. So all I had to do then was rightclick on the tab on the new worksheet copy, and copy it back to the original sheet.
Regards
Jeff
Comment from Jon Peltier
Time: Thursday, April 16, 2009, 6:34 am
Hi Jeff -
This is the behavior I’d expect if you had selected all the cells on a worksheet, and pasted this into a blank sheet. All the charts are copied, but the links point to the original sheet. If you actually copy the whole worksheet, tab and all, the copies of the charts should point to the copy of the data.
I don’t know what the effect of having scores of charts on a sheet might be. Generally I don’t put more charts on a sheet than I can get to in two clicks of a scroll bar. A quick experiment with 256 charts on a sheet in Excel 2007 showed that the links updated to the new parent sheet as expected.
I tried to put 256 charts onto an Excel 2003 sheet and got the No New Fonts error. Before I could end task on Excel, it crashed. So I went more systematically in 2003. Copying a sheet with 128 charts worked as expected, copied charts pointing to copied ranges.
A sheet with 160 charts could not be copied within the workbook. Actually it could, but only the data was copied, not the charts. And I only got one attempt: after deleting the chartless sheet, the fully loaded sheet couldn’t be copied. I could go through the commands, and Excel just ignored me.
Then when I closed and reopened the file, I got a warning about damage to the file. After repairing the file, no charts were left in it, and I got a message saying:
Microsoft Office Excel File Repair Log
Errors were detected in file ‘C:\Documents and Settings\Jon Peltier\My Documents\test\too many charts.xls’
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.
So incorrect links is not the major problem here.
Comment from jeff weir
Time: Thursday, April 16, 2009, 8:02 pm
Hi john. Man, I’ve hardly slept since discovering this blog yesterday. It’s my usual day off work today, and here I am sitting down in front of Excel after reading nearly all of your posts.
The problem could be with an addin I used to create the microcharts. Can’t remember the addin name, as used the work computer to do it. It was a free microcharting utility.
On an unrelated note, when building a dashboard report with this addin, I put some charts in some cells, using the snap to grid option. Later on, I used the auto outline feature to collapse the sheet just back to rows that happenned to contain the embedded charts.
This has the effect that the graphs look like sparklines when the spreadsheet is collapsed (using auto outline) but look much more like normal charts when the spreadsheet is expanded. This is cool – with a click of a button, you can expand from the ’small multitudes’ effect and zoom in closely on the charts.
Have you ever used this in your reporting solutions?
Regards, Jeff
I accidentally must have
Comment from Jon Peltier
Time: Thursday, April 16, 2009, 9:52 pm
Hi Jeff -
Welcome to the blog. That’s an interesting approach you’ve described. I haven’t used it, because I haven’t stumbled across it yet. But with your “guidance” I have an opportunity to try it out.
Comment from David
Time: Wednesday, April 22, 2009, 6:33 pm
Really Really Helpfull, saved me approx 80hours of creating new charts every day a months… thx allot
Comment from Brian Murphy
Time: Monday, April 27, 2009, 10:42 pm
Hi Jon,
I know you know this already, but I thought I’d pitch in.
Not too many folks use Defined Names to specify chart data ranges, but the result of copying charts that do can differ from what happens with regular cell ranges.
The technique you described that boils down to doing a couple of “moves” to accomplish a “copy” will correctly handle this situation.
Great blog!
Brian
Comment from Jon Peltier
Time: Monday, April 27, 2009, 11:09 pm
Brian -
Good point about the names. I hadn’t mentioned this because the post was getting long already.
It seems to me that workbook-level names behave much like plain old cell references. It’s the worksheet-level names that act up. In many cases, copying a sheet with charts that have worksheet-level named ranges as their series source data often results in the chart data being delinked from any worksheet cells, and values end up hard coded into the series formulas. Not the expected behavior by any means, and I think Excel 2007 does something different.
Comment from Rasmus
Time: Wednesday, May 6, 2009, 9:19 am
I mass produce a large amount of sheets with “show report filter pages” and then mass copy formulas refering to each pivot table. Unfortunatedly the way described above (coying whole sheet) is not possible here, will make a lot of manual work. Any ideas how to solve the problem?
BR
R
Comment from Jon Peltier
Time: Wednesday, May 6, 2009, 10:55 am
I don’t know where the additional work comes in; presumably it a complex setup you’ve developed.
Could you produce one sheet, and cycle through the filter pages on by one? Then there’s only one chart, and it links to the data.
Comment from Rasmus
Time: Thursday, May 7, 2009, 2:35 am
hi,
Thx for the quick reply.
Yes, that is actually what I did initially, but then I got a request to produce the report by sheets.
BR
R
Comment from Jon Peltier
Time: Thursday, May 7, 2009, 7:05 am
Could you train the users to select the pivot table page field items?
Comment from Rasmus
Time: Thursday, May 7, 2009, 7:28 am
Yes.
Or more precisely: I decided to put the first page with an active Pivot table and all the charts on and then I added all the other sheets with the info but without sharts. So, some kind of hybrid.
BR & thanks for the help
Rasmus
Comment from Ali
Time: Tuesday, May 12, 2009, 10:08 am
General comment on your site, tutorials and blogs: This is a great site and has been incredibly useful for someone like me who has to bludgeon my way through creating code. Basically, I find bits and pieces on the web and here and there and then torture them into doing what I want them to do eventually.
What I’m trying to do now is create several charts in workbookA, based on the data in several sheets in workbookB. Not suceeded yet.
Also: any idea why PlotArea commands don’t work unless the graph is visible (so can’t use ScreenUpdating = False and have to zoom to small screen size so that all 10 graphs in a row can be ’seen’ in order to get the plot area to adjust.
Comment from Jon Peltier
Time: Tuesday, May 12, 2009, 11:13 am
Hi Ali -
What version of Excel are you using. Excel 97 used to have problems with some chart formatting commands if the chart was not completely in view in the window. I haven’t noticed that problem in a long time, though. And I’m not sure about the ScreenUpdating setting.
You might have less trouble if you make sure you are not selecting the chart and its elements in code before formatting them. Change
Object.Select
Selection.DoSomething
to
Object.DoSomething
Comment from ali
Time: Wednesday, May 27, 2009, 2:25 pm
Thanks so much for that rapid response!! I, in contrast, have been otherwise occupied and just getting back to this now. I’m in Office 2003 sp2.
The main culprit is the plot area formatting
I’ve got:
With ActiveChart
.PlotArea.Height = 138
.PlotArea.Top = 0
.PlotArea.Width = 232
.PlotArea.Left = 9
.PlotArea.Right = 0
.PlotArea.Border.LineStyle = xlNone
.PlotArea.Interior.ColorIndex = xlNone
End with
These height and width are the same area as the chart area height and width (I am creating a bunch of small graphs and need the plot area to be as large as possible, i.e. not leave annoying wasted space within the chart area and a little squishy graph).
Thanks again for the response.
Comment from Jon Peltier
Time: Wednesday, May 27, 2009, 2:49 pm
Is there still a question in there?
There is no such property as .PlotArea.Right, by the way. Set your top and left, then match the width and height to the chart area:
.PlotArea.Width = .ChartArea.Width
.PlotArea.Height = .ChartArea.Height
Comment from Steven M.
Time: Wednesday, August 19, 2009, 3:37 am
Hi Jon,
I’ve learned a lot from your site and usually don’t have any problems following along. This time, I’m not getting the same result as you and am confused.
You wrote, “Instead of copying just the chart, this time copy the sheet with its chart.” Whenever I copy a sheet, the new sheet has all the data but none of the charts. A sheet loses its charts when I try to move it from one workbook to another as well.
I’m using Excel 2007 and get the same result in both *.xls and *.xlsx workbooks. To copy the sheet, I right click on the sheet name tab and select “Move or copy…” On the dialog I check the “Create a copy” box and click “OK.” I end up with 2 sheets named “Foo” and “Foo (2),” but “Foo (2)” has no charts I can see. What am I missing?
Would you please detail the steps you are using to copy both the sheet *and* its chart?
Thank you,
Steven
Comment from Jon Peltier
Time: Wednesday, August 19, 2009, 7:58 am
Steven -
To copy the sheet with its embedded charts, I am simply copying the sheet. When I place the new sheet, it contains all of the charts.
This was problematic in earlier versions of Excel 2007. In SP2 it appears to work as expected.
Comment from Steven M.
Time: Wednesday, August 19, 2009, 3:06 pm
Thank you for your reply Jon.
I am running Excel 2007 SP2 on Vista SP1. I tried copying a sheet using all 3 methods described on http://office.microsoft.com/en-us/excel/HP012170351033.aspx(menu bar Cells->Format->Move/Copy, right click on tab, and control-click-drag). In all 3, the charts are not in the new sheet. Do you have any other suggestions?
Regards,
Steven
Comment from Jon Peltier
Time: Wednesday, August 19, 2009, 4:31 pm
Steven -
All three of these methods works fine for me, with the embedded charts being copied along with their parent worksheets. I would have suggested updating to SP2, but you report that you already are at that level. So it’s “one of those things”, which Office has in abundance.
Does this happen to all sheets with embedded charts, or just this special one? Were the charts originally made in Excel 2007, or was the file updated from an earlier version?
Here’s something to try which has helped to clear up some other misbehavior:
Click on the Office button
Click on Excel Options
Click on Resources
Click on Diagnose
If this doesn’t work, I’m not sure what’s next.
Comment from Steven M.
Time: Thursday, August 20, 2009, 6:08 am
I found the issue Jon!
After navigating the Options dialog to run the diagnostics as you suggested, I decided to review the multitude of other options. I stumbled upon “Cut, Copy, and sort inserted objects with their parent cells” in the Advanced category. I enabled it and now when I copy a worksheet, my charts get copied too. I have no idea why it wasn’t enabled before.
Thanks again for your site. It is very useful and educational.
Regards,
Steven
Comment from Jon Peltier
Time: Thursday, August 20, 2009, 8:20 am
I have to remember that for the next person who has this problem. Thanks for digging.
Comment from Ken Morison
Time: Thursday, January 14, 2010, 6:55 pm
Another variation on the theme. When I copied (not moved) Sheet1 from one workbook to another it created Sheet1 (2) as expected but the chart refered to Sheet1 in the new workbook. All I did was change its name to Sheet4 before copying it and everything was fine. A Move worked correctly.
Comment from Jeff B
Time: Wednesday, March 10, 2010, 5:37 pm
I encountered the mysterious source data referencing when copying a sheet with just one embedded chart. I sent the file to a co-worker who has the same 2003 version of Excel. when she did the copy, the source data referenced the new sheet. After she created all the sheets I needed, the file was emailed back to me. Just for fun, I tried the copy again. Now it works for me as well.
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.