Links, i.e., external references, are used in order to retrieve data from an external source, usually another workbook. Only retrieved values and addresses for those values will be saved in the current workbook. Addresses are saved in order to enable updates, i.e., when the source changes, or the source is changed, so does the current workbook.
However, if we want to disable future updates to the current workbook, or if we want to forward our workbook to an external user with no access to the linked files, we need to break those links.
This is done via the Edit Links feature, which lists all linked workbooks and, among other useful features, also contains the Brake Links option:
Sometimes, you will want to, but not be able to, break those links.
This is happening due to two different groups of reasons:
First, due to protected worksheets. Links in protected worksheets can’t be broken, and those worksheets need to be unprotected first.
Second, references to external workbooks are stored not just in cells, but also in various other objects and Excel features. Due to those references, phantom links will still be shown as active even when addresses stored in cells are removed with Break Links.
In order to completely remove those phantom links, first they have to be removed from the mentioned objects and Excel features (listed in the order of causing issues with link breaking):
- Name Manager
- Conditional Formatting
- Data Validation
In theory, this should not be necessary. Excel does contain checks that should prevent this from happening. For example, you can’t reference another workbook in Conditional Formatting directly. What usually happens then is that these kinds of references are inserted when whole sheets (objects) are moved or copied into a workbook, and/or an INDIRECT function is used in combination with those actions.
However, there are also some objects which will not cause issues or will behave in other ways.
Text Box moved into a workbook can contain links to other files. Those links are visible in the Edit Links window and can be broken.
Charts moved into workbook can contain links to other files. Those links are visible in the Edit Links window and can be broken. Sometimes they will still appear as active, but when the workbook is reopened, they will no longer appear as active.
Pivot Tables can contain references to other workbooks that are not visible at all in the Edit Links window. If those references need to be removed, Pivot Tables need to be edited manually.
Linking in Excel is a rather complex topic. Also note that we are discussing unintended Excel behavior in this article, and as Excel is in continuous development, there may be differences in behavior from version to version.
In order to remove phantom links from your workbook, follow the following steps:
Unprotect any protected sheet:
You will also want to unprotect any hidden or really hidden worksheet.
Delete the necessary Names in the Name Manager:
Above is a typical picture of the Name Manager from the file circulating in the wild. The file contains a great number of broken names that are not only causing issues with breaking links, but also affecting file size and calculation times. Typically, you can delete all Names, but beware of the loss of functionality if some are still used for some purpose.
Delete all of the unneeded Conditional Formatting rules:
You can see all of the rules available in the active sheet by selecting the “This Worksheet” option. Using Conditional Formatting Rules Manager, you can only delete one Conditional Formatting rule at a time this way, and you will have to repeat the process on every sheet.
As with Names, you can typically delete all of the Conditional Formatting rules. This can be done faster with the Clear Rules, Clear Rules from Entire Sheet option (above Manage Rules), but beware of the loss of functionality if some are still in active use. File size and performance comment also applies.
Remove all of the unnecessary Data Validation rules:
Typically, you can remove most of the Data Validation by selecting “Apply these changes to all other cells with the same settings”. However, this will be an extremely tedious process if your workbook contains many problematic data validations, as you will have to manually locate every one of them.
As with Names and Conditional Formatting, you can typically delete all of the Data Validation Rules, but beware of the loss of functionality if some are still in active use. File size and performance comment also applies.
Break your links, save your file, and reopen it.
Workbook with no active or phantom links will have the Edit Links option grayed out both in the Data ribbon and on the Quick Access Toolbar:
If you can still see phantom links, you can save your file as an .xls file or as an .ods file (Open Document Spreadsheet), reopen it and save it again as an .xlsx file.
You can also open your file in LibreOffice Calc and save it before reopening it in Excel.
However, note that those are desperate actions barely above manual editing of the file, and the loss of functionality is almost guaranteed.
See also, how to remove leftover formatting that causes excessively large files in Excel.
One thought on “Unbreakable links in Excel”