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 :)
To detect non-working links to other workbooks, perform the following steps:
If this button is greyed out, that means there are no external references in your workbook.
Obviously, the links diagnosed as Error: Source not found are broken. In my workbook, there are two such links:
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:
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.
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.
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:
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:
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.
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.
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!
Ultimate Suite 14-day fully-functional version (.zip file)
VBA macro to find broken links (.xlsm)