Replacing links with values in Excel is a very useful tool. On many occasions before sharing our workbooks with external parties we prefer to replace the links with values.
Links can be tricky – links to other workbooks can be in formulas, names, charts, text boxes, and other objects, both visible and hidden. Getting all the links and breaking them depends on the complexity of our workbook. We must be very careful if we have a complex workbook and want to replace the links with values before sharing it.
In this post we will be exploring how replacing links with values in Excel works.
The most basic approach for replacing links with values in Excel is Paste Special tool. Suppose I have a sales projection workbook which I want to share with someone, although I don’t want to expose my calculations and links.
I created a new workbook and linked the information to the original workbook. I can have tons of information in the original workbook but I just want to share sales projection.
You can see the values are linked to another workbook. The simplest solution for replacing links with values in Excel is copying the data range and then use Paste Special > Values.
We can also do by right clicking and then using the shortcuts.
If you have lots of links at different places of the workbook, then copying and pasting values will take time and chances are that we may miss some of the links.
Edit Link took of Excel can be very useful in such situations.
The Edit Link can be found in the Data Tab of the Ribbon.
Once you click on the Edit Link, the Edit Link dialogue box will appear with various options. It will also lists the external sources of the links.
You can click on Break Link to replace links with values. You should note that the values last retrieved through the links will remain in the workbook.
Edit link tool will work with links in formulas, names, charts, text boxes, and other objects.
Hope you enjoyed this post on replacing links with values in Excel. If you have any questions, let me know through the comment section below.