How do I break a dead link in Excel?

In Excel, we usually create some external links for some purposes, however, after finishing the specific purpose, you may want to find and break all the external links. Here I introduce you the tricks to quickly find and break external links.

Find and break external links with Edit Link function

Find and break external links with Kutools for Excel

Find and break external links with Edit Link function

If you want to find and break external links, you can apply Edit Link function.

1. Click Data > Edit Links. See screenshot:

2. In the Edit Links dialog, select a workbook that you want to break the external links, and then click Break Link. See screenshot:

3. In the popping out warning dialog box, please click the Break Links button. See screenshot:

4. Then repeat step 2 to break all external links from the whole workbook. After removing all external links, please close the Edit Links dialog box.

Find and break external links with Kutools for Excel

With above method, you will break all the external links from the same workbook, but in sometimes, you may want to break some of the external links from different worksheets, in this case, you can use Kutools for Excel’s Find and Break Broken Links function to solve.

Free Download
free full-featured in 30 days

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Link > Find and Break Broken Links. See screenshot:

2. In the Find and Break Broken Links dialog, you can see all the external links are list in the dialog, and check the links you want to break and then click Break link to break all the checked links.

Tip:

1. In the Find and Break Broken Links dialog, you can filter links based on linked worksheet by selecting the sheet name from the Filter dropdown list.

2. You also can view the cell in actual time when you click the link in the Find and Break Broken Links dialog after checking View cell option.

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.

Table of Contents hide

Download Practice Workbook

What Are Broken Links in Excel?

Reasons for Broken Links in Excel

4 Suitable Approaches to Find and Fix Broken Links in Excel

1. Use Edit Links Command to Find and Fix Broken Links in Excel

2. Use the Find and Replace Option to Search and Correct Broken Links

3. Apply the Workbook Relationship Diagram to Find Broken Links in Excel

4. Embed VBA Codes to Find and Remove Broken Links in Excel

Concluding Words

You May Also Like to Explore

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Find Broken Links.xlsx

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.

4 Suitable Approaches to Find and Fix Broken Links in Excel

Let’s find out now how we can apply the following methods to find the broken links and then fix them within seconds.

1. Use 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 Edit Links in Excel

2. Use 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 (4 Ways)

Similar Readings

• How to Remove External Links in Excel
• Find External Links in Excel (6 Quick Methods)
• Hyperlink to Cell in Excel (2 Simple Methods)
• How to Break Links in Excel (3 Quick Methods)

3. Apply 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? (3 Reasons with Solutions)

4. Embed 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: How to Break Links in Excel and Keep Values (3 Easy Ways)

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.