Referencing your workbook with other documents can be quite resourceful in many cases. However, when you establish a link between two workbooks, any change you make in the source workbook is copied into the destination workbook. If you’ve already retrieved the values you wanted, it is the best to the links.
Unfortunately, breaking links don’t always work. This can be due to a number of reasons, including protected workbooks, external links embedded in data validation, and conditional formatting. In this article, we have covered all possible cases that could trigger Excel to not break links, so keep reading!
Inspect the Problem
Before we head to the fixes, let’s inspect the problem a bit. Normally, you can break links through the Edit Links option in the Data ribbon.
In some cases, you may see that the Edit Link option is grayed out. This is because Excel cannot find any external links in the workbook. There are two types of linking in Excel, external linking and hyperlinks. If you’ve entered hyperlinks, the break links option will not be available for you.
In other cases, you could access the Edit Link option, but you encounter the “We cannot update some of the links in your workbook right now.” You mostly have links embedded for data validation, conditional formatting, Excel charts, or name manager. Lastly, you cannot break links if you’re dealing with broken links.
Check Workbook for Issues
You can inspect for problems using the compatibility checker in Excel. The tool will give you a summary of the potential issues in the workbook. If your worksheet has broken links, the compatibility checker will help you locate these issues through the Find, Fix, and Help options. You can also use the compatibility checker to locate cells with data validation that contain links.
- From the menu bar, go to File.
- Select Info from the sidebar.
- Click on the option next to Inspect Workbook.
- From the fly-out, choose Check Compatibility.
- In the window, look for issues, and select Find.
- Manually remove the links from your worksheet.
Unprotect Worksheet
Excel doesn’t let you create changes in a protected worksheet. It protects all locked cells in the worksheet, that may contain the external link. Some worksheets are password protected, so make sure you have this access before you move ahead with the steps to unprotect your worksheet.
- Head to the Review tab from the menu bar.
- From the Protect section, click on Unprotect Sheet.
- If you are prompted to enter a password, type in the password and click OK.
Remove Data Validation
If you’ve inserted external links while creating data validation, Excel will fail to break such links. If you’re unsure which cell contains Data Validation, you can use the Find and Select tool to locate such cells. Once you’ve located these cells, you can follow these steps to clear Data Validation:
- Select your cells and move to Data from the menu bar.
- Locate and click on Data Validation from the Data Tools section.
- On the Settings tab of the Data Validation window, clear contents under Source.
- Select OK.
Erase Conditional Formatting
Excel also does not break the external links embedded in conditional formatting. Therefore, you will have to manually erase the source by removing the conditional formatting rule.
- Select the range with conditional formatting.
- Hop on to the Home tab in the menu bar.
- From the Style section, click on Conditional Formatting > Clear Rules.
- Select Clear Rules from Entire Sheet.
Remove Links from Name Manager
If you cannot remove links from the Edit Links option, you can erase the connection from Name Manager in Excel. The Name Manager has a name for every range in Excel, including the range extracted from an external source. If you delete the range from the list, the connection is essentially cut off.
- Go to Formulas.
- Select Name Manager from the Defined Names section.
- In the window, check the information under Refers To. If the source is external, select it.
- Click the Delete button to remove the linking.
