ExcelInsider Team
Broken links in Excel occur when formulas, charts, or data references point to sources that are missing, invalid, or no longer accessible. Excel allows users to easily locate and deal with broken links using various built-in tools and functions.
Follow the steps below to efficiently find broken links in Excel:
➤ In your dataset, from the main menu head to Data >> Workbook Links.
➤ You should now see the Workbook Links Pane appear, showing details of all broken links detected within the dataset.
In this article, we will learn four effective methods of finding broken links in Excel.
Find Broken Links Using the Workbook Link Feature
In the sample dataset, we have a worksheet called Sales Data containing information about Product name, Quantity, Unit Price and Total cost. In the dataset, we can see some of the cells in column D are showing the #REF! error caused by broken links. Using Excel’s Workbook Links feature, we will now identify and locate these broken links. The modified dataset will be stored in a separate worksheet called “Workbook Link”.
Workbook Link in Excel is a useful tool that helps users identify, manage, and fix links between different workbooks. This feature is particularly helpful when dealing with external references or troubleshooting broken links.
Steps:
➤ Open the Workbook Link worksheet and head to Data >> Workbook Links from the main menu.
➤ The Workbook Links Pane should now appear on the right side of your dataset, showing details of all detected broken links.
Note:
Using the Workbook Links pane, you can break all links to permanently remove connections to external workbooks.
Find Broken Links Using the Find and Replace Tool
The Find and Replace tool is a well-known feature in Excel that enables users to quickly find specific data and replace it with new information throughout the entire dataset. We can also use this tool to effectively identify broken links within a worksheet.
Working with the same dataset, we will now use the Find and Replace tool to find broken links. The updated dataset will be displayed in a separate “Find and Replace” worksheet.
Steps:
➤ Open the Find and Replace worksheet and press Ctrl + F to open the Find and Replace dialogue box.
➤ In the Find and Replace dialogue box, type “.xl” in the Find what text box, set the Within parameter to Sheet, and the Look in option to Formulas, then click Find All.
➤ You should now see all broken links displayed in the search results pane.
Note:
You can click on any individual result to jump directly to the cell containing the broken link.
Identify Broken Links Using the VBA Editor
VBA Editor is a powerful built-in tool in Excel that allows users to run custom macros and scripts to automate tasks. Unlike previous methods, this method offers greater flexibility but requires basic VBA knowledge, making it more challenging for casual users.
Working again with the same dataset, we will use the VBA Editor to determine broken links. We will display the updated dataset in a separate “VBA Editor” worksheet.
Steps:
➤ Open the VBA Editor worksheet and press Alt + F11 to open the VBA Editor window.
➤ From VBA Editor main menu, go to Insert >> Module and paste the following code:
Sub FindBrokenLinks()
Dim linkSources As Variant
Dim i As Long
linkSources = ThisWorkbook.linkSources(Type:=xlLinkTypeExcelLinks)
If IsEmpty(linkSources) Then
MsgBox "No external links found.", vbInformation
Exit Sub
End If
For i = LBound(linkSources) To UBound(linkSources)
MsgBox "Broken or external link found: " & linkSources(i), vbExclamation
Next i
End Sub
Note:
This VBA macro checks the current workbook for any external links. If no external link is found, it shows a message informing users that there are no external links. If any external link is found, it shows a message box for each broken or external link, helping users to quickly spot where the issues are.
➤ Next, press Ctrl + Q to close the VBA Editor window, then press Alt + F8 to open the Macro dialogue box and run the macro.
➤ Message boxes will pop up one after another, each displaying a broken or external link found in the workbook. Click OK on each to proceed.
Check for External Links Using Document Inspector
The Document Inspector in Excel is used to check for hidden or personal information in a workbook before sharing it. Unlike the previous methods, this tool can not directly identify broken links. Instead, it helps detect the presence of external links, allowing us to locate and fix them.
Again, working with the same dataset, we will locate external links using the Document Inspector feature to find potential broken links. The modified dataset will be stored in a separate “Document Inspector” worksheet.
Steps:
➤ Open the Document Inspector worksheet and from the main menu, click on File.
➤ Next, head to Info >> Check for Issues >> Inspect Document.
➤ When the warning prompt appears, click Close to continue.
➤ Uncheck all options in the Document Inspector dialogue box except for Links to Other Files, then click Inspect.
➤ The inspection result should now display whether any external links exist in your dataset.
Frequently Asked Questions
Can Broken Links Affect Workbook Performance?
Yes, broken links can slow down your workbook by causing delays in calculations and data updates. This reduces performance and can lead to errors or unreliable data.
Which Method is More Suitable For Large Datasets?
If you are dealing with large datasets, the Workbook Link and VBA editor methods would be the best. Workbook Link gives a clear overview, while VBA automates the search, saving time and improving efficiency.
Concluding Words
Knowing how to find broken links in Excel is a must if you want to maintain data accuracy and keep your workbook running smoothly. In this article, we have discussed four effective methods of finding broken links in Excel, including using the Workbook Link, Find and Replace tool, VBA editor and Document Inspector. Feel free to try out all these methods and select one that best meets your needs.
