Aung Shine
Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we input data from different sources by linking multiple excel files. However, the links may get broken for various reasons. That will result in errors in the worksheet we’re working on. So, we will need to remove those broken links. This article will show you 3 simple methods to Remove Broken Links in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
Introduction to Broken Links in Excel
Often, our dataset contains links to other worksheets or workbooks. However, there are some reasons for which the links break. The possible causes are Name change of the source worksheet or workbook, location alteration of the source file, and deletion of the file. If these cases happen, the links will not perform accurately in the active worksheet. It will result in errors. Those links are termed broken links.
In the following dataset, the D5 cell is linked to the D5 cell of Sheet1 in the Sample file.
However, the source sheet name got changed in the Sample file. And that’s why you can see the #REF! error in the below figure. In this article, we’ll show how to remove such kinds of links.
3 Simple Methods to Remove Broken Links in Excel
There are multiple ways to remove broken links in excel. Here, we’ll discuss all 3 methods to carry out the operation. So, follow along.
1. Remove Broken Links Through Edit Links Command in Excel
The most simple process is to use the Edit Links Command. Therefore, follow the below steps to perform the task.
STEPS:
- First, go to the Data tab.
- Now, select the Edit Links option from the Queries & Connections group.
- As a result, the Edit Links dialog box will pop out.
- There, select the broken link.
- Then, click the Break Link option.
- Consequently, you’ll get a warning dialog box.
- Press Break Links.
- Thus, it’ll remove all the broken links in the excel worksheet.
Read More: How to Break Links in Excel (3 Quick Methods)
2. Use Excel VBA to Remove Broken Links
Moreover, you can apply VBA codes to perform the task. Hence, learn the following process.
STEPS:
- Firstly, go to the Developer tab.
- Next, select the Visual Basic option from the Code group.
- Subsequently, in the VBA window, click Insert ➤ Module.
- After that, copy the below code and paste it into the Module box.
Sub RemoveLinks()
alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(alinks) Then
Sheets.Add
shtName = ActiveSheet.Name
Set summaryWS = ActiveWorkbook.Worksheets(shtName)
summaryWS.Range("A1") = "sheet"
summaryWS.Range("B1") = "location"
summaryWS.Range("C1") = "function"
summaryWS.Range("D1") = "file"
summaryWS.Range("E1") = "outcome"
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 = "Source unavailable" Then
countBroken = countBroken + 1
End If
Next
If countBroken > 0 Then
sInput = MsgBox("Do you want to remove broken links of status 'Source unavailable'?", vbOKCancel + vbExclamation, "Warning")
If sInput = vbOK Then
For r = 2 To lastrow
If ActiveSheet.Range("E" & r).Value = "Source unavailable" 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 "links absent"
End If
End Sub
Public Function linkStatusDescr(statusCode)
Select Case statusCode
Case xlLinkStatusCopiedValues
linkStatusDescr = "data copied"
Case xlLinkStatusIndeterminate
linkStatusDescr = "status unavailable"
Case xlLinkStatusInvalidName
linkStatusDescr = "wrong name"
Case xlLinkStatusMissingFile
linkStatusDescr = "Source unavailable"
Case xlLinkStatusMissingSheet
linkStatusDescr = "worksheet absent"
Case xlLinkStatusNotStarted
linkStatusDescr = "yet to start"
Case xlLinkStatusOK
linkStatusDescr = "all okay"
Case xlLinkStatusOld
linkStatusDescr = "expired"
Case xlLinkStatusSourceNotCalculated
linkStatusDescr = "yet to compute"
Case xlLinkStatusSourceNotOpen
linkStatusDescr = "inactive source"
Case xlLinkStatusSourceOpen
linkStatusDescr = "active souorce"
Case Else
linkStatusDescr = "status undetected"
End Select
End Function
- Next, press F5 to run the code.
- Consequently, the Macro dialog box will appear.
- Choose RemoveLinks and press Run.
- Thus, it’ll return a new worksheet.
- There, you’ll see the Source unavailable in the outcome.
- At the same time, you’ll get a warning dialog box in that same worksheet.
- Press OK.
- At last, the broken links will get eliminated.
Read More: How to Break Links in Excel When Source Is Not Found (4 Ways)
3. Broken Links Removal from Named Ranges
Again, there might be broken links in the named ranges. Follow the process below to erase those links.
STEPS:
- First of all, go to the Formulas tab.
- Then select the Defined Names drop-down and choose Name Manager.
- The Name Manager dialog box will appear.
- Select the broken link.
- Afterward, press Delete.
- In this way, we can eliminate the broken links.
Read More: How to Find Broken Links in Excel (4 Quick Methods)
Conclusion
Henceforth, you will be able to Remove Broken Links in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
