AppGarden External Link and Dependency Mapper logo AppGarden

External Link and Dependency Mapper

A workbook full of #REF! after a source file moved: how to trace it in Excel™

By Paul Kemp · Published

Someone reorganises a shared folder. Your workbook opens with a prompt about links, and a few formulas now show #REF!. Others show stale numbers. The cause is a linked file that has moved, been renamed or been deleted.

Three states a link can be in

  1. Working: Excel™ can still reach the source.
  2. Stale: Excel™ is showing the last value it stored, because the source is unavailable or refresh is set to manual.
  3. Broken: the reference no longer resolves, and the formula or name shows #REF!.

A stale link looks fine. It is the one most likely to mislead.

Trace by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Choose Home → Find & Select → Go To Special, tick Formulas → Errors and press OK. This selects every cell showing an error.
  2. Open Formulas → Name Manager. Names showing #REF! are broken.
  3. Open Data → Edit Links and check the status column.
  4. If the file moved, select the link and use Change Source to point at the new location.
  5. If the file is gone for good, Break Link turns the formulas into their last stored values. It cannot be reversed in the file, so save a copy first.
  6. After repairing, check the cells that used to show errors.

See the links and their impact

External Link and Dependency Mapper lists problems in order of severity: references already broken, links that will break for someone else, links that work today, and notes about what could not be inspected. Each location can be selected from the pane. It does not read a file's modified date, so "stale" means exactly three things: links set to manual refresh, a linked formula in error, or a reference that no longer resolves. Scanning and reading the report are free.

See External Link and Dependency Mapper for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. External Link and Dependency Mapper is not affiliated with or endorsed by Microsoft.