Sometimes you may require to copy chart from one excel book to another. You right click on chart, copy and paste it on another workbook. And the chart appears to be pretty well copied. But what about the data necessary for creating this chart?
After you save the excel file with copied chart and move it to some other folder, the problem starts when you try to open it again. If you are using Excel 2003 or earlier version, you get the following message.
Now, suppose we want to change a few data in a series. Right click on chart, go to the data source and try to edit any series. What happens? You get an error – “Reference is not valid. Reference must be to an open worksheet”.
When you copy the chart from one excel workbook to another, the chart takes reference data from the original workbook. To make any changes, your original file must be available at its original location. At the same time, it must be open also so that you can make required changes to the data source.
What if you wish to copy only chart without any reference and links to original file? Sometimes back, I was facing the same problem. When I searched for the solution, I found that most of the people advising to copy the source data in another worksheet along with the chart. After that, make changes to the data source of the chart. Though it works, it is not the efficient solution and is time consuming. One another solution is to paste the chart as an image. It may be a good option if you wish to take just print-outs.
Thankfully, I could get the solution from one of my colleagues. So, this is the Excel Tip – Delink Excel Chart Data!
De-link Excel Chart Data
You can’t do much at the time of copying the excel chart. But after copy, you can delink your chart data from original file. How?
In the Data Ribbon, go to connections sub-group and click on “Edit Links”. In the ‘edit links’ pop-up window, select the source listed as the original excel file and click on ‘Break Link’. You will be asked for the confirmation. Confirm by clicking on ‘Break Links’ again.
That’s it. Your chart is now free from all the references and links. Try to edit any series now. All the references have been converted to arrays of data. It has absolutely no relation with original file; you can move this file wherever you wish.
This simple tip can save you from the hassles of copying the sheet with chart data along with chart. At the same time you can make changes to the chart data as per your requirements.