How to Find and Fix Broken Links in Excel Workbooks

How to find and fix broken links in Excel workbooks

Quick steps (Windows / Mac)

1. Data tab → Edit Links (or Data → Queries & Connections → Workbook Links).
   • Click Check Status to see which sources show Error: Source not found.
   • Select a broken source → Change Source → browse to the correct file → Change Source.
   • Or select the source → Break Link (converts formulas to values).

Find which cells contain external links

1. Press Ctrl+F, search for common workbook file extensions (e.g., .xls, .xlsx, .xlsm). Set Look in: Formulas to find external references.
2. Use Find & Replace to correct mistyped paths or workbook names inside formulas.

Hidden places Excel doesn’t show (and how to check)

• Named ranges: Formulas → Name Manager — inspect and update any names referencing external files.
• PivotTable caches / data connections: Data → Queries & Connections / Workbook Links. Change source or refresh.
• Charts and embedded objects: check chart series formulas and embedded OLE objects.
• Conditional formats, data validation, formulas in shapes or text boxes — inspect those manually or with VBA.

VBA checks (runs in Developer → Visual Basic → Insert Module)

• List PivotCache sources:

vb
Copy CodeCopied
Sub ListPivotCacheSources() Dim pc As PivotCache   On Error Resume Next   For Each pc In ThisWorkbook.PivotCaches

 
Debug.Print "SourceData: " & pc.SourceData Debug.Print "Connection: " & pc.Connection 
Next pc   On Error GoTo 0 End Sub 


List embedded chart series formulas:

vb
Copy CodeCopied
Sub ListEmbeddedChartFormulas()   Dim ws As Worksheet, co As ChartObject   On Error Resume Next   For Each ws In ThisWorkbook.Worksheets

 
For Each co In ws.ChartObjects   If co.Chart.SeriesCollection.Count > 0 Then     Debug.Print "Sheet: " & ws.Name & " | Chart: " & co.Name & " | Formula: " & co.Chart.SeriesCollection(1).Formula   End If Next co 
Next ws   On Error GoTo 0 End Sub 

Run these and check the Immediate Window (Ctrl+G) for paths to missing files.
When simple fixes don’t work

Search the workbook for the missing filename (or parts of it) with Look in: Formulas.
Inspect Name Manager, Data Connections, Power Query queries, PivotTables, charts, shapes, and VBA modules.
If you cannot locate the source, use Edit Links → Break Link to stop Excel trying to update (backup first — action is irreversible for formulas).

Preventive tips

Use consistent relative paths or store linked files in a shared network location.
Avoid hard-coded absolute paths in formulas; use structured references or central data sources.
Maintain a single source workbook and update links via Edit Links → Change Source when files move.

If you want, I can produce a short VBA script that searches all worksheets (cells, shapes, names, validations, conditional formatting, chart series, and VBA modules) and lists any external references.