Copying Chart to Another Sheet with Updated References in Excel

Question

I have made a chart within a sheet in Excel. Within the sheet, the chart refers to specific cells in the sheet to display (i.e. cells that contain my data points).

I would like to know how to copy the chart into another sheet within my Excel file, and have the references update to refer to the cell addresses that are within the its new sheet.

Whenever you copy-paste/move a chart into another sheet, the chart will still refer to cells within the original sheet it was made in. This is because anytime you refer to a cell within a chart, it specifically refers to the sheet it was made it (i.e. Instead of saying $T$7:$T$12, references are made to be SHEET'!$T$7:$T$12).

In the references within the chart, if I change the reference from SHEET'!$T$7:$T$12 to $T$7:$T$12 (or even T7:T12), I get a message saying that my formula contains an error.

How can I solve this issue?

Thanks.

Solution

The easiest way to handle this is to copy the entire worksheet with data and chart, then paste the new data into the copied worksheet. It's a simple enough protocol, if time-consuming, but there's more description in my tutorial at Copy Chart to New Sheet and Link to Data on New Sheet.

An alternative is to copy the chart to the new sheet use a VBA procedure to modify the series formulas in the copied chart, to replace the old sheet's name with the new sheet's name. The VBA is basically this:

Sub FindReplaceSeriesFormula(myChart As Chart, OldText As String, NewText As String)
  Dim srs As Series

  For Each srs In myChart.SeriesCollection
    srs.Formula = Replace(srs.Formula, OldText, NewText)
  Next
End Sub

and you would call it like this:

Sub FixActiveChart()
  FindReplaceSeriesFormula ActiveChart, "Sheet1", "Sheet2"
End Sub

I discuss this approach and provide some more bulletproof code in my tutorial Change Series Formula – Improved Routines

Comments

[Rey Juna]

Is there a reason you can't copy the whole sheet with your data and chart? I often do that then modify the data in the copied sheet to be what I want.

[Dan]

@ReyJuna Thank you! That should help me in the meantime. I was just curious as to know whether copying a chart, and updating references with it was a function somewhere within Excel, since it could be more efficient, especially when working with many sheets. Anyways - thanks for the help!

[Rey Juna]

I've been working with Excel for over 20 years and have not come across that function. In the past I've thought it might be interesting to write VBA to do it, but I've never had the chance to tackle it.

[Rajesh Sinha]

@Dan, you need to create Dynamic Range where data in both sheets has similar dimension and Sheet name should be read from a Cell as variable,, Or better refer this,, peltiertech.com/change-series-formula-improved-routines ☺

[Blaisem]

When I copy the entire worksheet (via the arrow icon in the top left, i.e., above 1 and to the left of A), the chart retains its data references to the old worksheet. Does this mean the only method to update the data references to the new sheet is with the macro, or am I copying the worksheet incorrectly?

[Jon Peltier]

To copy a sheet, right click on the sheet tab at the bottom of the window and use 'Move or Copy'. Or simply hold down Ctrl while you drag the sheet tab to the location of the new sheet. No code required.