Removing Phantom Excel Links

If you have ever opened an Excel file and been prompted to update links to another file, yet you are certain that there are no links to any other files, your file may contain phantom links.

To remove Phantom Links from a workbook, first check to make sure that there are not any non-phantom links in the file.

How to check for non-phantom links

Check for Real Links in your formulas

1. Go to the Edit menu and select the Links option towards the bottom (if this option is grayed out, then there are no real formula links)
2. Run a search (Ctrl + F) for the file that appears in the Edit Links menu
3. Search all tabs simultaneously by pressing Shift + Ctrl + Page Down and then running the search
4. You can use the * wildcard character in your search
5. If you find a real link, change the file reference or delete the link

Check for Defined Name Links

1. Go to the Insert Name Define menu
2. Select each Name to see what is in the “Refers To” box
3. If any of these names is referring to cells in another file, change or delete the Name

Check for Chart Links

1. If there are any charts in the file, the charts may be linked to cells in another Excel file
2. Check the data ranges and x-axis labels for each chart to ensure that they are not referencing data in another file
3. If any of the charts is referring to cells in another file, change or delete the data references

Check for Object Links

1. Links can exist in Object such as text boxes, auto shapes or drawing objects
2. On each worksheet, press the F5 key, click the “Special” button and select “Objects”
3. Delete these objects and then save the file
4. Close and reopen the file to see if this has solved the problem (you may want to make a copy of your file before performing this step)

Check for Data Validation Links

1. Links can exist as part of Data Validation criteria
2. The easiest test is to remove all Validation and see if the link remains (you may want to make a copy of your file before performing this step)
3. Copy any blank, unformatted cell
4. Select all cells with Validation; press the F5 key, click the “Special” button and select “Data validation”
5. Select Paste Special and then select Validation
6. Save, close and reopen the file to see if this has solved the problem
7. Repeat steps above for the remaining worksheets in the file

Check for Conditional Formatting Links

1. Go to the Conditional Formatting Rules Manager
2. Select “This Worksheet” from the drop down
3. Check each Rule to ensure they are referencing data in an external file
4. Amend or Delete the Rule to remove the Link
5. Repeat steps above for the remaining worksheets in the file

Check for Links on Hidden Worksheets

1. If you or anyone else has hidden any worksheets in the file, there may be links on these hidden sheets
2. Check for hidden worksheets by going to the Format Sheet Unhide menu
3. If this option is greyed out, the file probably doesn’t contain any hidden sheets; however, be sure to also check for Very Hidden Sheets with the Visual Basic Editor
4. If there are hidden worksheets, unhide them and complete all of the steps above on the sheets that were hidden

If you perform the steps on the previous page and there are no Non-Phantom links, then the workbook probably contains Phantom Links.

Remove phantom links

Refer the Link to itself

Go to the Edit Links menu and select the unwanted link
Choose the “Change Source” button and refer the link to itself
This doesn’t usually work as Excel will probably tell you that there is an error in a formula, but it’s worth a try

Create a Real Link between the two files

1. If the previous step doesn’t work, open the file containing the Phantom Link AND the file that is being referred to (if you can’t open the file that is being referred to, you will need to skip this step)
2. Create a real link between the problem file and the file that is referred to
3. Save both files
4. Go to the file with the Phantom Links, select Edit Links, click the “Change Source” button and try to refer all the links to the file in which you just created the real link
6. Save the files again and then delete the real link that you just created
7. Save the file with the Phantom Links one last time
8. This should remove the Phantom Links, but if it doesn’t you will need to follow the last step below

Delete Sections of the file with the Phantom Links

1. The following step requires that you delete sections of the file with Phantom links, so you should create a copy of the file with Phantom links before proceeding with this step
2. Open the copy of the file with Phantom Links and delete the first tab
3. Save and then reopen the file
4. If you are not prompted to update links, then you have found the tab containing the phantom link
5. If you are still prompted to update links when you reopen the file, repeat the step above for each tab (you will need to add a new blank worksheet before you can delete the final tab in the file)
6. Once you identify which tab contains the Phantom links, return to the original file with the Phantom links and create another copy of the file
7. Open this new copy of the file and go to the worksheet that contains the Phantom Links (as identified in the step above)
8. Select a section of approximately 100 cells and then select Edit Clear All
9. Save the file, close it and then reopen the file
10. If you are not prompted to updated links, then you have successfully identified the region of cells that contained the Phantom links
11. If you are still prompted to update links, continue this step until you successfully remove a block of cells containing the Phantom Links
12. Now go back to the original file with the Phantom links, clear the section of the worksheet containing the Phantom Links, and recreate the formulas