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.

Replacing Links with Values in Excel

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.

Replacing Links with Values in Excel

We can also do by right clicking and then using the shortcuts.

Replacing Links with Values in Excel

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.

Replacing Links with Values in Excel

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.

Replacing Links with Values in Excel

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.

Replacing Links with Values in Excel

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.

Download Excelworkbook

WAIT
I have a FREE gift for you

Do you want FREE Beginners' Guide to Real Estate Financial Terms?

Download the ebook now

Beginners' Guide to Real Estate Financial Terms