Find and Break External Links in Excel

To break links in Excel, open Data > Edit Links (called Workbook Links in Microsoft 365), pick the linked file and choose Break Link. Excel replaces each formula that used that file with its current value. Before you do that, it pays to find every link, because some hide outside cell formulas. This guide shows how to find external links, decide whether to keep them, and remove them cleanly, for anyone who inherited a workbook that keeps asking to "update links".

What is an external link in Excel?#

An external link is a formula that reads a value from a different workbook (a separate Excel file). While the other file is open, the link looks like this:

='[Budget.xlsx]Q1'!B4

The file name sits in square brackets, followed by the sheet name, an exclamation mark and the cell. When the other file is closed, Excel adds the full folder path in front of it. Links appear when you copy a sheet or cells from another file, or when someone builds a lookup against another workbook. The guide to VLOOKUP from another workbook shows how these formulas are made, and workbook vs worksheet explains the difference between a file and a tab.

Links to other sheets in the same file, like =Rates!C3, are not external links. They never cause update prompts. If they are new to you, read how to reference another sheet or practise cross-sheet references.

How to tell if a workbook has external links#

The workbook usually tells you itself. Common signs are:

• A yellow security bar when the file opens, saying automatic update of links has been disabled.
• A message asking whether to update the links in the file.
• Numbers that don't change when you expect them to, or that show #REF! after a file was moved.

To see the full list of linked files, go to Data > Queries & Connections > Edit Links. In current Microsoft 365 versions the button is named Workbook Links and opens a pane instead of a dialog. If the button is grayed out, the workbook has no links that Excel can list, but there may still be hidden ones (see below).

How to find every cell with an external link#

Find and Replace can list every linked cell in one go:

1. Press Ctrl+F (Mac: ⌘F) to open Find.
2. In Find what, type .xl. Every link to an Excel file contains .xlsx, .xlsm or .xls.
3. Click Options. Set Within to Workbook and Look in to Formulas.
4. Click Find All.

The list at the bottom shows the sheet, cell address and formula of each match. Click a row to jump to that cell. Press Ctrl+A in the list to select all the matches at once, then give them a fill color so you can review them later.

Searching for [ also works, because external references use square brackets. Table formulas like Sales[Amount] use brackets too, so .xl gives fewer false matches.

How to break links in Excel#

Breaking a link turns each linked formula into a plain value. Do it when the numbers are final and the source file is no longer needed:

1. Save a copy of the workbook first. Break Link can't be undone with Ctrl+Z.
2. Go to Data > Edit Links (or Workbook Links).
3. Select the source file. Hold Ctrl (Mac: ⌘) to select several.
4. Click Break Link, then confirm. In the Workbook Links pane, open the menu next to the source and choose Break links.

After that, ='[Budget.xlsx]Q1'!B4 becomes, say, 48,500. The value stays the same, but it will never update again.

If you only need a few cells fixed, you can also copy them and use Paste Special > Values on top of themselves. That removes the link from those cells and leaves the rest alone.

Change the source instead of breaking the link#

Sometimes the link is wanted and only the file has moved or been renamed. Then choose Change Source in Edit Links (or the pane's menu) and pick the new file. Every formula that pointed at the old file now points at the new one. This is the fix when links show #REF! or old numbers after a folder change. The article on the #REF! error covers other causes of that error, and the lookup errors lesson practises catching errors with IFERROR and IFNA.

Why can't I break a link? Where hidden links hide#

If Edit Links still lists a source after you break it, or Find finds nothing, the link lives outside the cells. Check these places:

Names are the most common culprit. When you copy a sheet from another workbook, its names come along and keep pointing at the old file. The guide to formula auditing in Excel shows more tools for tracing where a formula gets its data.

How to keep links under control#

Links are useful in live reporting, but they break easily. A few habits help:

• Keep linked files in one shared folder and don't rename them.
• Prefer one input sheet with pasted or imported data over dozens of cell-by-cell links.
• List the linked files in a note on the first sheet, so the next person knows where the data comes from.
• Before sending a file outside your team, break links or paste values, so readers don't see prompts or private folder paths.

Practise it#

Most link problems start with references between sheets and files, and end with an error that needs handling. Try the audit exercise on this page, then practise cross-sheet references in a live workbook and practise fixing lookup errors. Each step checks your formula instantly.