← Back to Error Directory

Broken External Workbook Links in Excel — Complete Fix

That persistent "update links" prompt has a specific tool built to resolve it — most people just never find it.

Table of Contents

1. Understanding the "Update Links" Prompt

When a workbook contains formulas referencing cells in a separate, external workbook file, Excel shows a security-related prompt on open, asking whether to update those linked values from the external source now. This exists because blindly refreshing external data automatically could theoretically be used to inject unexpected data into a workbook — the prompt gives you explicit control over when that external refresh happens.

2. Interactive Demo: Edit Links Status Simulator

See exactly what the Edit Links dialog reports for different link health scenarios.

  1. Go to the Data tab.
  2. Look for Edit Links in the Queries & Connections group (its exact location varies slightly by Excel version — sometimes under a "Links" or "Connections" button).
  3. The dialog lists every external workbook the current file references, along with each one's current status (OK, or an error indicating the source can't be found).

This is the single most useful diagnostic tool for external link issues — most people never discover it exists, and instead struggle with individual #REF! errors scattered throughout a workbook without realizing they all trace back to one or two broken external file connections.

4. Fixing a Link by Updating the Source Location

If the external file was moved or renamed but still exists somewhere, select the affected link in the Edit Links dialog and click Change Source, then browse to and select the file at its new location or new name. Excel updates every formula referencing that external file to point to the new path automatically — you don't need to manually edit each individual formula.

If you no longer need the external connection — for example, distributing a finalized report where the underlying live source shouldn't need to keep updating — select the link in Edit Links and click Break Link. This converts every formula referencing that external workbook into its last successfully calculated static value, permanently removing the dependency. This action cannot be undone through Excel's normal Undo function once the file is saved, so confirm you genuinely want frozen values before proceeding.

External links don't only live in visible cell formulas — they can also hide inside Defined Names (check Formulas → Name Manager for any name whose Refers To field mentions another file path) and inside chart source data ranges. If Edit Links shows a persistent broken link but you can't locate any visible formula referencing it, check both of these less obvious locations before concluding the link can't be found.

A very common way external links get created unintentionally: copying a range of cells from one open workbook and pasting it into another using a normal paste, where the copied cells contained formulas referencing other cells within their original workbook. Rather than adjusting those references to the new workbook's own data, Excel preserves the original external reference, silently creating a link you never explicitly intended. Use Paste Special → Values Only when copying between workbooks unless you specifically want a live external link.

8. Frequently Asked Questions

Why does Excel ask about updating links every time I open a file?

This prompt appears whenever a workbook contains formulas referencing another external workbook file, asking whether you want Excel to refresh those values from the external source now, or continue using the last saved cached values instead.

How do I find all external links in a workbook?

Go to Data tab, then Edit Links (in the Queries & Connections group, sometimes under a Links or Connections button depending on your Excel version), which lists every external workbook the current file references, along with each link's current status.

How do I permanently remove external links without losing the data?

In the Edit Links dialog, select the link and click Break Link, which converts all formulas referencing that external workbook into their last calculated static values, permanently removing the dependency on the external file.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.