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.
Primo says
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)
Jon Peltier says
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.
simon says
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?
simon says
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?
Jon Peltier says
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”.
Jeff Weir says
Hi Jon. 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
Jon Peltier says
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 SettingsJon PeltierMy Documentstesttoo 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.
jeff weir says
Hi Jon. 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
Jon Peltier says
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.
David says
Really Really Helpfull, saved me approx 80hours of creating new charts every day a months… thx allot
Brian Murphy says
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
Jon Peltier says
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.
Rasmus says
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
Jon Peltier says
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.
Rasmus says
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
Jon Peltier says
Could you train the users to select the pivot table page field items?
Rasmus says
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
Ali says
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.
Jon Peltier says
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
ali says
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 withThese 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.
Jon Peltier says
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
Steven M. says
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
Jon Peltier says
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.
Steven M. says
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 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
Jon Peltier says
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.
Steven M. says
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
Jon Peltier says
I have to remember that for the next person who has this problem. Thanks for digging.
Ken Morison says
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.
Jeff B says
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.
Gyorgy F. says
Hi Jon,
You wrote:
“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.”
And that is usually the case indeed. In one of my computers however (WinXP, Off2007) the copied new sheet’s embedded chart annoyingly enough references back to the old (source) worksheet, whatever i do… After spending several hours on searching a solution on the Internet I found no help.
You also mentioned above:
“This was problematic in earlier versions of Excel 2007. In SP2 it appears to work as expected.”
Finally i tried this, and voilà: installing Office 2007 SP2 has solved the issue.
Thanks for your site. It saved the rest of my day.
Regards,
Gyorgy
Kathy says
Can I select several cells which are not adjactent to each other and then create chart on another sheet?
Brandi says
Hello,
I have a worksheet that contains pivot tables and related pivot charts. I would like to copy the tables and charts and then modify the filters so that the new charts will reflect the filter changes. When I tried the steps above of copying the worksheet, any changes I made to the new pivot chart or table still linked and affected the original sheet. Is there any way to link the copied pivot chart to the new copied pivot table?
Jon Peltier says
Brandi –
I just checked this in Excel 2010. I created a pivot table on a worksheet, and created a pivot chart on this same worksheet. I copied this worksheet within the same workbook, so Sheet1 became Sheet1 (2). The pivot chart on the original worksheet stayed linked to the original pivot table, while the pivot chart on the copied worksheet was linked to the copied pivot table.
Brandi says
Thank you for your quick reply Jon!
I am currently using Excel 2007, so maybe that’s my issue. I will see if I can upgrade to 2010. Do you know of any workarounds within the 2007 version?
Jon Peltier says
I didn’t use 2007 much at all. When I started using 2010, it didn’t irritate me as much, so eventually I switched to 2010.
Bob Fabinski says
I am running Excel 2010 on Win7, and if I copy a sheet with an embedded chart, the chart is still pointing to the original sheet. This used not to happen to this same sheet in Excel 2007 with SP3. Is there something I am missing?
Jon Peltier says
Excel 2010, Windows 7: I copy the sheet with embedded chart (the chart links to data on its parent sheet), and the chart on the new sheet links to data on the new sheet.
Gyorgy F. says
I have the same problem as Bob F. with Excel 2010, but under WinXP, ie. when I copy a sheet with an embedded chart, the chart is still pointing to the original sheet. When I open the same file in Excel 2007 (under Win XP or Win 7) this problem does not occur, ie. if I copy a sheet, the embedded chart in the copied sheet links to data on the new sheet.
Earlier I had the same problem with Excel 2007 (see my comment above), at that time installing a Service pack solved the issue… Maybe installing the newast Sp for Office 2010 would solve the issue again, but unfortunately the administrator of that computer left for vacation for several weeks…
Jon Peltier says
I suspected it was service pack related, but I checked, and I have no Office 2010 service packs installed. It may be fixed by a service pack, but it may not be broken without it.
PG says
Thank you for providing both these explanations and the ChangeSeriesFormula Addin, this was exactly what I needed, and you made me save a lot af time.
Please keep doing such a good work :) !
bazza oxfam says
Better late than never!
I’ve been struggling with ways to copy sheets with embedded charts; it’s easy in Excel 2003 but the charts disappeared from the new sheet in Excel 2007. Just found your blog via ‘a well-known search engine’ as they say and soon came across the post from Steven M on 20 August 2009. Ticking the box in Advanced options ‘cut copy and paste etc…’ worked perfectly.
Only problem is that each user has to copy their own version of the sheet from their master file, and they are not all very happy with computers. But the onus is now on them to tick the box rather than on me to find a way of doing it.
Many thanks. I’m sure I’ll be using you again.
ckz says
Copying the sheet and chart may be the better plan…
I created VBA code that executes the formatting to how I want a chart to look. My hope was that when I opened another workbook, I could apply the macro to my new data set that would be in the same Cell locations. My problem comes when my new Chart and the Worksheet have different names. I thought a solution would a reference in the code to two cells. In Cell C1, a function that I created identifies the worksheet name. In Cell C2, the value would become the new Name of the selected chart. If I have these two values in each cell, can my code reference them as a Variable, Object, String, or Name, then in the code instead of an actual sheet name and an actual chart name use their “Variable” Name….this way the code is a bit more universal…? Any suggestions on setting the variables and making the chartname code to change upon a new name typed in C2?
Many thanks
Jon Peltier says
CKZ –
Could you just use ActiveChart and ActiveSheet? That simplifies a lot of issues.
Hangman says
Apologies, I had hoped this would be simple and I really didn’t want it to turn into a massive process. The
=IF(MOD(COLUMN(‘Source Data’!$D$5:$O$5),1)=0,’Source Data’!$D$5:$O$5,NA())
basically checks to see whether the current month has a zero value and if it does only plots values year to date to avoid the plotted line suddenly going from a value down to zero. Not sure if that makes any sense.
Hangman says
I think that may be the only workable solution to save the workbook first and then remove unwanted worksheets and then run my exisiting code to paste everything as values.
Rob Connell says
This does not work i.e. steps 1) and 2) above do not link the chart in the new sheet to the data in the new sheet. Excel 2016
Best just to rename the spreadsheet and then change the data.
Jon Peltier says
Rob –
Should work. Embedded chart or chart sheet?
Rob says
It is an embedded chart
I cannot select the sheet and the chart at the same time. Excel just flicks from the sheet (by itself) or the embedded chart (itself) but does not select them together, so I cannot copy them together.
This occurs even if I hold either of the shift or the ctrl key.
This is the step that is missing from your notes
Jon Peltier says
To copy the sheet, right click on the tab, select Move or Copy from the pop-up menu, check the Make a Copy box, and select the sheet in the list to place the copy before.
Alternatively, hold down the Ctrl key, click on the tab, and drag it to where you want the copy. When you let go, a copy of the sheet will appear.
Copying a sheet in this way copies the embedded chart along with the sheet.
Rob says
Thank you for showing how to copy both the sheet and graph. There was nothing on the microsoft web site so this is great.
Jon Peltier says
Rob –
Glad to help. I know everyday I learn something new is a good day.
Natasha says
YOU ARE A GENIUS!
I’ve been trying to figure this out for two days, all over the internet- and you had it right there. Working unbelievably perfect.
Thank you so much!
wordpress says
Great article.