You open a workbook you have used for months and Excel puts up a dialog before the sheet renders: This workbook contains one or more links that cannot be updated. Click Continue and some cells show #REF!, while others still hold numbers that no longer match anything you can check. The file is not damaged. Excel is holding a path to a workbook that has moved.

An external reference is not a copy of the data. It is a path wrapped inside a formula:

=SUM('C:\Reports\2026\[Budget.xlsx]Q3'!$B$2:$B$40)

Excel writes that path as an absolute location at the moment you create the link, and it keeps pointing there afterwards. Rename the folder, move the workbook to a new drive, or open the file on a machine where that drive letter is not mapped, and the reference resolves to nothing.

Two behaviours decide what you see. Excel stores the last calculated result next to the formula, so a dead link still shows a number: the workbook looks fine and is quietly wrong. Automatic links also refresh on open, which is why the warning appears before you touch anything.

A second fault looks identical. If the path resolves but the cell, row or sheet it names was deleted, the reference itself is invalid and repointing will not rebuild it. Path problems are fixable in the dialog; reference problems are not.

Fix 1 — Read the link list before changing anything

Go to Data → Queries & Connections → Edit Links. Some recent Microsoft 365 builds label the button Workbook Links; on Mac it sits under Data → Edit Links.

You get one row per source workbook, with columns for Source, Type, Update and Status. Read the Source column: it shows the exact path Excel is chasing, and whether that is a local path or a OneDrive/SharePoint URL. Those behave differently when a colleague opens the file.

Do not click Break Link yet: it applies to every link to that source at once and replaces formulas with values.

Fix 2 — Repoint the link with Change Source

Select the entry, click Change Source…, browse to where that workbook lives now, and confirm. Excel rewrites the path for that source and leaves your formulas alone. Click Update Values to test: the Status column should stop reporting an error and the totals should move.

Work through the list one source at a time. Duplicate entries for the same workbook usually mean someone saved a copy to a new folder and half the formulas kept the old path; repoint both, then break whichever you no longer need.

Fix 3 — Put the file back where Excel expects it

Sometimes the cheapest repair is to satisfy the path instead of editing it. Recreate the folder shown in the Source box and drop the workbook in with the same name. Nothing in your file changes, so every copy other people hold keeps working, which matters on a shared model.

Watch drive letters when you repoint. A mapped Z: on your PC can be S: on someone else’s, and Excel stores whichever letter was in use. A UNC path such as \\fileserver\finance\Reports\Budget.xlsx survives that, and so does a SharePoint URL.

Fix 4 — Break the links when the data is historical

If the external numbers are a snapshot you will never refresh again, break the link on purpose rather than leaving a broken one in the file.

Open Edit Links, select the source, click Break Link, then Break Links in the confirmation. Excel converts every formula pointing at that source into its current value. The operation is permanent: no undo after you save. Use File → Save As first so the original stays intact.

For a single range, copy the cells and use Ctrl+Alt+V → Values. Check the numbers against the source first: a cached value that was already stale stays wrong forever.

Fix 5 — Stop the prompt without touching the data

To silence the dialog while keeping the links, open Edit Links → Startup Prompt…. Three options are offered.

Let users choose keeps the current behaviour. Don’t display the alert and don’t update automatic links opens the workbook silently with last-saved values until someone clicks Update Values. Don’t display the alert and update links attempts a refresh with no warning and no way to notice a failure.

The setting is stored in the workbook, so it travels with the file. The second option is the sensible default for a distributed report; the third hides exactly the failure you want to catch.

Fix 6 — Faults the dialog cannot reach

#REF! inside a formula. The path resolved, but the cell it named is gone. Change Source will not rebuild that; rewrite the formula or restore the deleted sheet from a copy, the same pattern as Excel #REF! after deleting a sheet.

PivotTable caches. A pivot built from another workbook does not appear in Edit Links. Use PivotTable Analyze → Change Data Source instead.

Named ranges. Open Formulas → Name Manager and look for names whose Refers To starts with a path or [1], the marker link conversion leaves behind. Delete the unused ones; they can keep a dead link alive after every visible formula is clean.

When it is not worth fixing

Stop when the links chain through files you no longer have. The cached values are then the only copy of the data, and the question is not how to repair a path but whether those numbers can be trusted. If they were refreshed after the final change to the sources, breaking the links is safe. If not, the model has to be rebuilt from whatever source data survives.

Also separate the two symptoms. A link warning is not corruption. If Excel instead reports unreadable content or offers to repair the file on open, you are dealing with a different problem and link tools will not help. When a workbook has lost its sources and its structure, send a copy for an assessment before you spend another evening on paths.

FAQ

Why do the numbers still show if the link is broken?

Because Excel cached the result of the last successful calculation. The value is a snapshot, not a live figure. Treat such a number as unverified until you compare it with the source.

Can a broken link corrupt the workbook?

No. A link is a text path inside a formula; a failed lookup returns an error value, not damage. Files that fail to open have a different cause.

Will the fix survive if I email the file?

Only the Change Source repair travels with it, and only as the path you chose. If the recipient does not have the source at that location, the link breaks again on their machine. Breaking links to values is the only repair that is portable.

Still stuck? Email the file to help@easyofficefix.com with the exact error text. You get an assessment and a flat price before any work starts.