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?