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!
Thanks Pierre, that is my issue. Trying to fix that link has wasted hours of my time and would have wasted far more without your tip.
Cheers
Steve
Dude you rock!
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!
This worked for me after HOURS of looking for the phantom link it was in one of my worksheets – still don’t know where but i’ve been able to recreate my worksheet without the phantom link! 🙂
Wow, persistence paid off. I wonder what it was exactly; could have been a hidden shape or paste link formula. In any case, recreating the sheet is a drastic move but you did it and it works. Thanks for posting.
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.
Oh my goodness! – After 2 years working on a file with a persistent ghost link, I finally found it in Tactic 8, Data validation!!! – Kamran, THANK YOU!
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
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.
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.
Wow, what a story. Thank you, Hugh, this is great information about yet another possible cause to search through in this situation.
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!~
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.
Try deleting data validation from all cells.
It works for me