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.
