If you cannot break links in Excel® then follow these steps (backup your file first):
- Unprotect each sheet in your problem file: HOME RIBBON – (CELLS) FORMAT – PROTECT SHEETS
- Break links: DATA RIBBON – (CONNECTIONS) EDIT LINKS – Select sheet then BREAK LINK
- Delete all named ranges to external files: FORMULA RIBBON – (DEFINED NAMES) NAME MANAGER
- Check all chart series ranges: Right click chart – SELECT DATA – (SERIES) EDIT. Check each series, if any ranges are in external files then cut the range from the external file and paste in to problem file. Also check chart titles and data label ranges.
- Check Conditional Formats and Data Validation for similar phantom links.
- Save a copy of the problem file then:
- Rename it in file explorer: changing the extension from .xlsx to .zip.
- Navigate to the folder ‘FILENAME.zip>xl’ file and delete the folder named ‘externalLinks’.
- Rename the ZIP file from extension .zip to .xlsx
- Save the file as file type ‘xls’ then back to ‘xlsx’ (or whatever the original file type was). Create a backup before trying this.
Some of these problems can be fixed with our add-in: fix and speed up Excel® files tools ADD-IN.
Lets go through each of those steps in more detail to make sure you can break links.
1. Unprotect Sheets in your Problem File
a) When the active sheet is protected and you try to edit links the BREAK LINK button will be grayed out. You will need to unprotect this sheet or go to another sheet before you try and break links.
b) If a cell within a protected sheet is linked to an external file then you won’t be able to break links. Excel® will give you a warning that the external link cannot be broken due to the sheet being protected.
Excel® won’t be helpful enough to tell you which sheet contains the external link so you may need to go searching for it. You can use our audit tools to find all cells with external links.
If you are using Excel® 2010 or later you can go to FILE – INFO. Under PROTECT WORKBOOK at the top you will see a list of any protected sheets.
Once all the sheet are unprotected you can go on the next step.
2. Attempt to Break Links
This step will succeed in breaking links unless there are “phantom links” with the workbook. We will come to these later.
The Break Link function replaces the external links in any formulas with constant values.
If you want to identify all cells with references to external workbooks before you carry out this process you can do so:
- Press CTRL+F
- Type “xl*]” in the ‘Find what:’ box (don’t include the inverted commas in the box)
- Select ‘Within:’ Workbook
- Select ‘Look in:’ Formulas
- Click ‘Find All’
You can now check the list of links before you proceed.
To break links click DATA RIBBON – (CONNECTIONS sub menu) EDIT LINKS
You will see a list of external links that your current file is linking to. You can click each of the files and then click BREAK LINK.
If BREAK LINK is grayed out then go back to step 1 as you must still have a protected sheet in your workbook.
Hopefully you have now broken links successfully. However, if you still cannot break the Excel® link then you need to proceed to the next step.
3. Delete Named Ranges to External Files
This is the most common type of phantom link. It is possible that named ranges used by a file are defined as a range of cells in an external workbook.
You can check these easily and delete any that refer to external files.
Click on FORMULA RIBBON – (DEFINED NAMES) NAME MANAGER
Select each named range that refers to an external workbook and click DELETE. If there are many named ranges then you may want to sort them by ‘Refers To’. This will group links to the same external file.
You can delete more than one named range at a time. Select one, hold shift then select the one at the end of the range you want to delete.
Note: you could use the file fix and speed up tools to delete named ranges to external files. With the tool you can choose to omit open files when deleting links to external files.
Go back to check EDIT LINKS to see if this has resolved your phantom link problem. If you still cannot break link then go on the next step.
4. Delete Chart External Links
It is possible that some of the series data used by an Excel® chart has been moved to an external file. This will create a phantom link.
Check if your file has any charts. It is possible that your workbook has hidden sheets so you may need to unhide them and check these for charts as well.
If FILE-INFO is saying there are still hidden sheets then your file may have sheets that are ‘Very Hidden’. This is unlikely unless you or someone else has set the sheet ‘Visible’ property to xlVeryHidden in the Visual Basic Editor. Click FILE – CHECK FOR ISSUES – INSPECT DOCUMENT to find any hidden sheets. You can either click to remove the sheets. Or you can unhide them in the Visual Basic Editor (click ALT+F11). In the VBE you can click on the hidden sheet then change its visible property to xlVisible.
In each chart. Right click the chart then ‘SELECT DATA’. Click on each series then ‘EDIT’.
If you see a link to an external file in the name or values then you have found some phantom links. Go to this data in the external file, cut it and paste it in your problem workbook next to the chart.
If you’ve already tried to BREAK LINKS to this file then instead of the file and range (above), all you will is a list of values (below).
If this is the case then you’ll still need to cut the cells from the external file and paste in your problem workbook. The difficulty is going to be finding the cells. You don’t have a range reference in this case.
Also check the chart titles and other chart elements for external links such as this:
Other Non-Chart Objects
Other objects such as form controls or linked images may reference external files. You can check these as well before you go to the next step.
4a. Delete any external links in Conditional Formats.
UPDATE: Some links to external files can be hidden in any Excel feature that can reference sheet ranges. These features include Conditional Formats. (Thanks to Roger, Yuri and others in the comments for these additions)
Go through each sheet and select every cell. Click CTRL+A
From {Home} menu. In the {Styles} section click on {Conditional Formatting} then {Manage Rules…}.
For each of those rules: click on it then {Edit Rule…}. You’ll need to check there are no references to external files in there. If there are then then amend or delete the rule. You can learn more about conditional formatting here if needed.
You can follow a similar process for Data Validation if you still have external links. To find data validation in a sheet you need to go to the {Home} menu then within the {Editing} section click on {Find & Select} then {Go to Special}. Now select {Data Validation} then click {OK}.
If you still cannot break links then try this:
5. Save a copy of the problem file.
This is the last resort if you cannot break links in Excel®. But this will resolve any remaining phantom link problems.
The downside is that this method is breaking external links without actually finding them. There is a chance this will impact the functionality of your workbook.
This method goes in to the file structure within the workbook to remove the link data.
- Create a copy of the file in which you are trying to remove the links
- In file explorer: right click this new file and RENAME
- Change the extension from .xlsx to .zip.
- Open this ZIP folder and navigate to the folder ‘FILENAME.zip > xl’
- Delete the folder named ‘externalLinks’.
- Back out of the ZIP folder
- Rename the ZIP folder from extension .zip back to .xlsx
- Open this file
When the file opens in Excel® you should not be asked to update links. The EDIT LINKS option should be grayed out.
Compare this new file with the original to ensure it is working correctly.
6. Change the file type to ‘xls’ then back to ‘xlsx’.
Update: this is added courtesy of the comment from Jack below. Thanks Jack.
This is the “last last” resort. Downgrade the file.
- Create a backup of your file.
- ‘Save As’ your workbook.
- Change the file type to ‘xls’ (Microsoft Excel 5.0/95 Workbook’
- Click ‘Save’
- ‘Save As’ again.
- Change the file type to ‘xlsx’
- Click ‘Save’
That may well fix any remaining problems.
There can be a downside to this method. Saving a file as ‘xls’ that was created as ‘xlsx’ may result in some features being removed from the file. You will receive a warning if there are any features that are incompatible with the ‘xls’ format so check that first. If there are any issues then your backup should bail you out.
Leave a Comment