Broken Link
Executive Summary
Key Takeaways
- ✓ A broken link in a financial model is a formula reference that no longer resolves to a valid data source.
- ✓ Internal broken link.
- ✓ A broken external link that retains a cached value will silently propagate incorrect data through the model.
- ✓ - Internal broken links: search the workbook for `#REF!` and `#NAME?` error values - External broken links: examine the Edit Links dialog (Data > Edit Links) and check for links with unknown or error status; or use an automated structural audit tool that identifies all link types including cached-value cases
Definition¶
A broken link in a financial model is a formula reference that no longer resolves to a valid data source. The formula points to a cell, named range, worksheet, or external workbook file that has been deleted, renamed, moved, or made unavailable since the formula was written.
Types of Broken Links¶
Internal broken link. A reference to a cell, range, or named range within the same workbook that no longer exists. Most commonly displays as #REF! (deleted cell reference) or #NAME? (deleted named range). These errors are visible and detectable through standard error checking.
External broken link. A reference to a cell or range in a separate workbook file that is unavailable at its registered path. When Excel cannot locate the source file, it may display an error in the link management dialog but retain the last cached value in the referencing cell. This is the more dangerous form: the cell displays a number rather than an error, and the number may be significantly out of date.
Why It Matters¶
A broken external link that retains a cached value will silently propagate incorrect data through the model. Every calculation that depends on the affected cell will produce results based on an unverified historical value, without any visible indication that the data is stale.
Broken internal links that display #REF! are more immediately apparent but can cascade through many dependent cells, causing extensive visible errors in a model that was previously working.
Detection¶
- Internal broken links: search the workbook for
#REF!and#NAME?error values - External broken links: examine the Edit Links dialog (Data > Edit Links) and check for links with unknown or error status; or use an automated structural audit tool that identifies all link types including cached-value cases
Remediation¶
- For
#REF!errors: trace back to the original source and either restore the deleted reference or update the formula to reference the correct current source - For cached external links: locate the source file, restore the link, refresh the values, and verify the current values against the original source documentation
Continue Reading¶
Prerequisites¶
- Excel Financial Models — the parent pillar
Related Technical Guides¶
- Broken Links — the full technical guide covering detection and remediation in detail
- Cross Workbook Links — the guide to external link risks in multi-file models
- Formula Error Types — the taxonomy of error types including broken link errors
- Named Ranges — the guide to named range management including deletion risks
Related Products¶
- Financial Model Audit Engine (FMAE) — deterministic structural auditing referenced throughout this guide
How OXXON tests thisRun a free structural check with FMAE
Related Articles
Broken Links in Financial Models
Broken links in financial models are references to cells, ranges, or external files that no longer resolve correctly. They occur in two forms: internal broken links, where a cell references a named range, cell address, or worksheet that has been deleted or renamed within the same workbook; and external broken links, where a cell references a cell or range in a separate workbook file that is unavailable, moved, or renamed. Both types can cause formula cells to return errors or silently retain stale cached values, distorting model outputs without visible indication to the user.
Cross Workbook Links in Financial Models
Cross workbook links are formula references in one Excel workbook file that draw data from cells in a separate workbook file. When the source workbook is available and open, the link resolves dynamically. When the source workbook is closed or unavailable, Excel either updates the link by reading the file directly, or retains the last cached value without indicating that the displayed value may be stale. Cross workbook links introduce fragility through path dependency: any change to the name or location of the source file will break the link. In institutional financial models submitted for audit or lender review, cross workbook links that cannot be verified against available source files are a material structural finding.
Formula Error Types in Financial Models
Formula errors in financial models fall into four principal categories: visible error values (including #REF!, #VALUE!, #DIV/0!, #NAME?, #N/A, #NULL!, and #NUM!), which display in cells and are immediately apparent; silent formula errors, which produce plausible-looking values but incorrect results; structural formula errors, which arise from incorrect model construction rather than incorrect values; and logic errors, which occur when a formula correctly implements an incorrect financial relationship. Each category requires different detection methods and carries different risk implications.
Named Ranges in Financial Models
A named range in Excel is a cell or range of cells that has been assigned a descriptive name through the Name Manager, allowing formulas to reference the name rather than the cell address. Named ranges improve formula readability and reduce errors from incorrect cell referencing in large models. However, they introduce structural risks when they are deleted, renamed, or allowed to accumulate as an unmanaged inventory: formulas that reference a deleted named range display #NAME? errors, and formulas that reference a renamed named range silently reference the wrong range if the old name is reused with a different definition.