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

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
51 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.

    • Ben Bennett says:

      W O W ! Adding a row did the trick. The #REF popped up immediately. Hours (years really) spent on this issue and finally have a fix instead of a workaround.

      Thanks Jerry! Thanks Tom!

  12. James Giordano says:

    I was able to get rid of them by deleting tabs one-by-one and then checking Edit Links.

    I suggest starting on a tab where the formulas or other content has been pasted in from other workbooks.

    Be careful, do not accidentally save a workbook with the tabs you want to preserve already deleted.

  13. Dave Martin says:

    I had a table in which a whole column had all been linked with an identical formula to an external workbook. When I had picked up the data I wanted I did a Copy and Paste Values on the whole column. When I came back to it some time later It said on opening that there were external links present and I spent ages trying to find them. No cell any longer had the formula with the link in it that Find could locate but the Table still remembered the formula which had been there! I tracked it down in the end by adding a row and finding the formula in one of the new cells but there really should be a more intuitive way of finding it. Better still the Table should not try to remember an old formula when no cell is any longer using it. Then I found this Tutorial which would have saved me a lot of time! Thanks

    This was on the latest version of Excel for Mac running under High Sierra.

  14. Chris K. says:

    Pierre you are my savior! Tactic 6 was the only one that worked and on a sheet that it was not supposed to have conditional formating! Thanks a lot!

  15. Joe Harvey says:

    Possible new tactic. I found my phantom link in an object. Home>Find & Select>Select Objects>Ctrl-A>Delete
    This might pair with Tactic 4.
    This helped since I didn’t even know the object existed but I knew the link was on a specific tab.
    Thanks for the help!!!

    • Tom Urtis says:

      Thanks for that tip Joe. The only caveat is to make sure you don’t delete an object that you really need!

      A quick navigation for Objects and all Special Cells categories is to hit the F5 key, Alt+S or click the Special button, select Objects or whatever SpecialCells category you want, and click OK.

  16. Lloyd Fleming says:

    Thanks for all these suggestions – just wasted an hour or more trying to eliminate a phantom link but in the end, the suggestion of just shutting down excel and re-opening (after I’d gone through steps 1 to 6 above) worked. Mind you, excel helped by crashing as well.

  17. Colin says:

    Great info thanks, none of the above worked so ended up deleting one sheet at a time until I found the one with the phantom links. I then found a hidden form on the offending sheet, thanks to this page https://www.microsoft.com/en-us/microsoft-365/blog/2009/01/30/hidden-and-invisible-objects/
    However I still can not locate or delete this form. I will have to delete the sheet and re-establish all the various calculations linked to it.

    • Tom Urtis says:

      Hi Colin, thanks for the info. As you describe your workbook, it sounds like the form you speak of is a collection of embedded objects on a worksheet, as opposed to a UserForm. There might be hidden objects too, or hidden named ranges that refer to linked workbooks. Feel free to post back with an update if that turns out to be the case.

  18. PeteO says:

    Many thanks for the hints. Every couple of years I find myself having to call in air support to clear this mystery up. Conditional formatting has been my downfall in the past but this time it was your Tactic 2, Names.
    Bless you, Microsoft, for encouraging us to think.

  19. Ally says:

    Tactic 7

    If the links really are phantom just save the workbook under a different name and they will disappear.

  20. Alison says:

    Save the workbook under a different name

  21. Kamran says:

    After wasting a few hours I was able to identify a Tactic 8:
    Go to “Find & Select” and choose “Data Validation”. Excel will show cells that links to an external sheet. Replace them with normal cells or “Clear all”

  22. Dan Regalia says:

    Hey Tom,
    I ran across your blog trying to hunt down some phantom links, great resource. Here’s something for you.
    I got a workbook from one of our accountants. I found references in this workbook going back to 1996 based on named ranges linked to filenames… Anyways, It had a bunch of links in it, that were bad.. turns out, they’re all hidden in the named ranges. This blog clued me in. So, for Excel 2019, it’s not that forgiving. Most of the bad/deleted named ranges are just set to invisible and ignored, but it looks like they’re being analyzed now. Time to clean house.
    This code will make a worksheet of all the named ranges.
    The second part is where you set them to visible to you can see them in the names manager. Now, you technically can, if you’re comfortable, delete them immediately, (see commented code), but don’t do that unless you know for a fact.
    Sub List_All_NamedRanges()
    Dim nr As Variant
    Sheets.Add
    v = 1
    For Each nr In ThisWorkbook.Names
    Cells(v, 1).Value = v
    Cells(v, 2).Value = “‘” & nr.Value
    Cells(v, 3).Value = “‘” & nr.RefersTo
    v = v + 1
    Next nr

    End Sub

    Sub fixbadNames()
    Dim nr As Variant
    v = 1
    For Each nr In ThisWorkbook.Names
    If InStr(1, nr.RefersTo, “”, vbTextCompare) > 0 Then
    nr.Visible = True
    End If
    If InStr(1, nr.RefersTo, “”, vbTextCompare) > 0 Then
    nr.Visible = True
    End If
    If InStr(1, nr.RefersTo, “”, vbTextCompare) > 0 Then
    nr.Visible = True
    End If
    If InStr(1, nr.RefersTo, “”, vbTextCompare) > 0 Then
    nr.Visible = True
    End If
    ‘ If InStr(1, nr.RefersTo, “=#REF!#REF!”, vbTextCompare) > 0 Then
    ‘ nr.Delete
    ‘ End If

    v = v + 1
    Next nr
    End Sub

    • Tom Urtis says:

      Hi Dan thanks for your comment and observations.

      On my social media channels (I post daily Excel tips on FB Biz, Twitter and LinkedIn), I have shown how to access invisible named ranges and to list any if needed. Your code is excellent and I have made sure to keep it published on my Blog’s comments here. Coincidentally I’m making design changes to my website this week. Your macros wrap everything together in one package and is quite a useful resource. Cool stuff. Maybe you can explain why you have so many individual terminating If statements in the fixbadNames macro instead of an ElseIf or Select Case structure. The only reason I can see doing that is if one single “nr.RefersTo can have more than one TRUE evaluation on different properties.

  23. Hugh says:

    Thanks for this good info. We are using a worksheet that had been through many iterations and updates. The “update / don’t update” message appeared when opening the worksheet. Data > Edit links showed links to three external sources, which were earlier versions of the worksheet saved on various drives. The “break links” approach removed one of them, but the other two would not break despite repeated attempts. Searching in the workbook didn’t find them. Tried checking for data validation, names, etc. There were no tables or objects.

    I deleted tabs to identify which sheet had the phantom links. It was the main database, which had previously included many columns with historical data but had recently been simplified to contain only current data. I highlighted and deleted about 50 empty columns to the right of the last column containing data, and the phantom links disappeared. Perhaps when deleting the historical data, one of my collaborators had deleted data but not columns, and somehow the phantom links were hiding there. When the empty columns were deleted, the links went with them.

    Excel for Mac v.16.37.

  24. Amber K says:

    I have Excel 2016 Files,and yes I say files because anytime I create a blank file and copy with NO formatting from one file to another the same phantom cell links happen.

    I can be in Tab 3, and make changes which will show up in random cells in other tabs.

    There are no defined names, links that are found with .xl find, I’ve gone through the list…no resolution.

    ANY help is GREATLY appreciated!~

    • Tom Urtis says:

      Could be a few different reasons why. On each sheet, hit the F5 key and select the Special button. Click for Objects and OK (again, do this for each sheet) and see if there are hidden objects that refer to anything outside the workbook. Are you sure there is no programming code whatsoever in any of the modules. This has been one of my more active blog topics, so take a look, if you haven’t already, to read through the comments, as there are some really good tips for uncommon places where links could be hiding that many users may not be aware of.

  25. Michela says:

    Try deleting data validation from all cells.
    It works for me

  26. Ian says:

    After trying a variety of these solutions, I realised I could follow back the version trail to see where the links had appeared in the first place. This led me to finding the source – they were simply cells that had previously been linked, and the tab was somehow remembering these links, even though I’d pasted over everything with unformatted data.

    In the end, once I suspected I knew what the problem was, was to rename the tab. This immediately removed the link.

    • Tom Urtis says:

      Thank you for sharing your solution. I did not expect this topic when I posted it to be as comment-rich as it has been over the years. A lot of Excel users experience phantom links. and a lot of users have contributed their found fixes.

Leave a Reply

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

*