This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro.
You may also want to read
Excel find all external links and broken links in workbook
There are several ways to find external links and broken links in workbook, some are easy to use but have limitations, I will talk about each method in this article. Note that this article writes about file link, not Hyperlink.
Basically there are three cases you would use external links:
1) In a formula, directly retrieve linked value
2) In a formula, retrieve value of Named Range defined in this workbook (simply go to Formulas > Name Manager to check)
3) In a formula, retrieve value of Named Range defined in external workbook
Less common use of external links include Objects and graphs. I am not going to talk about these less popular ones in this tutorial, you can click here to find out more from Microsoft support.
Use Find and Replace to find all external links
Normally, if you link a workbook to external source, you will see something like this in formula
External links always refer to a another file name within square brackets [ ], we can make use of this characteristics and search the any string within workbook that contains [ or ], but the assumption is that you don’t actually have a text that contains square brackets.
Although you may think this is a ridiculous method, it is suggested by Microsoft. In fact, Excel maintains a dependency table for the links instead of just looking for the square brackets.
Press CTRL+F > input and select details as follows > Find all
Now you can see all external links in the result box.
This method cannot search Named Range defined in another workbook because [ ] are not present in the formula.
This method also fails to indicate broken links.
Use Cell Relationship Diagram to find all external links and broken links in workbook
Excel 2013 introduces a new add-in to view the relationship diagram. To activate the add-in, navigate to Files > Options > Add-Ins
In dropdown box, select COM Add-ins > Press Go
Select Inquire > Press OK
Now you have a new tab INQUIRE
Workbook Relationship shows how your workbook is connected to other workbooks
If workbook link is broken, you will find the Excel logo turns red
Worksheet Relationship shows how your worksheets are connect to other worksheets, it can also show the linked workbook information.
Similar to Workbook Relationship, you will also see the Excel logo turns red if the link is broken.
Cell Relationship is relatively useless for our topic because it can only find the linked references for a single Cell each time.
Use Excel VBA to find all external links in workbook
If you want all the information of the external links and status used in formula, I highly recommend this VBA approach to you. In order to understand the VBA code, first you need to understand two Methods:
Workbook Method LinkSources can be used to find all external links in a workbook, storing the data in any array.
Suppose you have two formula that link to another workbook, for example
The returned array is
Array (1) : C:\Users\WYMAN\Desktop\folder\FileB.xlsx Array (2) : C:\Users\WYMAN\Desktop\folder\FileC.xlsx
Although LinkSources fails to locate the Cell address that contains the external link, we can loop through each used Range that contains formula and see if it contains the string of the link.
The next step is the add brackets [ ] to the workbook name.
Since external link can be with or without brackets, both of the below should be considered as as external links when you do the checking
Workbook LinkInfo Method is to check status of linked workbook, so that we know whether the workbook link is broken or normal.
VBA code – find all external links and broken links in workbook
The below Procedure makes use of both LinkSources and LinkInfo Methods to find all external links and broken links in workbook.
Since LinkSources fails to identify which Cell is using the external link, the Macro has to loop through each used Range to see if the cell formula matches links in LinkSources. On the other hand, if a cell contains Named Range which uses external link, the Macro would loop through each underlying external link of all Named Range to find any match.
Sub listLinks() alinks = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(alinks) Then Sheets.Add shtName = ActiveSheet.Name Set summaryWS = ActiveWorkbook.Worksheets(shtName) summaryWS.Range("A1") = "Worksheet" summaryWS.Range("B1") = "Cell" summaryWS.Range("C1") = "Formula" summaryWS.Range("D1") = "Workbook" summaryWS.Range("E1") = "Link Status" For Each ws In ActiveWorkbook.Worksheets If ws.Name <> summaryWS.Name Then For Each Rng In ws.UsedRange If Rng.HasFormula Then For j = LBound(alinks) To UBound(alinks) filePath = alinks(j) 'LinkSrouces returns full file path with file name Filename = Right(filePath, Len(filePath) - InStrRev(filePath, "\")) 'extract just the file name filePath2 = Left(alinks(j), InStrRev(alinks(j), "\")) & "[" & Filename & "]" 'file path with brackets If InStr(Rng.Formula, filePath) Or InStr(Rng.Formula, filePath2) Then nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1 summaryWS.Range("A" & nextrow) = ws.Name summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "") summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address summaryWS.Range("C" & nextrow) = "'" & Rng.Formula summaryWS.Range("D" & nextrow) = filePath summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus)) Exit For End If Next j For Each namedRng In Names If InStr(Rng.Formula, namedRng.Name) Then filePath = Replace(Split(Right(namedRng.RefersTo, Len(namedRng.RefersTo) - 2), "]")(0), "[", "") 'remove =' and range in the file path nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1 summaryWS.Range("A" & nextrow) = ws.Name summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "") summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address summaryWS.Range("C" & nextrow) = "'" & Rng.Formula summaryWS.Range("D" & nextrow) = filePath summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus)) Exit For End If Next namedRng End If Next Rng End If Next Columns("A:E").EntireColumn.AutoFit lastrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row For r = 2 To lastrow If ActiveSheet.Range("E" & r).Value = "File missing" Then countBroken = countBroken + 1 End If Next If countBroken > 0 Then sInput = MsgBox("Do you want to remove broken links of status 'File missing'?", vbOKCancel + vbExclamation, "Warning") If sInput = vbOK Then For r = 2 To lastrow If ActiveSheet.Range("E" & r).Value = "File missing" Then Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).ClearContents dummy = MsgBox(countBroken & " broken links removed", vbInformation) End If Next End If End If Else MsgBox "No external links" End If End Sub Public Function linkStatusDescr(statusCode) Select Case statusCode Case xlLinkStatusCopiedValues linkStatusDescr = "Copied values" Case xlLinkStatusIndeterminate linkStatusDescr = "Unable to determine status" Case xlLinkStatusInvalidName linkStatusDescr = "Invalid name" Case xlLinkStatusMissingFile linkStatusDescr = "File missing" Case xlLinkStatusMissingSheet linkStatusDescr = "Sheet missing" Case xlLinkStatusNotStarted linkStatusDescr = "Not started" Case xlLinkStatusOK linkStatusDescr = "No errors" Case xlLinkStatusOld linkStatusDescr = "Status may be out of date" Case xlLinkStatusSourceNotCalculated linkStatusDescr = "Source not calculated yet" Case xlLinkStatusSourceNotOpen linkStatusDescr = "Source not open" Case xlLinkStatusSourceOpen linkStatusDescr = "Source open" Case Else linkStatusDescr = "Unknown status" End Select End Function
All external links and status are displayed in a new worksheet. Column B contains a hyperlink that goes to the Cell that contains the link.
If the status in column E is “File missing”, a message box pops up and then you can click on OK to clear the Cell value.