How to Link Excel Workbooks: Reference Data Across Multiple Files

Applicable versions: Excel for desktop

Excel workbook linking allows data to be sourced from cells on the same worksheet, other worksheets or external workbooks. This powerful feature enables cross-workbook formulas, external data connections, and dynamic file references that update automatically. External cell references in this context are not the same as hyperlinks.

This has many benefits, for example:

• Automatic data synchronization across multiple files
• Reduced manual data entry and copy-paste errors
• Centralized data management for reporting
• Real-time updates from source files

1. What is Excel workbook linking?

Excel workbook linking is the process of creating external cell references to data in a different workbook. The dependent workbook is the one which contains the external references (e.g. formulas) to the data in the source workbook. Workbook linking ensures that the dependent workbook always references the most up-to-date information from external data sources.

External workbook references are dynamic, which means that if the data in the source workbook changes then these changes are reflected in the dependent workbook automatically.

a) How to reference other cells in the same sheet

To reference a cell on the same worksheet, the format is:

=F5

b) How to reference cell(s) on another worksheet sheet

When referencing another sheet within the same workbook, the format is:

Worksheet name ! Cell address

For example, to reference a cell on a different worksheet called January, use

=January ! G2

c) How to reference cell(s) on another workbook

The format for a full external cell reference in another workbook is:

[Workbook name]   Worksheet name   !   Cell address

For example, to create an external workbook reference to a different workbook called Sales_2025, use:

=[Sales_2025] Sales ! A1

Please note:

• If the workbook being linked to is CLOSED, the full pathname and the workbook name is used.
• If the workbook being linked to is OPEN, only the workbook name is used.
• When you create the link to the second workbook, ensure it is open first. When you close the second workbook, Excel adapts the formula reference for you by inserting the full pathname. As soon as you open up the second workbook again, the pathname is removed.

2. View and manage external workbook references

All external workbook references are managed through Excel’s Edit Links dialog box.

1Click the Data tab.

2Click the EDIT LINKS button on the Connections group.

This displays the Edit Links dialog box. If the Links option is not available in the Connections group (i.e. it is greyed out), then the current workbook does not link to any other workbooks.

3To update external data from the linked source workbooks, click Update Values button.

4To change the source reference, i.e. select a different file or a new location, click the Change Source button.

5To determine whether the source workbook is open, click the Check Status button.

6To open the source workbook, click the Open Source button.

7To break the links to all other workbooks, click the Break Links button. It is important to note that this option is all or nothing. It is not possible to break a link to one workbook but leave the links to the remaining links active. There are a number of reasons why a link may need to be broken:

• If the data in the source workbook is no longer needed, e.g. if the data has been consolidated into the dependent workbook.
• If data collection is complete, a report has been put together, and the figures must not change.

8When dealing with intermediary links, (e.g. A links to B and B links to C), ensure that all workbooks are open before changing anything.

• Click Start Prompt… to specify what message to give to the user and what actions to take regarding links, when the workbook is first opened.
• Changes made to the source workbook reflect in the dependent workbook automatically.

3. Troubleshooting external workbook references

When working with Excel workbook linking, you may encounter common issues that can disrupt your external data connections. Here are the most frequent problems and their solutions:

a) How to fix broken links

When source workbooks are moved, renamed, or deleted, Excel displays an error message to say that links are broken. To resolve this:

• Select the Data tab and choose Edit Links
• In the dialog, select the broken link and click Change Source
• Navigate to the new location of your source workbook
• Alternatively, click Break Links if you no longer need the external connection

b) How to resolve #REF! errors in external references

The #REF! error appears when Excel cannot locate the referenced cell or worksheet. Common causes include:

• Deleted worksheets: If the source worksheet was deleted, update your formula to reference the correct sheet.
• Renamed worksheets: Excel should update automatically, but manually edit the formula if necessary.
• Moved or deleted cells: Check that the cell range still exists in the source workbook.
• File access issues: Ensure you have permission to access the source workbook.

c) How to address performance issues with multiple external links

Too many external workbook references can slow down Excel performance. To optimize:

• Limit the number of external links: Consider consolidating data or using fewer source files
• Set links to manual update: In Edit Links, change from Automatic to Manual updates
• Close unnecessary source workbooks: Only keep essential files open during work
• Use Excel tables: Structured table references can be more efficient than specific cell references

d) How to manage security prompts for linked files

Excel may display security warnings when opening workbooks with external links. To manage these prompts:

• Click Enable Content if you trust the source files
• Use the Startup Prompt options to control when alerts appear
• Add trusted locations in Excel’s Trust Center settings (File menu > Options > Trust Center).

4. Frequently Asked Questions about Excel workbook linking

Can I link to a closed workbook?

Yes, Excel can reference closed workbooks, but the full file path must be included in the external cell reference. If you navigate to the workbook and select the cell(s) the correct external reference (including the filename) will appear in the formula.

When the source workbook is closed, Excel automatically adds the complete pathname to your formula. For example: C:\Documents\[Sales_2025.xlsx]Sheet1!A1.

When source workbook is open, Excel simplifies the reference back to just the workbook name, i.e. [Sales_2025.xlsx]Sheet1!A1.

How many external links can one workbook have?

There is no specific limit to the number of external workbook references you can create, but practical limitations include:

• Performance impact: Too many links can slow down file opening and calculation times.
• Memory usage: Each external connection uses system resources.
• More complex maintenance: More links mean more potential breaking points.

For optimal performance, limit external links to essential connections and consider consolidating data whenever possible.

Can I link to workbooks stored in the cloud?

Yes, but with limitations depending on your setup:

• OneDrive/SharePoint: External links work when files are synced locally.
• Excel Online: Limited external linking capabilities compared to desktop Excel.
• Google Sheets: Cannot directly link to Google Sheets from Excel.

What happens if I share a workbook with external links?

When sharing workbooks containing external references:

• Recipients need access: Others must have access to the same source files for links to work
• Path differences: File paths may be different on other computers which will break the links
• Consider breaking links: Convert to values using Paste Special > Values for static data sharing
• Use relative paths: Keep source files in the same folder structure when possible

How do I update external links automatically?

To ensure your external workbook references update automatically:

• In the Edit Links dialog, ensure Automatic is selected for update method
• Enable automatic calculation in Formulas > Calculation Options > Automatic
• Keep source workbooks accessible and in their original locations
• Use the Update Values button for manual updates when needed

Can I references external workbooks using Excel for web (Excel online)?

Yes, with limitations. You can view external references (links) to other workbooks, but you cannot create or update them directly using the web version.

The Edit Links dialog is also not available in Excel for the web, so you cannot break or update the links or change the data source. To create or modify these links, you’ll need the Excel desktop application. 

When you open a workbook in Excel for the web that contains external references, you’ll see a yellow bar indicating that it’s referencing external data. External links are disabled by default and require explicit enabling. 

Click Enable content in the yellow bar notification. This will update the links with the latest data from the external workbooks.

5. Summary

• When referencing a cell in the same worksheet, the cell reference appears ‘as-is’. For example:

A1

• When referencing a cell in a different worksheet, the cell reference is prefixed by the worksheet and separated by an exclamation mark. For example:

Sheet1!A1

• When creating external workbook references, the cell reference is prefixed by the workbook name in square brackets, then the worksheet name, an exclamation mark and then the cell reference. For example:

[Book2]Sheet1!A1

• If the workbook is closed, the full pathname of the workbook is placed in the cell reference. When the workbook is open, only the workbook name is displayed.

• If the workbook name or worksheet name contains spaces, it is enclosed in single quotes. For example:

[‘Workbook 1′]’Worksheet 1’!A1

• To view or manage external workbook references that the current workbook links to, click the Edit Links button on the Data ribbon.