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?
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.
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.
2) Click on Move or Copy…
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.
4) Click on the OK button and see the Resulting Excel Chart with the new Excel reference range:
Video Tutorial
This embedded resource is unavailable in the restored edition.
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
Reader discussion
Comments preserved from the original page. New comments are closed.
Reader
September 10, 2014 At 12:42 am
The recommended solution does not work in MS Office 2010!
Reader
January 30, 2015 At 9:00 am
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
Reader
September 15, 2015 At 11:41 am
Thank you!!! You have saved me literally hours of my life recreating the same set of graphs dozens of times using different data.
Reader
September 21, 2015 At 6:46 pm
That is so awesome! So glad to help. Thanks for the great comment. Steve=True
Reader
April 25, 2016 At 4:48 am
This simple remedy does not work for large graphs and sheets in 2007.
Reader
April 25, 2016 At 11:09 am
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?
Reader
April 26, 2016 At 2:23 pm
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.
Reader
June 1, 2016 At 7:25 pm
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.
Reader
February 19, 2017 At 6:40 am
Hi Steve,
Thanks for your support.
but this solution doesnt work when you want to copy in same sheet, right?
regards
Reader
February 20, 2017 At 10:35 pm
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.
Reader
February 19, 2018 At 4:14 am
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?
Reader
November 30, 2018 At 10:48 am
Unfortunately, not much time available. I always recommend going to Excel Rescue
Reader
June 12, 2018 At 3:39 pm
Is it possible to copy the chart in other workbook and linked it with the source workbook? So, the changes are dynemic
Reader
June 13, 2018 At 9:54 am
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.
Reader
December 2, 2018 At 7:28 am
Works Great office 365 !! Advice
Reader
December 3, 2018 At 7:33 am
Thanks for the great comment!!
Reader
January 29, 2019 At 7:56 pm
Hi Steve,
Your suggested method for copying charts together with data by copying the whole worksheet is the one I used until last month with Excel 2010
Got a new computer now with Windows 10 and Excel 2016.
If I copy a worksheet with a chart now, it copies the data only, but not the chart.
I have to copy the chart separately to the new worksheet but the data references are still to the original worksheet and I have to manually edit the data ranges.
It drives me mad (I have to make lots of charts for a living), but looks like I’m the only one having this problem as I can’t find any comments on the web.
Appreciate your help, Joop
Reader
January 30, 2019 At 11:24 am
Hi Joop, i just tested it on my Windows 10 Excel 2016 and i do not have any issues copying the entire worksheet to a new sheet. Seems strange that your excel doesn’t copy charts and just data.
Reader
February 11, 2019 At 6:59 pm
I was having this same problem and was searching an answer. I found that if I go to Options, then Advanced, scroll down to “Cut, copy, and paste”. The third option that says “Cut, copy, and sort inserted objects with their parents cells”. Make sure there is a check mar in THIS box. Mine was not, but putting the check mark back, makes a chart copy w/ a Tab copy now. Woot woot !
Reader
February 12, 2019 At 8:18 am
Thanks for the great help Mike!!
Reader
August 10, 2019 At 11:55 am
This solution didn’t work for me in Excel 16.27 for Mac. Still references original worksheet in another file.
Reader
August 12, 2019 At 11:54 am
Thanks for the note. I don’t use a mac, so not sure of any work around.
Reader
February 3, 2020 At 11:43 am
Thank you!
Reader
February 14, 2020 At 3:02 pm
You are welcome. Thanks for your comment!
Reader
May 18, 2020 At 8:45 am
Copying an entire Excel 2013 worksheet containing multiple graphs will transfer 98% of the references correctly. I have a worksheet that contains five different graphs with up to five different plots per graph. If I copy the worksheet, all references in four of the graphs transfer correctly. However, all references to the x-data in each of the plots in one graph remain linked to the original worksheet. What is strange is that the y-data and titles DO transfer over to the new worksheet! I’m at a loss.
Reader
May 22, 2020 At 7:58 am
Hi Scott, that is very very strange. Can you try and remove 4 of the five different graphs and try and see what happens then? I would break it down to see if it works small and then ramp it up. Only other thing i can think of is that your x axis values are in a Named formula or in code. Let me know what you find out.
Reader
November 25, 2020 At 9:04 am
Hello. I’ve been copying sheets for two decades. After upgrading to Office 2019 the copy loses the chart. Please help. Thank you.
Reader
December 15, 2020 At 6:22 am
Hi Boris, on my copy of 2019, it works as described in the article. Are you perhaps on Office 365?