Tom’s Tutorials For Excel: Finding and Deleting Phantom Links


Share Button

Tom’s Tutorials For Excel: Finding and Deleting Phantom Links

If you’ve ever wondered why this message pops up after you are sure you’ve deleted all your workbook’s links, well, join the club. It’s a common problem because some links can be difficult to find.

In my 20 years working with Excel, I have not yet seen a single case when this message appeared and there was not a link; Excel is very good at recognizing an existing link. So, how do you find a link when you have looked throughout your workbook and cannot locate what Excel says is there?

Here are some tactics in no particular order for searching and destroying phantom links:

Tactic 1
Right-click on any sheet tab, left-click Select All Sheets, press Ctrl+F and in the Find what field enter
=[
or
!
The idea is to look through all sheets for formulas referencing external workbooks, so be sure to unhide any hidden sheets with this tactic.

Tactic 2
Look closely at your list of defined names, not just in the name box but in the Define Name dialog, and make sure you do not have any named ranges refering to an outside Workbook. In Excel version 2003 or before, from the worksheet menu click Insert > Name > Define. In version 2007 or 2010, from the Ribbon click the Formulas tab, and in the Defined Names section click Name Manager.

Tactic 3
Open a new workbook, create a link to it, and save that workbook. Now go to Edit > Links (version 2003) or the Name Manager (version 2007 or 2010), click the Edit button, and use the “Change Source” to refer the link to the new workbook. Save again and then delete the link you created.

Tactic 4
If you have pivot tables or charts, they may contain source data that is derived from outside workbooks and hence being the links culprit. Take a look at the chart series and the pivottable source ranges.

Tactic 5
A few macros:

Sub DeleteLinks()
'thanks Hans Herber 
Dim varLink As Variant, intCounter As Integer
varLink = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(varLink) Then
For intCounter = 1 To UBound(varLink)
ActiveWorkbook.ChangeLink Name:=varLink(intCounter), newname:=ThisWorkbook.Name
Next intCounter
End If
End Sub

Sub ExternalLinkDelete()
'thanks Hans Herber 
Dim cell As Range
For Each cell In activeworksheet.UsedRange
If Left(cell.Formula, 1) = "=" And InStr(cell.Formula, "[") > 1 Then cell.Value = cell.Value
Next cell
End Sub

Sub RemoveAllHyperLinks()
Dim Sh As Worksheet
For Each Sh In Worksheets
Sh.Cells.Hyperlinks.Delete
Next Sh
End Sub

Good luck ! The above tactics are by no means a comprehensive list. With persistence you will find the link(s), maybe with the above suggestions, or from your own ideas or suggestions by others.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
16 comments on “Tom’s Tutorials For Excel: Finding and Deleting Phantom Links
  1. Pierre says:

    Hello,
    You could add a tactic 6 which I just found after hours of testing, as all the methods listed above did not work: I excel 2010, the link can be as well in some conditional formatting formulas.
    You have to go in to Conditional formatting / manage rules / this worksheet and remove the rules which refer to the external link.
    Regards
    Pierre

  2. Larry says:

    I’ve seen data validation ghosts links to external drop down list ranges as a lof the causes for the external links. This comes from people copying and pasted all instead of pasting values.

  3. Jim Robin says:

    Tactic 6 worked – and it was on a sheet that wasn’t supposed toi have conditional formatting. Thanks for the tip…

  4. Greg says:

    Thanks, Pierre, for Tactic 6. Too bad i hadn’t found this long ago. I, too, learned this by trial and error.

  5. villanovano says:

    Hello Tom! You helped me a lot with Tactic 2… This name thing was driving me crazy because I could not find the dammed link… because there was not any… it was a name problem.

    You are the master… Thanks pal

  6. Worm says:

    The mythical Tip 6 worked – Conditional formatting!

    Thank god for that!

  7. Jon Peltier says:

    Lately in 2013 & 2016, when I get a warning about links, if I can’t find them, rebooting Excel can clear them up. Go figure.

    • Tom Urtis says:

      That’s interesting. I myself have not seen a case where Excel is wrong about that, but what you are seeing means that sometimes Excel is wrong, which shouldn’t surprise given their errors in Help.

  8. Patricia says:

    I couldn’t get the first few tactics to work but I did find a work around. If you tell Excel to update the link you then get an Edit Links box and it shows the source. I tried breaking the link and I still got the Update Links message but under the Edit Links dialog box is a Start Up Prompt dialog box. You can tell it to stop displaying the Update Links message when opening the file and to NOT update the links automatically.
    Thanks for all the suggestions Tom. I will keep Tactic 6 just in case.

  9. Doris Choo says:

    This is amazing. It frustrated me for many years, trying to figure out where those Links hidden were. Thanks for sharing!

  10. RoderickE says:

    I’ve tried all the tactics to no avail for this issue. Only when I refresh data do I get the popup about links. I’ve check Named Ranges where you’d think the problem lies since refreshing it doing pivot tables. Any idea?

  11. Jerry Norbury says:

    There is yet ANOTHER source of links.

    Links are kept (hidden, cannot be searched for) in Excel tables which contain formula (aka calculated columns).

    Those formula can contain links to external sources.

    The way to find them is to add a row to the table and see if the formula columns suddenly come up #REF.

    The way to delete them is to ensure that the exact SAME formula is in every cell of the appropriate column, then delete the top row of the table under the headers.

    • Tom Urtis says:

      Thanks Jerry. I will look into this further and ask the Tables people at Microsoft about why this is, and if Excel can make the links identification process more intuitive, or at least, easier.

Leave a Reply

Your email address will not be published. Required fields are marked *

*