How to find and fix broken links in Excel
Are non-working links causing havoc to your worksheets? Do not worry! This tutorial will teach you 3 easy ways to find and fix broken Excel links, plus our own one-click solution as an extra bonus :)
Excel cells may often link to other workbooks to pull relevant information from there. When a source workbook gets deleted, relocated, or damaged, external references to that file break down and your formulas start returning errors. Obviously, to fix the formulas, you need to find broken links. The question is how? The answers follow below :)
Find and fix broken links in Excel
To detect non-working links to other workbooks, perform the following steps:
- On the Data tab, in the Queries &Connections group, click the Edit Links button.
If this button is greyed out, that means there are no external references in your workbook.
- In the Edit Links dialog box, you can see a list of all external sources that your current workbook is referring to. When this window is opened for the first time, all the links have status Unknown. To clarify the status, click the Check Status button on the right-hand side:
- When the information gets updated, review the status of each link. For convenience, you can click the header of the Status column to sort the records accordingly.
Obviously, the links diagnosed as Error: Source not found are broken. In my workbook, there are two such links:
- Let's go ahead and fix the broken links. For this, select the source that reports an error and click the Change Source button on the right:
- In the Change Source window that opens, navigate through the folders on your computer until you find the correct workbook, select it, and click OK (or double-click the workbook):
- Repeat the above step for each problematic source individually. When done, click the Close button.
After fixing all erroneous sources, you may notice that your list of links has actually become shorter. The reason is that you might have had multiple occurrences of the same workbook, and after changing the source, the incorrect ones disappeared from the list.
For example, we had the following pairs that were referring to the same file: Colrado report.xlsx (misspelled) and Colorado report.xlsx (correct); Florida_report.xlsx (non-existent) and Florida report.xlsx (correct). After fixing the links, the incorrect sources are gone, and we now have this list:
Identify and correct broken links with Find and Replace
The Edit Links feature discussed above can help you quickly get a list of all external sources in a workbook, but it does not show which cells contain those external references. To identify such cells, you can use Excel's Find and Replace.
Find broken links to all or specific workbook
External links always point to another Excel file that has ".xl" as part of the filename extension such as .xls, .xlsx, .xlsm, etc. You can make use of this fact when searching for references to any outside workbooks. Or you can search for specific text (substring) within a particular workbook name. The detailed steps follow below.
- Press Ctrl + F to open the Find and Replace dialog. Or click Find & Select > Find… on the Home tab in the Editing group.
- In the Find and Replace dialog box, click the Options button.
- Depending on whether you want to find all external links in a workbook or only references to a specific file, type one of the following in the Find what box:
- To search for all links, type .xl.
- To search for links to a particular workbook, type that workbook name or its unique part.
- In the Within box, select Workbook to search on all tabs or Sheet to look in the current worksheet.
- In the Look in box, choose Formulas.
- Click the Find All button.
And now is the key part - analyzing the results.
If you searched for all external references, then click the header of the Value column to sort the results by value and scroll down the list. Broken links to other workbooks will have a #REF! error in the Value column.
If you searched for references to a specific workbook, simply review the results.
For example, we know that some cells in the summary report point to the non-existent file (Colrado report.xlsx) and we wish to know exactly which cells. So, we type the misspelled word colrado in the Find what box and hit Find All. As the result, Excel displays 3 such references in 2 different sheets. The #REF! errors in the Value column confirm that all three links are broken:
Fix broken links to a specific workbook
In the list of Find All results, you can click any item to navigate to the cell containing the link and edit each one individually. Or you can use the Replace All feature to correct all the occurrences of an invalid link at once. Here's how:
- In the Find and Replace dialog box, switch to the Replace tab.
- In the Find what box, type the incorrect file name or path.
- In the Replace with box, type the correct file name or path.
- Click Replace All.
As an example, let's replace a wrong workbook name Colrado report.xlsx with the right one Colorado report.xlsx. In this particular case, replacing just a single word (colrado) will also work. However, please keep in mind that a specified text will be replaced anywhere within the path string (the full path to a file is displayed if the source workbook is closed at the moment). So, the smaller piece of text you enter, the bigger the chance of a mistake.
Once the replacement is done, the #REF! errors disappear from the results and normal values are shown instead:
In a similar manner, you can replace the path to a source file. For example, if the source workbook was originally in the Documents folder, and then you moved it to the Reports subfolder in the same folder, you can replace \Documents\ with \Documents\Reports\.
Someone may say it's ridiculous to use Find and Replace for solving the broken links problem, but as far as I know this is the only inbuilt feature that can help you find cells containing broken links.
Check for broken links with VBA
The below code loops through every external reference in a workbook and tries to figure out if it is broken or not. To find external files, we utilize the LinkSources method. To identify broken links, the LinkInfo method is used.
Sub FindBrokenLinks()
linksDataArray = ActiveWorkbook.LinkSources(xlExcelLinks)
Dim reportHeaders() As String
Dim rangeCur As Range
Dim sheetCur As Worksheet
Dim rowNo As Integer
Dim linkFilePath, linkFilePath2, linkFileName As String
Dim linksStatusDescr As String 'https://docs.microsoft.com/en-us/office/vba/api/excel.xllinkstatus
Dim sheetReportName As String
sheetReportName = "Broken Links report"
linksStatusDescr = "File missing"
reportHeaders = Split("Worksheet, Cell, Formula, Workbook, Link Status", ", ")
rowNo = 1 'Header row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Evaluate("ISREF('" & sheetReportName & "'!A1)") Then
ActiveWorkbook.Worksheets(sheetReportName).Cells.Clear
Else
Sheets.Add.Name = sheetReportName
End If
Set sheetReport = ActiveWorkbook.Worksheets(sheetReportName)
For indI = 0 To UBound(reportHeaders)
sheetReport.Cells(rowNo, indI + 1) = reportHeaders(indI)
Next
For Each sheetCur In ActiveWorkbook.Worksheets
If sheetCur.Name <> sheetReport.Name Then
For Each rangeCur In sheetCur.UsedRange
If rangeCur.HasFormula Then
For indI = LBound(linksDataArray) To UBound(linksDataArray)
linkFilePath = linksDataArray(indI) 'LinkSrouces returns the full file path with the file name
linkFileName = Right(linkFilePath, Len(linkFilePath) - InStrRev(linkFilePath, "\")) 'extract only the file name
linkFilePath2 = Left(linksDataArray(indI), InStrRev(linksDataArray(indI), "\")) & "[" & linkFileName & "]" 'the file path with the workbook name in square brackets
linksStatusCode = ActiveWorkbook.LinkInfo( CStr(linkFilePath), xlLinkInfoStatus)
If xlLinkStatusMissingFile = linksStatusCode And (InStr(rangeCur.Formula, linkFilePath) Or InStr(rangeCur.Formula, linkFilePath2)) Then
rowNo = rowNo + 1
With sheetReport
.Cells(rowNo, 1) = sheetCur.Name
.Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
.Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
.Cells(rowNo, 3) = "'" & rangeCur.Formula
.Cells(rowNo, 4) = linkFilePath
.Cells(rowNo, 5) = linksStatusDescr
End With
Exit For
End If
Next indI
For Each namedrangeCur In Names
If InStr(rangeCur.Formula, namedrangeCur.Name) Then
linkFilePath = ""
linksStatusCode = -1
If 0 < InStr(namedrangeCur.RefersTo, "[") Then
linkFilePath = Replace( Split( Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(0), "[", "")
linksStatusCode = ActiveWorkbook.LinkInfo( CStr(linkFilePath), xlLinkInfoStatus)
End If
If xlLinkStatusMissingFile = linksStatusCode Then
rowNo = rowNo + 1
With sheetReport
.Cells(rowNo, 1) = sheetCur.Name
.Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
.Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
.Cells(rowNo, 3) = "'" & rangeCur.Formula
.Cells(rowNo, 4) = linkFilePath
If 0 < Len(linkFilePath) Then
.Cells(rowNo, 5) = linksStatusDescr
End If
End With
End If
Exit For
End If
Next namedrangeCur
End If
Next rangeCur
End If
Next
Columns("A:E").EntireColumn.AutoFit
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
A list of invalid links is output in a new worksheet named Broken Links report. Column B has a hyperlink to the cell containing the link.
You can insert the code in your own workbook or download our sample file with the macro as well as the step-by-step instructions on how to use it.
Find broken links in Excel with a click
While reading through the first part of this tutorial, you might feel a little discouraged that there is no simple way to find all broken links in a file, say by clicking a single button. Though such a solution does not exist in Excel, nothing prevents us from developing it ourselves :)
For users of our Ultimate Suite, we do provide a one-click tool to find all external references in a workbook or only broken links. Simply click the Find Links? button on the Ablebits Tools tab, and you'll immediately see a list of all links in the current workbook, where invalid ones are highlighted in light red. To limit the list to not working links, select the Broken links only checkbox.
Clicking a cell address on the add-in's pane will take you to a cell that contains a particular link. That's all there is to it!
Unlike the VBA code above, the add-in finds all kinds of broken links including those where a sheet is missing or mistyped.
This is how to check broken links in Excel. I thank you for reading and hope to see you on our blog next week!
Available downloads:
Ultimate Suite 14-day fully-functional version (.zip file)
VBA macro to find broken links (.xlsm)
