Excel workbooks that pull data from other files using external links are some of the trickiest to migrate. When you rename a source file from .xls to .xlsx, every workbook that references it has a broken link — until you update the connection.
An external link in Excel looks like this in a formula: ='C:\Reports\[budget.xls]Sheet1'!$A$1. The path, filename, and extension are all part of the reference. Change the filename from budget.xls to budget.xlsx and the link breaks.
External links are also used in named ranges, charts, pivot table sources, and conditional formatting rules — not just formulas. A workbook can have hundreds of external links scattered throughout.
The fastest way to see what a workbook is linked to: Data > Edit Links (or Queries & Connections in newer Excel). This shows every source file the workbook references.
If Data > Edit Links is greyed out, the workbook has no external links to other Excel files. It may still have links to databases or web sources, but those are handled differently.
For finding links buried in named ranges or charts, use Find & Replace (Ctrl+H) to search for .xls] across the entire workbook — this catches references that don't show up in Edit Links.
Before updating links in the dependent workbook, convert the source files first. If budget.xls links to data.xls, convert data.xls to data.xlsx first, then convert budget.xls and update its links.
Migration order: always convert the deepest dependencies first and work up the chain.
Method 1 — Edit Links dialog: Data > Edit Links > select the source > Change Source > browse to the new .xlsx file. Repeat for each source.
Method 2 — Find and Replace: Ctrl+H, search for budget.xls], replace with budget.xlsx]. This is faster when many formulas reference the same source file.
Method 3 — VBA batch update:
' Update all links from .xls to .xlsx in the current workbook
Dim lnk As Object
For Each lnk In ThisWorkbook.LinkSources(xlExcelLinks)
Dim newPath As String
newPath = Replace(lnk, ".xls]", ".xlsx]")
If newPath <> lnk Then
ThisWorkbook.ChangeLink lnk, newPath, xlExcelLinks
End If
Next lnkAfter migration, some links may no longer be needed — the data has been consolidated or the source file archived. Breaking these links converts the external reference to a static value:
Data > Edit Links > select the source > Break Link. The formulas will keep their last calculated values as static numbers.
After updating links, close and reopen the workbook without updating links on open. Then manually refresh: Data > Refresh All. Verify that all linked cells show current data, not the cached values from the last open.
LegacyLeaps maps all external link dependencies before conversion and updates connection strings automatically during migration.
Run Free ScanPractical fixes for legacy Excel and Access problems. No spam.