Excel User Tips

Locate phantom links in a workbook

Q. Whenever I open a particular Excel workbook, I get a message asking if I want to update the links. I've examined every formula in the workbook, and I am absolutely certain that the workbook contains no links to any other file. What can I do to convince Excel that the workbook has no links?

You've encountered the infamous "phantom link" phenomenon. I've never known Excel to be wrong about identifying links, so there's an excellent chance that your workbook does contain one or more links -- but they are probably not formula links.

Follow these steps to identify and eradicate any links in a workbook.

Select Edit, Links. In many cases, this command may not be available. If it is available, the Links dialog box will tell you the name of the source file for the link. Click the Change Source button and change the link so it refers to the active file.
Select Insert, Name, Define. Scroll through the list of names in the Define Name dialog box and examine the Refers to box (see the figure below). If a name refers to another workbook or contains an erroneous reference such as #REF!, delete the name. This is, by far, the most common cause of phantom links
If you have a chart in your workbook, click on each data series in the chart and examine the SERIES formula displayed in the formula bar. If the SERIES formula refers to another workbook, you've identified your link. To eliminate the link move or copy the chart's data into the current workbook and recreate your chart.
If your workbook contains any custom dialog sheets, select each object in each dialog sheet and examine the formula bar. If any object contains a reference to another workbook, edit or delete the reference.

Next, save your workbook and then re-open it. It should open up without asking you to update the links.