Broken links in Excel are references to external workbooks that Excel can no longer find, often showing as #REF! errors or triggering a startup prompt. This guide shows you how to find every broken link in a workbook and fix or remove them safely, using built-in tools and a few manual checks.
What Causes Broken Links?
A link in Excel is a formula that references a cell in another workbook, like =SUM([Budget.xlsx]Sheet1!B2:B10). The link breaks when the source file is moved, renamed, deleted, or when the referenced sheet or named range no longer exists. Common scenarios:
- You email a workbook to a colleague but forget to send the linked files.
- A shared drive path changes after a server migration.
- The source workbook is saved with a different name or in a different folder.
- A linked workbook is deleted during cleanup.
- A named range in the source file is removed.
Excel does not automatically update or repair broken links. It simply cannot find the file, so it leaves the old path in the formula and may display a warning when you open the workbook.
How to Find Broken Links
Excel provides a built-in tool to list all external links, but it doesn’t tell you directly which ones are broken. You have to check each one manually. Here’s how to access the list:
- Go to the Data tab on the ribbon.
- In the Queries & Connections group, click Edit Links (older versions: Connections group > Edit Links).
- The dialog box shows every external source referenced in the workbook.
To see which of those sources are broken, click each link and look at the Status column. If it says OK, the link is valid. If it says Not available or Error: Source not found, that link is broken. You can also click Check Status to refresh the list.
How to Fix Broken Links
Once you’ve identified a broken link, you have three main options: repair it, break it (convert to values), or remove it entirely. The right choice depends on whether you still need the data.
Option 1: Repair the Link
If the source file still exists but has moved, you can point Excel to the new location:
- In the Edit Links dialog, select the broken link.
- Click Change Source.
- Browse to the correct file and select it.
- Click OK and then Close.
Excel will update all formulas that use that link to the new path.
Option 2: Break the Link (Convert to Values)
If you no longer need the live connection but want to keep the current values, break the link:
- In the Edit Links dialog, select the link.
- Click Break Links.
- Confirm when prompted.
This replaces every formula that referenced the external workbook with its current calculated value. The link is gone, and the data becomes static. Note that this action cannot be undone with Ctrl+Z, so save a backup first.
Option 3: Remove the Link
If the broken link is in a defined name, a chart series, or a conditional formatting rule, the Edit Links dialog may not show it. You must find and delete those references manually:
- Defined names: Go to Formulas > Name Manager, look for names with external references, and delete or edit them.
- Charts: Click the chart, check the formula bar for external references in the SERIES formula, and update or remove them.
- Conditional formatting: Go to Home > Conditional Formatting > Manage Rules, and check each rule’s formula.
Common Problems and Misconceptions
One frequent issue is that Edit Links shows no links, yet you still get a startup prompt about updating links. This usually means the link is hidden in a defined name, a chart, a shape, or a PivotTable cache. Use the manual checks above to find it.
Another misconception is that breaking a link will delete the data. It doesn’t – it just converts formulas to values. The data remains in the cells.
Also, links can break silently if the source file is open but the workbook is saved in a different format (e.g., .xlsx vs .xls). Excel may not recognize the path correctly.
How to Prevent Broken Links
Prevention is easier than repair. Follow these practices:
- Keep linked workbooks in the same folder and use relative paths when possible.
- Before moving or renaming a source file, update the links in the dependent workbook.
- Use Data > Edit Links > Startup Prompt to choose how Excel handles links when opening (e.g., ask, update, or don’t update).
- Periodically check for links and break any that are no longer needed.
- When sending a workbook, use File > Info > Edit Links > Break Links to remove external dependencies before sharing.
Using Excel’s Built-in Link Checker
Excel doesn’t have a dedicated “find broken links” button, but the Edit Links dialog is the closest thing. For a more thorough scan, you can use the Inspect Workbook feature:
- Go to File > Info.
- Click Check for Issues > Inspect Workbook.
- Select Links and click Inspect.
- The report will list any external links, but it won’t tell you if they’re broken – you still need to verify each one.
For a quick visual check, search for #REF! errors in your worksheet (Ctrl+F, type #REF!). These often appear where a link used to be.
When to Keep a Link vs. Break It
Keep a link if:
- You need the data to update automatically when the source changes.
- The source file is stable and accessible.
- You are the only user or you control the file paths.
Break a link if:
- You are sending the workbook to someone who won’t have access to the source.
- The source file is gone and you only need the current values.
- You want to avoid startup prompts and accidental data changes.
In general, if a link is broken and you can’t repair it, breaking it is the safest way to clean up your workbook.
Frequently Asked Questions
Why does Excel say “This workbook contains links to other data sources”?
This is a security and transparency warning. Excel is telling you that the workbook references external files. It appears even if the links are valid. You can suppress it by going to Data > Edit Links > Startup Prompt and choosing “Don’t display the warning and don’t update automatic links.”
Can I find broken links in a workbook without opening it?
No, you need to open the workbook in Excel to use the Edit Links tool. However, you can open the file in a text editor (like Notepad) and search for external file paths, but this is unreliable and not recommended for regular use.
Will breaking a link remove the data?
No. Breaking a link converts the external reference formula to a static value. The data that was last pulled from the source remains in the cell. It just won’t update anymore.
How do I fix a broken link that shows #REF! in a cell?
First, check if the link is in the Edit Links list. If it is, repair or break it. If not, the #REF! error might be from a deleted sheet or range. You’ll need to manually edit the formula to remove the #REF! part or re-create the reference.
Can I break multiple links at once?
Yes. In the Edit Links dialog, hold Ctrl and click each link you want to break, then click Break Links. Excel will break all selected links in one action.
Why can’t I see the Edit Links button?
The Edit Links button is only available when the workbook contains external links. If it’s greyed out, there are no links to manage. Also, in some Excel versions, it’s under Data > Queries & Connections rather than directly on the ribbon.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- How to Factory Reset iPad If You Forgot Password
- Ubuntu in Windows Store: Which Editions Work?
- Secure Guest Account on Windows 11: Step-by-Step
- Stop Outlook From Changing Hyperlink Font
- Hiberfil.sys: What It Is & How to Delete It
- How to Make a Mask on Snapchat: Step-by-Step