0

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.

  • 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. – Rey Juna Aug 22 '18 at 16:08
  • @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! – Dan Aug 22 '18 at 17:04
  • 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. – Rey Juna Aug 22 '18 at 20:32
  • @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 – Rajesh S Aug 23 '18 at 9:18
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.