How to copy chart from one excel to other excel file without links

Have you tried to copy a chart from one excel file to another file? What’s a big deal here – just right click on chart, copy and paste wherever you want. Isn't this simple?
Good! But now save the excel file where you have pasted the chart and close it. Move it to some other folder and 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!

Delink 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.

No comments:

Post a Comment