How to Break External Links in Excel
Updated 2026-06-03
Quick answer
To break external links in Excel, go to Data → Edit Links → Break Link (this converts linked values to static numbers, which is irreversible, so save a copy first). To find them, use Edit Links to list workbook-level links, but formula-level and hidden links (in names, charts, data validation) often hide from it. Formula Audit XL surfaces every external reference across the model so none silently changes your numbers.
External links in Excel connect your workbook to data in another file. When the source workbook changes, your numbers change too, sometimes without you noticing. In a financial model that is shared, versioned, or sent to a client, uncontrolled external links are a risk. A number that looks correct today can be pulled from a stale or moved source file tomorrow.
Why external links are dangerous in financial models
An external link like =[Budget2025.xlsx]Assumptions!B12 will silently pull a different value if someone renames the source file, moves it, or updates it without your knowledge. When the source file is unavailable, Excel either shows a stale cached value or a #REF! error, both of which can propagate through a model before anyone notices.
The three most common problems:
- Version drift. The model was built referencing a prior-year version of an assumptions file. The link still resolves but points to outdated data.
- Moved files. The source workbook is on a network share that has been reorganised. The link breaks silently.
- Distribution issues. You send the model to a client; they open it without access to your network, and every linked cell shows an error or a stale cached value.
Find external links via Data → Edit Links
Step 1. Go to the Data tab → Connections group → Edit Links.
If the button is greyed out, Excel has found no standard formula-based external links. That does not mean the file is clean. See the next section.
Step 2. The Edit Links dialog lists every external workbook that this file references. The Status column shows whether the link is currently accessible.
Step 3. To update a link to the latest values from the source file, select it and click Update Values.
Step 4. To break a link, select it and click Break Link. Excel replaces all formulas referencing that source with static values. This action is irreversible after saving. Always create a backup copy first.
The links that Edit Links misses
Edit Links is not comprehensive. It only catches standard formula-based references to external workbooks. The following types of external links can bypass it entirely:
| Link type | Where to look |
|---|---|
| Named ranges pointing to external files | Formulas → Name Manager: look for names with [ in the Refers To column |
| Chart data series referencing another workbook | Right-click each chart → Select Data → check source ranges |
| Data validation lists sourced from another file | Data → Data Validation: check the Source field on each validated cell |
| Conditional formatting rules referencing external cells | Home → Conditional Formatting → Manage Rules |
| OLE / embedded objects | Insert → Object: check for embedded spreadsheets or documents |
A thorough external-link audit requires checking all of these, not just running Edit Links.
How to break links safely
- Save a backup copy (File → Save As) before touching anything.
- Open Edit Links and note all sources.
- Decide for each source: break it (convert to static values) or update it (pull the latest data and keep the link).
- For each link you are breaking: select it → Break Link → confirm.
- After breaking, run a spot-check on key output cells to confirm values are unchanged.
- Search for hidden links (see the table above) and remove them manually.
Find every external reference with Formula Audit XL
Formula Audit XL‘s consistency check surfaces every external reference in the model (formula-based links, named ranges, and other hidden locations) in a single panel. Rather than navigating through Name Manager, individual charts, and data validation dialogs one by one, you see a consolidated list of every external dependency and can decide quickly which to break, which to update, and which to document.
Common pitfalls
- Breaking links when they should be updated. If the source workbook exists and is simply out of date, update the link rather than breaking it. Breaking it locks in a stale number.
- Not saving a backup. Break Link has no undo once you save the file. The formula is gone. A backup takes thirty seconds.
- Assuming Edit Links is complete. It is a starting point, not a complete audit. Named ranges and charts regularly harbour external links that Edit Links ignores.
- Links in the Personal Macro Workbook. If you use
PERSONAL.XLSB-stored macros that reference cells in other files, those links may surface in Edit Links even though they are macro-related, not model-related.
Related guides
- How to Find Circular References in Excel
- Excel Formula Auditing: The Complete Guide
- Inconsistent Formulas in Excel: Find and Fix
- How to Audit a Financial Model in Excel
Free tool: Excel Circular Reference Finder
Frequently asked questions
What does 'Break Link' actually do in Excel?
Break Link replaces every formula that references the external workbook with the current static value. The formula is gone and only the number remains. This is permanent and cannot be undone after you save. Always save a copy of the file before breaking links.
Why does the 'Edit Links' dialog still appear after I break all links?
Links can hide in named ranges, chart data series, conditional formatting rules, and data validation lists. Breaking workbook-level formula links via Edit Links does not remove these. You must locate and delete them manually or use an audit tool to surface them.
How do I find external links in formulas without Edit Links?
Use Find & Replace (Ctrl + H) with 'Find what' set to '[' (the character that appears in external references like =[OtherFile.xlsx]Sheet1!A1). Search in Formulas. This finds formula-based external links but misses links in names, charts, and validation.
Can I update external links instead of breaking them?
Yes. In the Edit Links dialog, select the source workbook and click 'Update Values' to pull the latest data. You can also set automatic update behaviour: click 'Startup Prompt' to choose whether to prompt, auto-update, or not update on open.
What happens if the source file is moved or renamed?
Excel shows a broken-link error (#REF! or a warning dialog on open) because it cannot locate the source. In Edit Links, click 'Change Source' to point the link at the new file location.