May 26 2010

Find (and Delete) External Links

I’ve talked with many people that express frustration about having external links in their spreadsheet, but not being able to find them. If this has affected you, you’ve most likely noticed the warning Excel provides asking if you want to update external links when opening your spreadsheet. There are a few ways to locate these links and remove them if desired.

[ad#in-post-ad]

Using the Find Feature
A quick way to cycle through your external links (depending on the number you have) is to use the find feature built into Excel.  We will be searching for “[“, left bracket, which can help identity those external workbook references.

  • Hit Ctrl+F (or Edit–>Find on your toolbar) on your keyboard to open your find wizard
  • Click the “Options” button to open additional criteria
  • Select: within “Workbook”
  • Select: look in “Formulas”
  • Finally, enter a left bracket “[” in the Find What field (without double quotes)

That’s it.  Excel will now go through and show you all the instances where the left bracket has been used in your spreadsheet, which should identify external references to other spreadsheets.

Delete Links Add-In
Microsoft has also provided an add-in to help you identify and delete links.  To view Microsoft’s solution to this problem, visit http://support.microsoft.com/default.aspx?kbid=188449.  There is an additional Read-me file to help you use the add-in after you extract from the exe file.

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

37 Comments on this post

Trackbacks

  1. Saeed said:

    tnx very much, i love this site

    May 26th, 2010 at 10:29 pm
  2. Kanti Chiba said:

    You may also find links in the Names if you copy from amonther file.

    So check for these with :

    Insert>Name>Define and look for any links in the Names.

    May 27th, 2010 at 7:22 pm
  3. Automobile India said:

    Now I am getting a message that some of the links can’t be updated. I can see that the problem links are to bogus documents. But I can’t find the links them selves’….

    June 1st, 2010 at 11:52 pm
  4. Automobile India said:

    I’ve started a small business and have PPC advertising and a very thorough in depth website but getting external links is proving challenging.

    July 1st, 2010 at 4:50 am
  5. car battery said:

    Cheers, I was going a bit confused trying to figure out how to delete backlinks recently, even though I’ve been using Excel for a long time now.

    July 29th, 2010 at 8:51 am
  6. Automobile India said:

    hmm! figured that event tracking must be newer. It definitely looks like a better way. The fake page views thing was a bit of a kludge. I’ll have a look at event tracking for next time. The rest of the jquery might still be useful.

    August 2nd, 2010 at 4:53 am
  7. Irfan said:

    very useful tip
    i also applied one of my worksheet
    Thanks

    August 17th, 2010 at 12:57 am
  8. James Price said:

    Kanti Chiba comment about links in range names are very relevant. This is so easy to do without realising that you have done it.

    Also watch out for links in hidden worksheets as well.

    August 17th, 2010 at 9:23 am
  9. computer support said:

    How easily you solve a big problem. I wanna say thanks to the mastermind behind it. I wanted to thank you for this excellent read!! I definitely enjoyed every little bit of it. I have you bookmarked your site to check out the latest stuff you post.

    August 21st, 2010 at 2:31 pm
  10. Automobile India said:

    As you suggest, generic terms are fairer game, but there needs to be clear differentiation between internal and external links and the purpose of the link should be comparatively obvious from the outset.”

    September 2nd, 2010 at 12:02 am
  11. Online Penny Auctions said:

    I have an Excel doc. That has a lot of external links. Now I am getting a message that some of the links can’t be updated. I can see that the problem links are to bogus documents. But I can’t find the links them selves’. Is there a command in Excel that will lead me back to the links that can’t be updated?

    September 4th, 2010 at 1:28 pm
  12. Underfloor heating mats said:

    I have an Excel doc. That has a good deal of external hyperlinks. Now I am receiving a message that a few of the inbound links cannot be updated. I can see that the trouble hyperlinks are to bogus paperwork.

    October 2nd, 2010 at 8:10 am
  13. Karthik Krishnamoorthy said:

    Thanks for the Tip! Very helpful indeed!

    December 8th, 2010 at 5:56 pm
  14. Certified Translation said:

    Now I’m receiving a message that some on the inbound links cannot be updated. I can see that the trouble hyperlinks are to bogus paperwork.

    December 23rd, 2010 at 12:01 pm
  15. saglik haberiniz said:

    Hi, thanks for this beautiful informations.

    March 5th, 2011 at 8:49 am
  16. PhilUK said:

    Bill Manville has an addin – Findlink – which finds (and deletes) links.
    I find it very useful in these situations
    It can be downloaded from Steven Bullen’s website
    http://www.oaltd.co.uk/mvp/MVPPage.asp

    June 17th, 2011 at 4:24 am
  17. Khalid Irfan said:

    can someone tell me how to remove links from post?

    December 13th, 2011 at 2:05 am
  18. Pedro said:

    Hi, very easy and good information, Thank you!!

    March 8th, 2012 at 2:25 pm
  19. Francesco said:

    Neat and efficient tips. Thanks.

    April 8th, 2012 at 9:59 pm
  20. S. Keeran said:

    Thank you very much. I had a mess going and this worked!!

    A life saver

    October 13th, 2012 at 11:15 am
  21. Sharon said:

    Thank you, thank you, thank you!!!

    October 25th, 2012 at 10:05 am
  22. K said:

    If you hit ctrl button with the ` button, this shows all the links in the workbook as well. Hit ctrl and ` to get rid of this view once you are done.

    October 29th, 2012 at 12:10 pm
  23. J Sowers said:

    This was an awesome tip. So easy.

    February 8th, 2013 at 2:03 pm
  24. bOB said:

    i FOUND pHILLukS comment the most helpful. For excel 2010 users you need to actually open the .xla file in order to install it. I also had to place the .xla file into the Add-Ins folder in order for it to work. Thanks.

    February 20th, 2013 at 10:25 am
  25. Cecilia said:

    Thank you. This site helps allot. :)

    April 16th, 2013 at 10:47 pm
  26. Savvy1 said:

    Quick and easy description on how to find and remove links no longer needed. Thanks ExcelHints!!

    January 16th, 2014 at 11:05 am
  27. Sajjad said:

    Thanks, It is useful guidance.

    January 23rd, 2014 at 2:25 am
  28. Jan said:

    I have tried the above mentioned way of location links. It does not help. I have even tried to break the link, but still Excel says “This workbook contains one or more links that cannot be updated”

    Very frustrating – anybody that can help?

    February 19th, 2014 at 3:54 am
  29. J. Andrew Smith said:

    Finding left brackets in all formulas is NOT a perfect solution, as it once was in Excel = 2007, and maybe also “.xl?]” for links to Excel <= 2003 files.

    March 5th, 2014 at 1:09 pm
  30. J. Andrew Smith said:

    Sorry, my last comment got kind of “edited” there. Searching for just left brackets doesn’t work well any more, as it did in Excel = 2007, and also “.xl?]” for links to Excel <= 2003 files, because you'll find Excel-file extensions too.

    March 5th, 2014 at 1:22 pm
  31. Fabio Santos said:

    Great article! I’d just add you have also to remove some of references on the “Name Manager”. At least, I had! :)

    March 10th, 2014 at 10:48 am
  32. Andy Smith said:

    There could be links in objects too, such as comboboxes whose RecordSource property is a range in another workbook.

    Finding objects has to be done tab by tab: on every tab type Ctrl-G for Go To, click Special, choose Objects, click OK, then repeatedly hit the Tab key to select each object one at a time. When one object is selected, right-click it and get its Properties, and maybe you’ll find links there.

    Another possibility is charts whose data sources are in another workbook. Right-click any chart, choose Source Data and dig around.

    May 29th, 2014 at 4:45 pm
  33. Andy Smith said:

    Here’s some Excel-2010 code I wrote to help find links. I hope you can copy-and-paste it OK! It creates a tab-delmited text file in the same folder as the active workbook which lists all the links.

    Public Sub AllLinks()
    Dim Rg As Range, Rg1 As Range, WS As Worksheet, Nm As Name, Ch As Chart, cs As ChartObject
    Dim Obj As Object, Sh As Shape, F%, FN$, Flag As Boolean, TestStr$

    Close

    F% = FreeFile(): FN$ = ActiveWorkbook.FullName & ” Links.txt”: Open FN$ For Output As #F%

    Print #F%, “Type”; vbTab; “Worksheet”; vbTab; “Source”; vbTab; “Target”

    If Application.Workbooks.Count > 2 Then ‘ Including this workbook
    MsgBox “Please close all but the one workbook in which to find links.”, vbExclamation
    Exit Sub
    End If

    For Each Nm In ActiveWorkbook.Names ‘ Name links
    If Nm.RefersToLocal Like LinkRefPattern1$ Or Nm.RefersToLocal Like LinkRefPattern2$ Then

    Flag = True: Print #F%, “Name”; vbTab;
    If TypeName$(Nm.Parent) “Workbook” Then Print #F%, Nm.Parent.Name;
    Print #F%, vbTab; Nm.Name; vbTab; “‘”; Nm.RefersToLocal

    End If
    Next

    For Each Ch In ActiveWorkbook.Charts ‘ Links in chart sheets
    Flag = AllChartLinks(F%, Ch)
    Next

    For Each WS In ActiveWorkbook.Worksheets
    ‘ Formula links
    Set Rg1 = WS.Cells.Find(LinkRefPattern1$, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not (Rg1 Is Nothing) Then
    Set Rg = Rg1
    Do
    If Rg.HasFormula Then Print #F%, “Formula”; vbTab; WS.Name; vbTab; Rg.AddressLocal(False, False); vbTab; “‘”; Rg.FormulaLocal
    Set Rg = WS.Cells.FindNext(Rg)
    If Rg Is Nothing Then Exit Do
    Loop Until Rg.Address = Rg1.Address
    End If

    Set Rg1 = WS.Cells.Find(LinkRefPattern2$, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not (Rg1 Is Nothing) Then
    Set Rg = Rg1
    Do
    If Rg.HasFormula Then Print #F%, “Formula”; vbTab; WS.Name; vbTab; Rg.AddressLocal(False, False); vbTab; “‘”; Rg.FormulaLocal
    Set Rg = WS.Cells.FindNext(Rg)
    If Rg Is Nothing Then Exit Do
    Loop Until Rg.Address = Rg1.Address
    End If

    For Each cs In WS.ChartObjects ‘ Links in chart objects on worksheets
    Flag = AllChartLinks(F%, cs.Chart, WS)
    Next

    On Error Resume Next

    For Each Sh In WS.Shapes ‘ Shape links

    TestStr$ = “”: TestStr$ = Sh.ControlFormat.LinkedCell
    If TestStr$ Like LinkRefPattern1$ Or TestStr$ Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Shape CF Link”; vbTab; WS.Name; vbTab; Sh.Name; vbTab; “‘”; Sh.ControlFormat.LinkedCell

    TestStr$ = “”: TestStr$ = Sh.ControlFormat.ListFillRange
    If TestStr$ Like LinkRefPattern1$ Or TestStr$ Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Shape CF List”; vbTab; WS.Name; vbTab; Sh.Name; vbTab; “‘”; Sh.ControlFormat.ListFillRange

    TestStr$ = “”: TestStr$ = Sh.DrawingObject.LinkedCell
    If TestStr$ Like LinkRefPattern1$ Or TestStr$ Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Shape DO Link”; vbTab; WS.Name; vbTab; Sh.Name; vbTab; “‘”; Sh.DrawingObject.LinkedCell

    TestStr$ = “”: TestStr$ = Sh.DrawingObject.ListFillRange
    If TestStr$ Like LinkRefPattern1$ Or TestStr$ Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Shape DO List”; vbTab; WS.Name; vbTab; Sh.Name; vbTab; “‘”; Sh.DrawingObject.ListFillRange

    Next

    For Each Obj In WS.OLEObjects ‘ Object links

    TestStr$ = “”: TestStr$ = Obj.LinkedCell
    If TestStr$ Like LinkRefPattern1$ Or TestStr$ Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Object Link”; vbTab; WS.Name; vbTab; Obj.Name; vbTab; “‘”; Obj.LinkedCell

    TestStr$ = “”: TestStr$ = Obj.ListFillRange
    If TestStr$ Like LinkRefPattern1$ Or TestStr$ Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Object List”; vbTab; WS.Name; vbTab; Obj.Name; vbTab; “‘”; Obj.ListFillRange

    Next

    On Error GoTo 0

    Next

    If Not Flag Then Print #F%, “None”

    Close #F%

    End Sub

    Private Function AllChartLinks(F%, Ch As Chart, Optional WS As Worksheet = Nothing) As Boolean
    Dim cs As Series, Ax As Axis, DL As DataLabel, FL$, WSname$, Flag As Boolean

    If Not (WS Is Nothing) Then WSname$ = WS.Name

    For Each cs In Ch.SeriesCollection

    FL$ = “”
    On Error Resume Next
    FL$ = cs.FormulaLocal
    On Error GoTo 0

    If FL$ Like “=SERIES(*,*,” & LinkRefPattern1$ & “!*,*)” Or FL$ Like “=SERIES(*,*,” & LinkRefPattern2$ & “!*,*)” _
    Then Flag = True: Print #F%, “Chart Series”; vbTab; WSname$; vbTab; Ch.Name; “/”; cs.Name; vbTab; “‘”; FL$

    If cs.HasDataLabels Then
    For Each DL In cs.DataLabels

    If DL.FormulaLocal Like LinkRefPattern1$ Or DL.FormulaLocal Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Chart Series Label”; vbTab; WSname$; vbTab; Ch.Name; “/”; cs.Name; “/”; DL.Name; vbTab; “‘”; DL.FormulaLocal

    Next
    End If

    Next

    If Ch.HasTitle _
    Then If Ch.ChartTitle.FormulaLocal Like LinkRefPattern1$ Or Ch.ChartTitle.FormulaLocal Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Chart Title”; vbTab; WSname$; vbTab; Ch.Name; vbTab; “‘”; Ch.ChartTitle.FormulaLocal

    For Each Ax In Ch.Axes
    If Ax.HasTitle _
    Then If Ax.AxisTitle.FormulaLocal Like LinkRefPattern1$ Or Ax.AxisTitle.FormulaLocal Like LinkRefPattern2$ _
    Then Flag = True: Print #F%, “Axis Title”; vbTab; WSname$; vbTab; Ch.Name; ” / “; AxisGroup$(Ax.AxisGroup); vbTab; “‘”; Ax.AxisTitle.FormulaLocal
    Next

    AllChartLinks = Flag

    End Function

    May 29th, 2014 at 4:58 pm
  34. Andy Smith said:

    Oops, forgot a couple of constants:

    Const LinkRefPattern1$ = “*.xl??]*”, LinkRefPattern2$ = “*.xl?]*”

    May 29th, 2014 at 5:07 pm
  35. Anonymous said:

    I think the admin of this website is truly working hard in support
    of his site, since here every stuff is quality based information.

    June 18th, 2014 at 3:37 am
  36. Mick said:

    Great, thanks a lot!

    June 22nd, 2014 at 7:21 pm
  37. Rebecca said:

    One other form of hidden links, check if you have drop down boxes referencing external data! Thanks to PhilUK’s message about Findlink which found it for me!

    “Bill Manville has an addin – Findlink – which finds (and deletes) links.
    I find it very useful in these situations
    It can be downloaded from Steven Bullen’s website
    http://www.oaltd.co.uk/mvp/MVPPage.asp

    July 8th, 2014 at 3:35 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories