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.
- 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.
- 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.
- 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)
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.