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
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
Next, save your workbook and then re-open it. It should open up without
asking you to update the links.