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:
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.
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:
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.
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.
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.
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.
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!
Table of contents