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.
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
You made my day buddy!
Oh my God, thank you so much! I’ve just spent hours on finding a phantom link, and your tactic was the only thing that worked! Thanks!
This is great – – I love seeing how my blog has enabled its visitors to help others. Thank you, Szandi and Pierre!
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.
Tactic 6 worked – and it was on a sheet that wasn’t supposed toi have conditional formatting. Thanks for the tip…
Thanks for the info, Jim!
Thanks, Pierre, for Tactic 6. Too bad i hadn’t found this long ago. I, too, learned this by trial and error.
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
Thank you for the nice note!
The mythical Tip 6 worked – Conditional formatting!
Thank god for that!
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.
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.
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.
Thanks, Patricia — good to know.
This is amazing. It frustrated me for many years, trying to figure out where those Links hidden were. Thanks for sharing!
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?
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.
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.
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.
Thank you, James!
This did it for me!! Thank you for those wise words!
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.
Wow. Thanks so much Dave, for sharing that. That’s a new one I’m adding to my list of possible reasons for phantom links.
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!
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!!!
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.
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.
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.
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.
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.
Thanks, Pete!
Tactic 7
If the links really are phantom just save the workbook under a different name and they will disappear.
Save the workbook under a different name
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”
Thank you for sharing that.