How to Find Excel Phanton Links

Why can't I break a external link in my workbook ? How can I locate "phantom links" in a workbook ?
Excel is asking if I want to update links, but I don't have any external link in my workbook! Is this a bug ?

These are only three examples of questions asked by lots of Excel users when they encounter the infamous "phantom link" phenomenon. Since Excel is never wrong about identifying links, you can be sure that your workbook does contain one or more links... but most probably they are not formula links.

So, how can we identify and eliminate "Phantom links" ?

We are already assuming that you had We can divide the process into 4 steps ... although you might not need them all to break the links in your workbook.

Before starting the execution of the following steps, please assure that you have already hit the Data > Edit Links > Break Link button, which will eliminate all references to external workbooks (except the one(s) which is causing the so called phantom links) .

If the Break Link button has not eliminated all the links to external workbooks, then:

Step 1: Identify Formula Links At this stage the the Edit Links button will most probably not be active anymore. If it is still active then click it and the Links dialog box will open. There you can see the name of the source file(s) for the link(s). Click the Change Source button and change the link so it refers to the active file.

Step 2: Identify links in Defined Names

This second step is, by far, the most common cause and will solve most cases of phantom links. Open your Name Manager (Ctrl + F3 or go to Formulas Tab / Defined Names / Name Manager) and scroll through the list of defined names and examine the Refers To field. If a name refers to another workbook or contains an erroneous reference such as #REF!, delete it.


Step 3: Identify links in Charts This is probably the most "painful" step of them all as you have to click on each data series, text box or title of your chart and examine the formula bar for references to other workbooks. The links can be eliminated by copying (as values) the data located into the current workbook or moving the chart's data to the current workbook and redo the chart.


Step 4: Identify links in Objects External references can also be attached to objects. The quickest way of reviews objects in a workbook is to use the Go To (F5 function key as shortcut) >> Special >> Select Objects >> Click OK. 

 After that, you can press the Tab key to move from object to object and examine the formula bar to search for external references.  If an object contains a reference to another workbook just edit or delete the reference.

Important Note: If Step 1 and Step 2 can be executed for the whole workbook, the last two steps have to be executed on each sheet of your workbook.

Do you like this free website? Please share this page on Google+ or Facebook.