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:
Note. This method of diagnosing broken links is not 100% reliable. For example, a #REF! error may be returned by a formula in the linked cell in the source workbook, and it will also be displayed in the Find All results. That does not mean, however, that the reference to that other workbook is broken. So, when using this approach, it stands to reason to click on each error and re-check the references manually.
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.
Note. After clicking the Replace All button, the Update Values window might open prompting you to choose the source workbook. Don't do that and simply click Cancel without selecting anything.
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 = xlCalculationAutomaticEnd 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.
Note. This code only finds links to invalid workbooks (non-existent, moved or deleted), but not missing sheets. The reason is that the LinkInfo method checks just the file name. An attempt to check a sheet name results in Error 2015.
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 (.exe file)
VBA macro to find broken links (.xlsm)
You may also be interested in:
- How to find links in Excel and break external links
- Excel HYPERLINK function with formula examples
- Hyperlinks in Excel: how to create, change and remove
- Excel cell reference: how to make and use
- How to quickly remove all unwanted hyperlinks from Excel
