While working with Microsoft Excel, it’s a common scenario to find some broken links in the data. There are a few simple reasons behind this problem and we can fix these broken links easily once we can find them. In this article, you’ll learn different suitable methods to search and fix broken links in Excel with short examples and quick explanations.
What Are Broken Links in Excel?
Our Excel sheets may contain different data or codes linked to the other workbooks. If any of the existing workbook paths or names are changed then the links will not function properly in the active workbook. Those links are known as ‘Broken Links’. But we have also options to fix these broken links and connect them to the correct sources.
Reasons for Broken Links in Excel
There are a few reasons that cause the broken links in Excel.
- 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.
1. Using Edit Links Command to Find and Fix Broken Links in Excel
In the following table, some random sales data over successive 3 months for several salesmen are present. All the sales data ranging from C6 to E10 are linked to 3 different workbooks. Now we’ll find out if any of the cells contains a broken link.
📌 Step 1:
➤ Select Edit Links from the Data ribbon first.
A dialog box will appear.
📌 Step 2:
➤ Under the Source tab, you’ll find all the workbooks linked to the cells in the current workbook. Now press Check Status on the right.
Under the Status tab, you’ll find the statutes of the workbooks. The first two are OK means the corresponding workbooks are not open right now but the sales data from those workbooks are valid.
If the status if ‘Source is open’ then it means that the corresponding workbook is active and open right now unlike the previous ones.
But let’s think of a scenario one of the workbooks is broken. It’s because the name of the ‘February Sales.xlsx’ has been changed to ‘Feb_Sales.xlsx’. That’s why the status of this workbook is showing ‘Error: Source not found’.
So, let’s fix the problem in the next steps.
📌 Step 3:
➤ Click on the Change Sources option.
The File Browser will open up.
📌 Step 4:
➤ Select the source file of the corresponding workbook and press OK.
📌 Step 5:
➤ Click on the Check Status option again and now you’ll see no error message under the Status bar in the Edit Links dialog box.
Read More: How to Break Links in Excel and Keep Values
2. Applying the Find and Replace Option to Search and Correct Broken Links
Sometimes our table data may contain #REF! errors. Probably the formulas used in the corresponding cells have returned the error values or the accurate source data are missing in those cells. We can use the Find and Replace tool to search and correct those errors.
📌 Step 1:
➤ Press CTRL+F to open the Find and Replace dialog box.
➤ In the Find what box, type ‘.xl’.
➤ Press the option Find All.
Like in the picture below, you’ll get all the reference data of the other workbooks.
Now look carefully under the Value tab and you’ll find some #REF! errors there. Let’s check the formulas that have caused the errors for the corresponding cells. The name of the source file is here ‘February Sales.xlsx’. But as we have seen in the first method that the name of our corresponding source file has been changed to ‘Feb_Sales.xlsx’, the formulas, therefore, have led to the #REF! errors here.
So, what we’ll have to do is now find and replace those names of the source files by using the Find and Replace tool in the next steps.
📌 Step 2:
➤ Switch to the Replace tab.
➤ In the Find what box, type ‘February Sales’.
➤ Type ‘Feb_Sales’ in the Replace with box.
➤ Press Replace All.
Finally, you’ll see no #REF! anymore under the Value bar in the following dialog box.
Read More: How to Break Links in Excel When Source Is Not Found
3. Applying the Workbook Relationship Diagram to Find Broken Links in Excel
We can also use the Workbook Relationship Diagram to find out all the broken links. All the source files along with the broken links will be displayed in a diagram as the resultant data. Let’s go through the following procedures now to see how this relationship diagram works to identify and show the broken links more effectively than the previous methods.
📌 Step 1:
➤ Open Excel Options from the File menu in your workbook first.
➤ Choose the Add-ins tab.
➤ In the Manage options, select COM Add-ins and click on the Go button.
📌 Step 2:
➤ In the COM Add-ins dialog box, mark on the Inquire option and press OK.
So, we’ve just enabled the Inquire ribbon which you’ll find in the topmost bar of your Excel workbook.
📌 Step 3:
➤ Now select the Workbook Relationship option from the Inquire tab.
📌 Step 4:
➤ Press Yes after you see the following message box.
Like in the picture below, you’ll notice a web diagram with the names of the external workbooks.
Now, look at the workbook name marked by a red rectangular border. This is the workbook that we’re looking for. The left-bottom corner of the workbook name has a cross sign. That means this workbook name has an error issue.
📌 Step 5:
➤ Put your mouse cursor on the symbol of the corresponding workbook.
And you’ll find a warning message saying that the file is missing or not accessible. This is how we can identify the broken link of an existing workbook.
Read More: Why Do My Excel Links Keep Breaking?
4. Using VBA Codes to Find and Remove Broken Links in Excel
In our last method, we’ll use the VBA codes to find all broken links in an additional worksheet. We have collected the following VBA codes from the Linkinfo and Linksources methods. So, let’s have a look at how these codes function to find all broken links in Excel.
📌 Steps:
➤ In the Sheet name, right-click your mouse button.
➤ Select View Code from the options, a VBA window will appear where you have to type the codes.
➤ Now paste the following codes there:
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.
➤ It will automatically load a Macro name, so press Run only.
In an additional worksheet, you’ll find all the links related to the external workbooks. You’ll see the messages for missing data or broken links under the Link Status tab on the rightmost column.
In the corresponding worksheet, you’ll also notice a message box as shown in the screenshot below. If you want to remove all the broken links then press OK and the broken links will disappear finally from your Excel spreadsheets.
Read More: Excel VBA to Break Links
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Concluding Words
I hope, all of these methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to find and repair the broken links. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
You May Also Like to Explore
<< Go Back To Excel Break Links | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
