You may open a workbook and see a message asking whether you want to update links in a workbook. This message sometimes appears even when a workbook contains no linked formulas.
Phantom links are external links that reference another workbook. Unexpected external linking can occur for various reasons, many of them stemming from moving or copying charts, chart sheets, or worksheets into another workbook.
Here are a few ways to deal with the spooky phantom link problem.
Find Phantom Links Manually
First, you need to see whether you have any real external links (nonphantom) that you forgot about. If you are not sure whether you have real external links, you need to check every cell, formula, name, chart, object, and comment in your workbook for any reference to an external file.
You can do this by ensuring no other workbooks are open. Press
Ctrl+F (or select
Home > Editing > Find & Select > Find) to start looking in the most obvious place: your formulas.
Optionsto expand the Find and Replace dialog box
- Select Workbook in the
Within:drop-down to search the entire workbook.
Look in:drop-down to search only formulas
- Then search for
[*], the asterisk represents a wildcard string.
Also search for common link indicators, such as:
- The file path of the external file, such as
- The file extension of the external file, such as
- The square brackets around the external file name, such as
- The exclamation mark after the external file name, such as
If you find any of these indicators, you need to delete them or replace them with valid references. You also need to check if any of your data validation rules, conditional formatting rules, pivot tables, or slicers are linked to external files and remove them if necessary.
Edit Links Feature
Data > Edit Links allows you to see all the external links in your workbook and manage them easily. To use this feature, follow these steps:
Datatab and choose
Edit Linksin the
Queries & Connectionsgroup.
- In the
Edit Linksdialog box, you will see a list of all the external files that your workbook is linked to.
- Select files and click on
Check Statusto see if the files are accessible or not.
- If the
Error: Source not found, click
Break Linkto remove the link or click
Change Sourceand browse for the workbook.
If you break a link, Excel will replace the link with the current value of the cell or object that contains it.
Using Name Manager
If previous methods don’t solve the problem, the phantom links may be caused by an erroneous name:
- Choose the
Formulastab and click on
Name Managerin the
- In the
Name Managerdialog box, look for any names that have a reference to an external workbook. You can identify them by the square brackets around the workbook name, such as
- Select the name that contains a phantom link and click on
Delete. Confirm your action by clicking on
- Repeat steps 2 and 3 for any other names that have phantom links.
- Close the
Name Managerdialog box and save your workbook.