This is a quick post to answer this rather common question from the forums:
I have a worksheet that has data in a particular arrangement and a chart that plots this data. I have another worksheet with the identical data arrangement. How can I copy the chart to the new worksheet and have the chart link to the data on the new worksheet?
Here is my simulation of the problem. Worksheet “Original Data” has a particular data arrangement with a “nice” chart that plots this data.
If I click on a series in the chart and peek at the formula bar, I can see that it plots data from “Original Sheet”. Here are the two series formulas:
=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)Great, just what I expect.
I went to a lot of trouble drawing this chart exactly to the boss’ specifications, and I don’t want to spend another hour and a half duplicating the chart for the data on the worksheet named “New Data”. So I copy the chart, and paste it on “New Data”. But that looks like it plots the numbers from “Original Data”.
I can verify this behavior by checking the series formulas in the chart:
=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)Most people don’t expect this behavior (though it does make sense, given the history and nature of charts).
In this simple case it wouldn’t take too long to actually edit the series formulas to make the new chart match the new data. But in real life, the chart contains 37 series, and you need to put the chart onto 47 different worksheets. (It’s not too hard to write some VBA to help you edit series formulas, but that’s not today’s lesson.)
So I’m going to show you an easier way to make sure the chart links to the data on the new sheet.
The original chart links to the data on the original sheet, right? If we make a copy of that original sheet, call it “Original Data (2)”, we can verify that the chart on this copied sheet links to the data on the copied sheet. See, series formulas:
=SERIES('Original Data (2)'!$C$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$C$3:$C$8,1)
=SERIES('Original Data (2)'!$D$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$D$3:$D$8,2)I didn’t even have to touch the chart. Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter.
Look, the chart now is showing the data we just pasted on top of the copied data:
All we need to do is remove the current “New Data” sheet, and rename this copied sheet to “New Data”.
It’s a small amount of work, but isn’t copying and pasting data a lot easier than futzing with a chart’s series formulas and source data?
Chandeep says
Jon.. Nice little trick there.
Quick question:
1. I could have made a default chart with the new data using alt f1
2. Then I could have just used paste special formats for charts for all the formatting Jazz, if any?
3. F4 key to repeat that to all charts
Wouldn’t that work as well?
Thanks
Mats Lind says
Thanks a lot for a great post. I really found this useful and I think the approach would help in many other similar kinds of situations where you want to reuse old analysis and presentations on new data! /Mats
Jon Peltier says
Chandeep –
Depending on the complexity of the charts, Paste Special Formats may not handle all of the custom formats. In fact, I just did a quick test with the charts in this post, and after Paste Special Formats, the chart area has a black border, not a light gray border. Experience shows also that more complicated combination chart types are not replicated reliably. Also, such attributes as position and size are not handled by Paste Special Formats.
If there are multiple charts on the original sheet, it is likely that each has its own customizations, so F4 isn’t the answer for multiple charts.
Matt says
that is exactly the thing that I was looking for!
Tony Phan says
I really need helps to make 8 identical sheets with identical format but different data. There are 3 graphs on each sheet and 18 curves in each graph. I copied, pasted, renamed the sheets as your instructions but 8 sheets got the latest update from the “Original Data (2)”. I tried to add sheets on the same excel file and a new file but got no luck. Thanks in advance!
Jon Peltier says
If the original charts plot data on the original sheet they are located on, then when the original sheet is copied, the charts on the copied sheet plot the data on the copied sheet. So you need to put new data into the cells of the copied sheet. If all charts point to the same other sheet, you haven’t followed instructions.
Richard Olin says
I was excited to discover this technique, However, per Tony Phan above, I cannot get it to work. Original data keeps appearing.
Jon Peltier says
Sheet1 has original data and chart.
Sheet2 has new data in same layout as in Sheet1 but no chart.
Copy Sheet1 to create Sheet1 (2) which has original data and a chart which plots this original data which is on Sheet1 (2).
Copy data from Sheet2 and paste over data on Sheet1 (2). Chart on Sheet1 (2) should now be showing the pasted data from Sheet2, which is now on Sheet1 (2).
Delete Sheet2 (maybe save a copy first, just in case) and rename Sheet1 (2) to Sheet2.
Richard Olin says
Tried again but cannot get series reference to be different from original.
using Office Pro 2016
Richard Olin says
Well, today the data copying worked flawlessly….. best not to do this while tired!!!
Many thanks
Jon Peltier says
Well, glad it’s working now.
Fat Bob says
All- one comment. For windows 10, Office 365, If this technique is not working, look at “Properties follow chart data point for all new work books” and “Properties Follow chart data point for current workbook”. These must NOT be checked. If they are checked, copying the sheet will only transfer sheet data but not charts.
Go to: “File”==> “Options”==> “Advanced”==>”Chart”; there are four chart options; the ones to uncheck are at the lower half of “Chart” options.
Hope it helps someone out there who is wondering why it is not working as discussed.
Jon Peltier says
Hi Bob –
That’s not true. Whether those two boxes are checked or not, copying the sheet will copy the sheet’s data and its charts.
I wrote an article, Properties Follow Chart Data Point. Reading it, you will learn how checking the boxes will change the way custom formatting in charts is preserved when the data is adjusted.
I assume you’re copying the sheet, not the contents of the sheet. Right click on the sheet tab and choose “Move or Copy”, or simply hold Ctrl while you drag the sheet tab to the new sheet’s location.
If you copy a sheet’s cells by selecting a range (or click the triangle where the row and column headings meet), then you will also copy the data and charts in the copied range. However, these pasted charts link to the original copied data, not to the pasted data, which was the point of this article.