How-to Copy Charts and Change References to New Worksheet

Have you ever made a great chart for your Excel Dashboard and you wanted to reuse it for another set of data?  Perhaps you want to recreate the chart for a new year or another company or another division.  What an easy way to do this without having to change each and every series to the new data range?

Copy Chart New Cell Reference Intro

This is a very common need for most Excel users.  So what do most Excel Beginners do?  They copy the chart/graph and paste it in the current worksheet or perhaps on another worksheet.  However the chart retains the same chart range.  That means that if you change the data for the original chart, both charts will change and look exactly alike.  This doesn’t help us with what we want to do.  Since the new chart still references the original data range, we will have to change each chart series reference in order to reuse the same chart with a new data range.

Copy Chart New Cell Reference Copy Chart

 





The Breakdown

Let’s see how to do this to quickly recreate our Excel dashboard charts:

In order to copy the chart and make it reference another worksheet, we must make a copy the Worksheet from the Worksheet tab.  This will not work if you just copy the range of data including the chart.  It must be done by copying the entire worksheet.

HOW-TO

1) Right Click on the sheet where the original chart and data series are stored.



Worksheet Tab

 

Copy Chart New Cell Reference Beginning Chart

2) Click on Move or Copy…

Move or Copy Worksheet Right Click Menu





3) Check the “Create a copy” checkbox.  If you want to make a similar chart in another workbook, change the “To book:” to the other workbook that you want this Excel Dashboard Chart to be copied.

Right Click on Worksheet to Copy

4) Click on the OK button and see the Resulting Excel Chart with the new Excel reference range:

Copy Chart New Cell Reference Copy Sheet

 



Video Tutorial

Hopefully this will save you lots of time in making your Excel Dashboards.  Now you don’t have to change each and every chart series to a new data range.

Steve=True

 

UPDATE:

If you need to copy and paste an Excel Chart into the SAME worksheet and have it reference a new data range reference for the chart data.




16 COMMENTS

    • Hi C O Fritz, I finally got a version of MS Excel 2010 and tried it out. It still works for that version.

      You have to copy the entire sheet from the worksheet tab.

      Steve=True

    • That seems strange. Did you get it to work on a smaller graph? I don’t see why it wouldn’t work for both small and large. What happens when you try it and how large was your chart?

  1. Dears, I need a support in Excel. I have developed a line chart in excel for product A sales. I want to use the same chart for product B as well in the same sheet just below product A. I copy pasted the chart but source data is not changing. It is still connected to product A.
    Appreciate if someone can help me. It will save a lot of my time.

    • Chandra, there may be no easy way other than copying the chart and then adjusting the data references when you are going to be on the same data set/sheet.

    • Hi there. To do what you are asking, you first need to follow the instructions to copy it to a new sheet. Then CUT and Paste from the new sheet to the old sheet. One extra step.

  2. Hi, I would like to ask help. I did followed this procedure.
    But when I use scatter diagram, some of the formula in the data series was successfully linked to a new table, but other data is still directing from the old table. Can I ask help?

  3. Is it possible to copy the chart in other workbook and linked it with the source workbook? So, the changes are dynemic

    • Hi Suzil, yes you can do that, however, I do believe that the other workbook also needs to be open in order to update the values.

LEAVE A REPLY

Please enter your comment!
Please enter your name here