How to Find Broken Links in Excel (4 Quick Methods)

In this article, we’ll demonstrate 4 effective methods to find and fix broken links in Excel.

What Are Broken Links in Excel?

Our Excel sheets may contain links to other workbooks. If any of the existing workbook paths or names are changed, then the links will not function properly in the active workbook. These non-functioning links are known as ‘Broken Links’.

Reasons for Broken Links in Excel

The main causes of broken links in Excel are:

If the name of the source file or workbook is changed or modified.
If the location of the source file or workbook is altered.
If the source file or workbook is deleted from the computer.

Fix 1 – Using the Edit Links Command to Find and Fix Broken Links in Excel

In the following table we have random sales data over 3 successive months for several salesmen. All the sales data in the range C6 to E10 are linked from 3 different workbooks. Let’s determine if any of the cells contains a broken link.

Steps:

Select Edit Links from the Data ribbon.

The Edit Links dialog box will open.

Under the Source tab, all the workbooks linked to the cells in the current workbook are listed.

Click Check Status on the right.

Under the Status tab, you’ll find the statuses of the workbooks. The first two are OK, meaning the corresponding workbooks are not open right now but the sales data from those workbooks are valid.

If the status is ‘Source is open’ then it means that the corresponding workbook is active and open right now.

But suppose one of the linked workbooks is broken, because the name of the file ‘February Sales.xlsx’ has been changed to ‘Feb_Sales.xlsx’. The status of this workbook shows ‘Error: Source not found’.

Let’s fix the problem.

Click on the Change Sources option.

The File Browser will open up.

Select the source file of the corresponding workbook and click OK.

Click on the Check Status option again.

Now there is no error message under the Status bar in the Edit Links dialog box. The broken source link is fixed

Fix 2 – Using the Find and Replace Option to Find and Correct Broken Links

If our table data contains #REF! errors, it is likely because either the formulas used in the corresponding cells have returned the error values, or the source data are inaccurate in those cells. We can use the Find and Replace tool to find and correct those errors.

Steps:

Press CTRL+F to open the Find and Replace dialog box.
In the Find what box, type ‘.xl’.
Click Find All.

All the reference data from the other workbooks will be displayed.

Under the Value tab are some #REF! errors. Let’s check the formulas that have caused the errors in the corresponding cells.

The name of the source file here is ‘February Sales.xlsx’. But as we have seen in Fix 1, the name of this file has been changed to ‘Feb_Sales.xlsx’, causing the formula to return #REF! errors because it can’t find the source data.

To solve the problem, we’ll find and replace the names of those source files using the Find and Replace tool.

Switch to the Replace tab.
In the Find what box, enter ‘February Sales’.
Enter ‘Feb_Sales’ in the Replace with box.
Click Replace All.

The #REF! errors no longer appear under the Value bar.

Fix 3 – Using the Workbook Relationship Diagram to Find Broken Links in Excel

The Workbook Relationship Diagram is an excellent tool to find all the broken links. All the source files along with the broken links will be displayed in a diagram.

Steps:

Open Excel Options from the File menu.
Select the Add-ins tab.
In the Manage options, select COM Add-ins and click on Go.

In the COM Add-ins dialog box, mark the Inquire option and click OK.

We have enabled the Inquire ribbon, which is located in the topmost bar of your Excel workbook.

Select the Workbook Relationship option from the Inquire tab.

Click Yes in the Worksheet Relationship message box that opens.

A web diagram like the one below containing the names of the external workbooks is generated.

Notice the workbook name marked in a red border. The cross sign in the left-bottom corner means there is an error issue.

Place your mouse cursor on the symbol of the corresponding workbook.

A warning message pops up saying that the file is missing or not accessible. The broken link is identified.

Fix 4 – Using VBA Code to Find and Remove Broken Links in Excel

In our last method, we’ll use VBA code to find all broken links in an additional worksheet. We have collected the VBA code below from the Linkinfo and Linksources VBA methods. Now we’ll use them to find all the broken links in a workbook.

Steps:

Hover over the Sheet name at the bottom of the sheet and right-click.
Select View Code from the context menu.

A VBA window will open.

Paste the following code in this window:

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

Press F5 and the Macros dialog box will open up.
Click Run to run the pre-loaded macro.

In a new worksheet, all the links related to the external workbooks will be listed. Notifications of missing data or broken links will appear in the Link Status column.