How to Break Links in Excel

You might have come across a situation in which you maintained an external link to another Excel Workbook and somehow that Workbook gets deleted or replaced or that information is no longer required to be updated. Then every time you open the Workbook it shows the Broken Link Error.

You might be thinking how to fix the same?

So, in that case, you need to break the links in Excel in your original file.

Breaking existing links in Excel 2016 can be achieved by selecting Data Tab on the Toolbar and then clicking on Edit Links (highlighted) in Connections group. Refer below image


Once you will click on Edit Link Excel will show up all the external references used in the Workbook.

You can select any of the source and click on Break Link. If you need to select all the links at once press Ctrl + A else hold the Ctrl Key and select multiple links as required.


The Edit Links button will appear disabled if there are no external references present in your Excel Workbook.

If you have defined a name for the link in your Workbook, then on breaking the link the defined name will not get removed automatically. You need to remove it separately by Name Manager button present under Formula tab – defined names section.

Click on the Name Manager will open the dialog box as shown in image below with all the Links defined with a Name. You need to select the entry and click on Delete button (highlighted)


Let us now understand this process by referring to an example of Worksheet Links. This will be useful for those who are new users to Excel and still in the learning phase.

Consider a case of an IT Company where a Manager needs to update Total Project Value by referring Project Value Realization Sheet. The data of Project Value Realization Sheet is linked with Cost Savings Sheet. Whenever any employee updates the Projected Cost in Savings Sheet it reflects in Project Realization Sheet.

Refer below image of a folder containing specified files


Project Realization Sheet contains month wise breakup of the savings figure along with total savings achieved. Note – The External Reference link (highlighted) in image below to the other workbook named – Cost Savings Sheet in the Cell D6 and similarly for Cells D7 and D8 respectively.


Cost Savings Sheet contains Project wise monthly saving data which is being updated by different employees. It also contains the Total savings for all the months in Cell F6 which is an external Reference to Cell D10 of Project Value Realization Sheet.


Both the files are at a specific location inside a folder. Suppose if the Cost Savings file gets moved to other location or deleted. You will get below message while opening the Project Realization Sheet.


Either you can continue or try editing the links. Once you click on Edit Links you will observe below screen showing Error: Source Not found


Now to remove this error message completely from appearing every time you open the Workbook you need to Break Link. Observe the button of Break Link in above screen. Once you will click on Break Link, system will give you a warning that any external references will be permanently converted to their existing values. This means that the data will no more get updated once you change the referenced sheet.


On clicking Break Links, Excel will remove the selected reference from Workbook. Observe the Cell D6 contains no external reference now. Refer below image


In this way, you may Break as many links present in a Workbook by simply selecting the links or selecting all the links at once.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

You might have come across a situation in which you maintained an external link to another Excel Workbook and somehow that Workbook gets deleted or replaced or that information is no longer required to be updated. Then every time you open the Workbook it shows the Broken Link Error.

You might be thinking how to fix the same?

So, in that case, you need to break the links in Excel in your original file.

Breaking existing links in Excel 2016 can be achieved by selecting Data Tab on the Toolbar and then clicking on Edit Links (highlighted) in Connections group. Refer below image


Once you will click on Edit Link Excel will show up all the external references used in the Workbook.

You can select any of the source and click on Break Link. If you need to select all the links at once press Ctrl + A else hold the Ctrl Key and select multiple links as required.


The Edit Links button will appear disabled if there are no external references present in yours Excel Workbook.

If you have defined a name for the link in your Workbook, then on Breaking the Link the defined name will not get removed automatically. You need to remove it separately by Name Manager button present under Formula tab – defined Names Section.

Click on the Name Manager will open the dialog box as shown in an image below with all the Links defined with a Name. You need to select the entry and click on Delete button (highlighted)


Let us now understand this process by referring to an example of Worksheet Links. This will be useful for those who are new users to Excel and still in the learning phase.

Consider a case of an IT Company where a Manager needs to update Total Project Value by referring Project Value Realization Sheet. The data of Project Value Realization Sheet is linked with Cost Savings Sheet. Whenever any employee updates the projected Cost in Savings Sheet it reflects in Project Realization Sheet.

Refer below image of a folder containing specified files


Project Realization Sheet contains month wise breakup of the savings figure along with total savings achieved. Note – The External Reference link (highlighted) in an image below to the other Workbook named – Cost Savings Sheet in the Cell D6 and similarly for Cells D7 and D8 respectively.


Cost Savings sheet contains Project wise monthly saving data which is being updated by different employees. It also contains the Total Savings for all the months in Cell F6 which is an external Reference to Cell D10 of Project Value Realization Sheet.


Both the files are at a specific location inside a folder. Suppose if the Cost Savings file gets moved to other location or deleted. You will get below message while opening the Project Realization Sheet.


Either you can continue or try editing the links. Once you click on Edit Links you will observe below screen showing Error: Source Not found


Now to remove this error message completely from appearing every time you open the Workbook you need to Break Link. Observe the button of Break Link in above screen. Once you will click on Break Link system will give you a warning that any external references will be permanently converted to their existing values. This means that the data will no more get updated once you change the referenced sheet.


On clicking Break Links Excel will remove the selected reference from Workbook. Observe the Cell D6 contains no external reference now. Refer below image


In this way, you may Break as many links present in a Workbook by simply selecting the links or selecting all the links at once.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

Tags: No tags

Add a Comment

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