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
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.
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.
Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.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.