Link Two Excel Workbooks and Fix Broken Links
Amir keeps branch sales in Pune_Sales.xlsx and a summary in Summary.xlsx. In the summary he types = and clicks the Pune total in the other file. Next month the branch file is renamed Pune_Sales_Oct.xlsx, and the summary shows a warning about links that cannot be updated. Linking is easy; keeping links healthy needs a few habits.
An external reference is a formula that reads a cell from another workbook, such as =[Pune_Sales.xlsx]Sheet1!$B$8. When the other file is closed, Excel shows the full folder path in the formula. The Data > Workbook Links pane (older versions: Data > Edit Links) lets you refresh, change or break these links.
The names and numbers in the examples are fictional teaching data.
Quick answer
- Open both workbooks.
- In the summary cell, type
=. - Switch to the other workbook (View > Switch Windows) and click the cell.
- Press Enter. Excel writes
=[Pune_Sales.xlsx]Sheet1!$B$8. - Save both files. Manage links later with Data > Workbook Links.
=[Pune_Sales.xlsx]Sheet1!$B$8
='C:\Reports\[Pune_Sales.xlsx]Sheet1'!$B$8
What the formula looks like
| Situation | Formula you see |
|---|---|
| Source file open | =[Pune_Sales.xlsx]Sheet1!$B$8 |
| Source file closed | ='C:\Reports\[Pune_Sales.xlsx]Sheet1'!$B$8 |
| Sheet name with a space | ='[Pune_Sales.xlsx]Sep Data'!$B$8 |
Excel uses absolute references ($B$8) for links. If you insert rows in the source file while the summary is closed, the link still points to B8 and can read the wrong number. Open both files when you change the source layout, or link to a named cell.
The security warning when you open the summary
- Excel shows a yellow bar: links to external sources are disabled.
- Click Enable Content if you trust the files.
- To refresh, go to Data > Workbook Links (or Data > Edit Links) and click Refresh all / Update Values.
- If Excel says the source cannot be found, use Change Source below.
Fix a broken link (file renamed or moved)
- Go to Data > Workbook Links (older versions: Data > Edit Links).
- Select the broken link.
- Click Change source (or the three dots > Change source).
- Browse to the renamed or moved file and click Open.
- Check one linked number against the source file.
Break a link (keep the values only)
- Go to Data > Workbook Links.
- Select the link and click Break link (older: Break Link).
- Confirm. Every formula that used the link becomes its current value.
- Save the file under a new name first if you may need the links again; Break Link cannot be undone after saving.
Find hidden links that will not go away
- Names: open Formulas > Name Manager and look for names that refer to another file.
- Charts: a chart series can point at another workbook. Click the series and read the formula bar.
- Data Validation and Conditional Formatting: list sources and rules can point at another file.
- Find: press Ctrl + F, type
.xls, choose Within: Workbook and Look in: Formulas, then Find All.
Ravindra Bagale's Tip
Keep linked files in one shared folder and never rename them casually. If the month changes every time, keep one fixed file name like Pune_Sales.xlsx and archive old months under a different name.
Ravindra Bagale's Tip – मराठी
Linked files एकाच shared folder मध्ये ठेवा आणि उगाच rename करू नका. दर महिन्याला नाव बदलत असेल तर Pune_Sales.xlsx असं एक fixed नाव ठेवा आणि जुने महिने वेगळ्या नावाने archive करा.
Ravindra Bagale's Tip – हिंदी
Linked files एक ही shared folder में रखिए और बिना वजह rename मत कीजिए। हर महीने नाम बदलता हो तो Pune_Sales.xlsx जैसा एक fixed नाम रखिए और पुराने महीने अलग नाम से archive कीजिए।
Practice task
- Create Pune_Sales.xlsx with sales in B2:B7 and a total in B8.
- Create Summary.xlsx and link a cell to the Pune total.
- Close Pune_Sales.xlsx and look at the formula: the folder path appears.
- Rename Pune_Sales.xlsx, open Summary.xlsx and fix it with Change Source.
- Save a copy of Summary.xlsx and practise Break Link on the copy.
Learn it properly
Course lessons:
- 23.6 Links to Another Workbook (External References)
- 23.7 Workbook Links: Change Source, Break Links and Fix Broken Links
Related guides: Excel Formulas Across Sheets and 3D References (SUM Jan:Mar) · Named Ranges in Excel: Name a Cell or List and Use It in Formulas · Power Query Merge in Excel: Combine Two Tables Like VLOOKUP
Frequently asked questions
Where is Edit Links in Microsoft 365?
In current Microsoft 365 builds it is Data > Workbook Links, which opens a side pane. Older versions show Data > Edit Links.
Why does Excel keep asking to update links?
The workbook still has an external reference somewhere: a formula, name, chart or validation rule. Find it and break or fix it.
Do links work with files on OneDrive or SharePoint?
Yes, but both files should be opened from the same synced location so the path stays the same.
Is Power Query better than links?
For whole tables, yes. Power Query imports the data and refreshes cleanly. Links are fine for a few single cells.