Linking is an effective way to calculate data across multiple workbooks. However, establishing such links may affect your data. Modifying or deleting a linked workbook will drastically change the data on other workbooks. If you’ve landed on this article, you probably understand what we’re talking about.
You understand linking and want to get rid of it but wait, Break Link is doing nothing to de-establish the link! If you received a “We cannot update some of the links in your workbook right now” alert, your workbook isn’t completely de-linked.
This article will discuss why Excel doesn’t break your link and how you may completely break it.
Why is Break Link Not Working?
The traditional way of breaking links through Edit Links from theData ribbondoesn’t work for external links that include Data validation, Conditional formatting, Chart sources, and Defined names. You must manually look for and remove such external links on Microsoft Excel.
If your workbook is protected, Excel won’t break links apart from the specified links. Workbook protection restricts other users from editing certain parts and modifying the contents of the sheets. The solution varies depending on the reason you suspect you’re dealing with. This article will discuss the solution for each of these problems.
Break Link normally works for formulas you’ve used to connect workbooks. For more complex entities such as data validation, you’ll have to inspect and remove them from your sheet. There are multiple ways to look for them in Microsoft Excel, so follow the one that’s more convenient for you.
If the workbook is protected, you’ll have to use theUnprotect Sheetfeature on MS Excel. Remember that you’ll need access to the set password to unprotect your entire sheet. Ask for the owner of the sheet for the password and then follow these steps to unprotect it:
you may inspect your workbook to check its compatibility. The compatibility checker will list theissues in the workbook. You can additionally prompt it to locate and highlight the problem area in the workbook.
Follow these steps to inspect for problems in your workbook on MS Excel:
Clear Data Validation
you may clear the data source of your data validation from theDataon the menu bar of your workbook. If you suspect the data validation used in your sheet is refraining you from de-linking your workbook, you can refer to these methods to locate and remove data validation on your workbook.
Here are the steps to remove data validation on your workbook:
Remove Conditional Formatting
If your workbook contains conditional formatting that links it to other workbooks, you’ll have to manually remove it from theConditional Formattingoption on theFormulastab. Follow these steps to delete the link caused by Conditional Formatting on MS Excel:
