How to Fix Broken External Links in Excel

What Are External Links in Excel?

An external link is a formula that references a cell in a different workbook file. They look like this in the formula bar:

Excel stores the full file path in the formula. When the source file is open, the formula pulls live data. When the source file is closed, Excel reads the last cached value that was saved in your workbook — which may be weeks or months out of date.

Why External Links Break

External links are fragile by nature. Any of the following will break them:

• The source file is moved to a different folder or drive
• The source file is renamed — even a single character change breaks the link
• The source file is deleted
• A referenced sheet inside the source file is renamed or deleted
• The file is moved to a different computer or server where the path structure is different
• SharePoint or OneDrive sync issues where the local path changes

The most dangerous scenario is a silent break: Excel has a cached value from the last time the link was live. The formula continues to show that old value without any error indicator — so you may be working with stale data without realising it.

How to Find All External Links

Method 1: Data → Edit Links

Go to Data → Edit Links (in Excel 365, this may be under Queries & Connections → Edit Links). This dialog shows every external workbook your file references. For each one, you can see:

• The source file name and path
• The status (OK, Error, or Unknown)
• When it was last updated

From this dialog you can Update, Change Source, Open Source, or Break Link.

Method 2: Find & Replace (Ctrl+F)

Press Ctrl+F, click "Options", and search for [ (an opening square bracket) in "Formulas". External link formulas always contain square brackets around the source file name. This method locates the exact cells, not just the source files.

Method 3: Use ExcelErrorFinder

Upload your file to ExcelErrorFinder. The audit engine scans every formula for external file references and reports them with the source file name, the exact cell, and a contextual explanation of the risk.

How to Fix Broken External Links

Option A: Update the Source Path

If the source file has been moved or renamed, you can update the link path:

1. Go to Data → Edit Links.
2. Select the broken link and click Change Source.
3. Browse to the new location of the source file and select it.
4. Click OK. All formulas referencing that source will be updated automatically.

Option B: Break the Link and Paste Values

If you no longer need live updates from the source file, the cleanest solution is to convert external links to static values:

1. Go to Data → Edit Links.
2. Select the external source.
3. Click Break Link. Excel will replace all formulas pointing to that source with their current values.
4. Confirm the action in the dialog that appears.

Alternatively, select the cells containing external links, copy them (Ctrl+C), and then use Paste Special → Values to replace the formulas with their current values in place.

Option C: Rebuild the Formulas Using Internal References

If the data that was in the external file should live in the current workbook, copy the relevant data into a new sheet within your workbook and update all formulas to reference the internal sheet instead. This is the most robust long-term solution.

The "Update Links" Prompt — What It Means

When you open a file with external links, Excel shows a security prompt asking whether to update links. This is because Excel is about to fetch live data from external files.

• "Update": Excel opens a connection to each source file and refreshes the values. If the source file is unavailable, it keeps the last cached value and may show an error.
• "Don't Update": Excel uses the last cached values from when the file was last saved. The data may be stale.

If you regularly click "Don't Update", the values in your workbook may drift increasingly out of sync with the source files — one of the silent data quality risks that ExcelErrorFinder flags.

Best Practices for External Links

• Avoid external links whenever possible. If the data belongs in your model, consolidate it into a single workbook. The fewer external dependencies, the more portable and reliable your file is.
• If you must use links, use named paths. On Windows, using a network drive letter (e.g., Z:\Reports\) is less stable than a UNC path (\\server\share\Reports\) because drive letters can change between users.
• Document every external link. Maintain a "Dependencies" sheet that lists every source file, its purpose, and who is responsible for keeping it updated.
• Audit before sharing. Before sending a workbook to another person, use ExcelErrorFinder to check for external links — the recipient likely cannot access your local file paths.
• Consider Power Query for live data. For workbooks that genuinely need live external data, Power Query (Get & Transform) provides a more controlled and auditable connection than formula-based links.

Frequently Asked Questions

Why does my file still show a "This workbook contains links" warning after I break all links?

Links can also exist in defined names, charts, form controls, and conditional formatting rules — not just cell formulas. Use the Name Manager (Ctrl+F3) to check for named ranges with external references, and inspect any charts for external data sources.

Can I automatically fix all broken links at once?

The "Break Link" option in Data → Edit Links removes all formulas referencing a given source file at once. For updating a moved source, "Change Source" updates all references to that file in a single step. Individual formula-by-formula editing is only needed when the links point to different source files with different new paths.

Will ExcelErrorFinder show me the full path of the external file?

ExcelErrorFinder extracts the file name from the formula. The full path depends on whether it's stored in the formula string (it will be for closed-workbook references). The audit results include the formula text so you can see the complete reference.