Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.7 Workbook Links: Change Source, Break Links and Fix Broken Links

By Last updated:

Example first. Pune_Nov.xlsx was moved to a new folder, Nov-Final. Region.xlsx now says We couldn't get updated values from a linked workbook. Instead of rewriting formulas, Raja points the link at the new location with Change source. At the end of the year he breaks the links so the file keeps the numbers as plain values.

Workbook Links pane — in 30 seconds

What: Data › Queries & Connections › Workbook Links opens a pane listing every file this workbook links to, with Refresh, Change source and Break links.

Why: Moved or renamed source files are the number one reason a linked report shows old numbers. The pane fixes all formulas that use a source in one step.

Tiny example: Change source from C:\Reports\Nov\Pune_Nov.xlsx to C:\Reports\Nov-Final\Pune_Nov.xlsx: every formula that used the old path now uses the new one.

First look at the data (before → after):

Workbook Links pane: before and after tables, fictional Blinkit Maharashtra data

Expected result — After Change source, and after Break links

Cell After Change source After Break links
Region!B2 ='C:\Reports\Nov-Final\[Pune_Nov.xlsx]Summary'!$B$2 4,12,000 (a plain value)
Region!B3 ='C:\Reports\Nov\[Nashik_Nov.xlsx]Summary'!$B$2 (unchanged) 2,68,000 (a plain value)
Before table and formula as text

Before — Workbook Links pane before the fix

Source file Status
Pune_Nov.xlsx (C:\Reports\Nov) Unable to refresh: file not found
Nashik_Nov.xlsx OK
Nagpur_Nov.xlsx OK

Formula / click path

Data › Queries & Connections › Workbook Links
Pune_Nov.xlsx › … (More commands) › Change source › pick the moved file › Select
Year end: … › Break links  (formulas become values)

Do it step by step:

  1. Open Region.xlsx and click Data › Queries & Connections › Workbook Links.
  2. Find Pune_Nov.xlsx in the pane and click its … (More commands) button.
  3. Click Change source, browse to the Nov-Final folder, select Pune_Nov.xlsx and click Select.
  4. Click Refresh for that file and check that the status is OK.
  5. At year end, save a backup copy, then click … › Break links for each source (or Break all at the top).
  6. Click a linked cell and confirm the formula bar now shows a number, not a formula.

Older Excel versions have the same tools in a dialog box: Data › Queries & Connections › Edit Links (Update Values, Change Source, Open Source, Break Link, Check Status). In current Microsoft 365 you can still add the legacy Edit Links command to a custom ribbon group with File › Options › Customize Ribbon › All Commands.

Finding a link that will not go away. Break links removes links in cell formulas, but links can also hide in other places. Check these one by one:

Where a link can hide How to check
Cell formulas Home › Find & Select › Find (Ctrl + F), Find what: .xl, Look in: Formulas, Within: Workbook
Defined names Formulas › Name Manager, look at the Refers to column for [
Data Validation lists Select the cells, Data › Data Validation, check Source
Conditional formatting rules Home › Conditional Formatting › Manage Rules › This Worksheet
Chart series and titles Click each chart series and read the formula bar
Shapes and buttons with assigned macros Right-click › Assign Macro

Break links is permanent. Every formula that used the source becomes a number. Save a copy first (for example Region_with_links.xlsx) so you can rebuild next month.

Practice task (try it before you open the answer):

  1. Move Pune_Nov.xlsx to a new folder and open Region.xlsx.
  2. Fix the link with Change source.
  3. Save a copy of Region.xlsx, then break all links in the copy and check B2.
Answer

After Change source the Pune cell shows ₹4,12,000 again and the status is OK. In the copy, after Break all, B2 contains the plain number 4,12,000 and the Workbook Links pane is empty.

Ravindra Bagale's Tip

When a file keeps asking "update links?" but you see no links, search names and data validation first. That is where forgotten links live in nine files out of ten.