Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Find External Links and Broken References in a Workbook with Python

Audit a workbook for links to other files and for #REF! errors — in cell formulas, defined names, data validation and conditional formatting — with openpyxl and the zip file itself, then report them or replace them with values.
Formulas in workbooksErrors & fixesopenpyxl

A workbook that points at other workbooks is fragile. Move the source file, rename a folder or email the report to someone outside the network, and every linked cell shows stale numbers or #REF!, while Excel greets the reader with a security prompt about links it cannot update. Broken references are the same problem from the inside: a deleted sheet leaves #REF! in every formula and named range that used it. Both are easy to miss by eye in a large file, and both are easy to find with Python. This guide, part of Working with Excel Formulas in Python, builds an audit that looks everywhere links and broken references hide.

The places external links and broken references hide in a workbook A workbook can carry external links or Audit report every link and #REF! Cell formulas [1]Data!B2 and #REF! Defined names named ranges Data validation list sources Conditional formatting rule formulas externalLink parts file paths Excel's "this workbook contains links" prompt can be triggered by any of these, not just by cells

Prerequisites

Bash
pip install openpyxl pandas

To have something realistic to audit, build a workbook with one external link, formulas that use it, a formula broken by a deleted sheet, a broken named range, and validation and formatting rules that reference both:

Python
from openpyxl import Workbook, load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.packaging.relationship import Relationship
from openpyxl.styles import Font
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.workbook.external_link.external import ExternalBook, ExternalLink
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Report"

link = ExternalLink(externalBook=ExternalBook(id="rId1"))
link.file_link = Relationship(type="externalLinkPath", Target="Budget_2026.xlsx",
                              TargetMode="External", Id="rId1")
wb._external_links.append(link)

ws["A1"], ws["B1"] = "Budget", "=[1]Data!B2"
ws["A2"], ws["B2"] = "Budget total", "=SUM([1]Data!B2:B13)"
ws["A3"], ws["B3"] = "Variance", "=#REF!-B1"
ws["A4"], ws["B4"] = "Actual", 1250
wb.defined_names["OldRates"] = DefinedName("OldRates", attr_text="#REF!$B$2:$B$9")
wb.defined_names["BudgetLines"] = DefinedName("BudgetLines", attr_text="[1]Data!$A$2:$A$13")

dv = DataValidation(type="list", formula1="=[1]Lists!$A$1:$A$5")
ws.add_data_validation(dv)
dv.add("C1")
ws.conditional_formatting.add("B4", FormulaRule(formula=["B4>#REF!"], font=Font(bold=True)))
wb.save("report.xlsx")

Step 1 — List the linked workbooks

External links live in their own parts of the package. openpyxl loads them into the workbook's external links list, each with the path of the linked file:

Python
wb = load_workbook("report.xlsx")
targets = {i: ln.file_link.Target for i, ln in enumerate(wb._external_links, start=1)}
print(targets)          # {1: 'Budget_2026.xlsx'}

The number is what formulas use: [1] means the first entry. The attribute starts with an underscore — it is not part of openpyxl's documented API — so for a check that must not break on an upgrade, read the same information from the zip file directly:

Python
import re
import zipfile

def link_targets(path: str) -> dict[int, str]:
    out = {}
    with zipfile.ZipFile(path) as z:
        for name in z.namelist():
            m = re.match(r"xl/externalLinks/_rels/externalLink(\d+)\.xml\.rels$", name)
            if m:
                rels = z.read(name).decode()
                out[int(m[1])] = re.search(r'Target="([^"]+)"', rels)[1]
    return dict(sorted(out.items()))

print(link_targets("report.xlsx"))   # {1: 'Budget_2026.xlsx'}

Step 2 — Scan every place a formula can live

Cell formulas are only one place. Defined names, data validation and conditional formatting all hold formula text, and all of them can point outside the workbook or at #REF!. One scanner covers them, classifying each hit:

Python
EXTERNAL = re.compile(r"\[(\d+)\]")

def classify(text: str) -> list[str]:
    kinds = []
    if EXTERNAL.search(text):
        kinds.append("external")
    if "#REF!" in text.upper():
        kinds.append("broken")
    return kinds

def audit(path: str) -> list[dict]:
    wb = load_workbook(path)
    targets = link_targets(path)
    found = []

    def record(where, location, text):
        for kind in classify(text):
            ids = [int(i) for i in EXTERNAL.findall(text)]
            found.append({"where": where, "location": location, "kind": kind, "formula": text,
                          "linked_file": ", ".join(targets.get(i, f"[{i}]?") for i in ids)})

    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("="):
                    record("cell", f"{ws.title}!{cell.coordinate}", cell.value)
        for dv in ws.data_validations.dataValidation:
            record("data validation", f"{ws.title}!{dv.sqref}", str(dv.formula1 or ""))
        for rng, rules in ws.conditional_formatting._cf_rules.items():
            for rule in rules:
                for f in rule.formula or []:
                    record("conditional format", f"{ws.title}!{rng.sqref}", f)
    for name, dn in wb.defined_names.items():
        record("defined name", name, dn.attr_text or "")
    return found

import pandas as pd

report = pd.DataFrame(audit("report.xlsx"))
print(report[["where", "location", "kind", "linked_file"]].to_string(index=False))
The audit report as a table The audit lists each finding with where it was found, its location, whether it is an external link or a broken reference, and the linked file. Two cell formulas and a defined name use Budget_2026.xlsx, the data validation list points at it too, and one cell, one defined name and one conditional format contain A B C D 1 where location kind linked file 2 cell Report!B1 external Budget_2026.xlsx 3 cell Report!B2 external Budget_2026.xlsx 4 cell Report!B3 broken 5 data validation Report!C1 external Budget_2026.xlsx 6 conditional format Report!B4 broken 7 defined name OldRates broken 8 defined name BudgetLines external Budget_2026.xlsx seven findings — only three of them are in ordinary cells

The report makes the point of the exercise: of seven findings, three are in ordinary cells. Somebody searching the sheet with Find would never see the validation rule, the formatting rule or the two named ranges, and those are exactly the leftovers that keep Excel's link warning alive after the "last" link has been removed.

Step 3 — Write the audit where people will see it

An audit is only useful if the owner of the workbook reads it. Write the findings to a new workbook alongside the original, sorted with broken references first, since those are already producing wrong numbers:

Python
report["kind"] = pd.Categorical(report["kind"], ["broken", "external"], ordered=True)
report = report.sort_values(["kind", "where", "location"])
with pd.ExcelWriter("report_audit.xlsx", engine="openpyxl") as writer:
    report.to_excel(writer, sheet_name="Findings", index=False)
    writer.sheets["Findings"].freeze_panes = "A2"
print(len(report), "findings written")    # 7 findings written

When the linked workbook has simply moved or been renamed, the fix is to change the path rather than the formulas. Every formula refers to the link by its number, so updating the one relationship target repairs all of them at once:

Python
wb = load_workbook("report.xlsx")
wb._external_links[0].file_link.Target = "Budget_2026_final.xlsx"
wb.save("report_repointed.xlsx")
print(link_targets("report_repointed.xlsx"))   # {1: 'Budget_2026_final.xlsx'}

Relative paths like Budget_2026_final.xlsx resolve against the folder of the workbook that holds the link, which is usually what you want when both files travel together. Use an absolute or UNC path only when the source lives in a fixed shared location.

Choosing a fix for each finding in the audit If an external link points at a file that has moved, repoint the link target. If the report must stand alone, break the link by replacing formulas with their cached values. If a reference is broken with What kind of finding is it? link, file moved Repoint change one Target path link, must stand alone Break the link formulas become values #REF! Rebuild or delete the target no longer exists

A #REF! cannot be repointed automatically: the reference that used to be there was destroyed when its sheet or range was deleted, and nothing in the file records what it pointed at. Those findings need a person — the audit's job is to make sure somebody knows they exist.

When the report should stand alone — before emailing it outside the organisation, for example — replace every formula that uses an external link with the value Excel last calculated for it. Load the workbook twice: once for formulas, once with data_only=True for the cached values:

Python
def break_links(path: str, out: str) -> int:
    formulas = load_workbook(path)
    values = load_workbook(path, data_only=True)
    changed = 0
    for ws in formulas.worksheets:
        cached = values[ws.title]
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and EXTERNAL.search(cell.value):
                    cell.value = cached[cell.coordinate].value
                    changed += 1
    formulas._external_links = []
    formulas.save(out)
    return changed

print(break_links("report.xlsx", "report_standalone.xlsx"))   # 2

A file written by Python has no cached values, so on this example the two broken-out cells become empty; on a workbook last saved by Excel they get the numbers Excel displayed. Clear or fix the link-carrying defined names, validation rules and formatting rules as well — the audit from Step 2 lists them — or Excel will keep asking about links. The general technique is covered in Convert Excel Formulas to Values with Python.

Common pitfalls

  • Checking cells only. Named ranges, validation and conditional formats are where most orphaned links survive.
  • Case of #REF!. Files saved by some applications store it as #ref!. Compare case-insensitively, as classify does.
  • Chart series. Charts can reference other workbooks too. openpyxl does not expose chart references reliably; for a complete audit, search the xl/charts/ XML in the zip for [ followed by a number.
  • Hidden defined names. Some add-ins create hidden names that hold links. wb.defined_names includes them; Excel's Name Manager does not show them.

Performance and scale notes

The scan visits every cell once and is fast for typical reports — a few seconds for a workbook of a few hundred thousand formulas. For a folder of files, run it in a loop and concatenate the reports, adding the file name as a column, to produce one inventory of every link in a shared drive; the zip-level link_targets function alone is enough for a quick first pass, because it does not parse any cells. The folder loop pattern is shown in Process Multiple Excel Files in Parallel with Python.

Conclusion

External links are numbered parts of the package that formulas reference as [1]Sheet!A1; broken references show up as #REF!. Both can hide in cells, defined names, data validation and conditional formatting, so audit all four, map link numbers to file paths, and write the findings where the owner will see them. Repoint a moved source by changing one relationship target, and break links for standalone copies by replacing linked formulas with their cached values and clearing the other places links live.

Frequently asked questions

How are external links stored in an xlsx file? Each linked workbook gets an externalLink part inside the package, and formulas refer to it by number — 1Data!B2 means cell B2 on sheet Data of the first linked workbook. The file path lives in the relationship file for that part.

Why does Excel say the workbook contains links when I cannot find any? Links often hide outside cell formulas — in defined names, data validation lists, conditional formatting rules, chart series or leftover named ranges from copied sheets. Scan all of those, not just the cells.

Can openpyxl update the path of an external link? Yes. Each entry in the workbook's external links list has a file_link relationship whose Target is the path; change it and save. It is an undocumented attribute, so test after upgrading openpyxl.

What does it mean to break a link? Replacing each formula that uses the link with the value it last calculated. The workbook then no longer depends on the other file, at the cost of no longer updating.

More on formulas in workbooks

More on formulas in workbooks →