How to copy Excel Chart without links

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.

excel-update-links.gif

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

copy-excel-chart

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 TipDelink 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?

edit-links-excel

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.

break-links-excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>