Maintain Excel External Links Between Workbooks in Different Folders
Question details
Learn how to retain data connections when an Excel workbook retrieves values from a source workbook saved in a separate folder.
- Product
- Microsoft Excel external workbook links
- Device & OS
- not provided
- Scenario
- A destination workbook uses formulas or imported data from a source workbook located in another directory, and the files may be moved or renamed.
- Observed behavior
- External links can stop resolving after a source or destination workbook is moved, renamed, or opened from a different path.
Keep the source workbook name, worksheet name, and referenced cells available before changing links. If the source file has moved, identify its current folder path so Excel can be pointed to the correct workbook.
Use full external references and update links after moving files
Use this method for cell-based links between workbooks, especially when the source and destination files are stored in different folders.
An external reference normally includes the source workbook, worksheet, and cell or range. When the workbooks are in separate folders, Excel stores a full path while the source workbook is closed.
For example, a linked formula can resemble ='C:\Reports\[Source.xlsx]Data'!$A$1. If Excel asks whether to update links when opening the destination workbook, choose Update so it can refresh values from the source file.
In the destination workbook, select the target cell, type =, then open the source workbook and select the source cell or range. Press Enter to let Excel create the external reference automatically.
Open the destination workbook after a source file has been moved. At the security or update prompt, select Update to search for and refresh the linked source data.
In Excel, open the Data tab and select Workbook Links or Edit Links, depending on your Excel version. Select the affected source, choose Change Source, and browse to the source workbook in its current folder.
Use named ranges for source values that may be reorganized
Use named ranges when source cells may move within the source workbook but represent a consistent field, metric, or table area.
Import structured source data with Power Query
Use Power Query when you need to import tables or recurring datasets rather than maintain many individual external-cell formulas.
Create and refresh external workbook references in WPS Spreadsheets
WPS Spreadsheets can help you link values across workbook files using external-reference formulas. Use a full source path for files in different folders, then update the reference if the source workbook is moved or renamed.
- 1. Open the destination workbook: Open the workbook that should display data from another file in WPS Spreadsheets.
- 2. Create an external formula: Select the destination cell, type =, open the source workbook, select the source cell, and press Enter to generate the linked reference.
- 3. Save both workbook files: Use Menu > Save As to save the source and destination workbooks in their intended folders so the link can retain the correct location.
- 4. Update a moved source reference: If a linked value cannot be found after moving a file, select the formula cell and edit the workbook path in the formula bar to match the source file's current folder.
Frequently Asked Questions
Why do Excel links break when workbooks are stored in different folders?
Excel external formulas depend on the source workbook path, file name, worksheet name, and referenced range. A move, rename, or missing source location can make the stored path invalid.
Can relative references keep links working between separate Excel workbooks?
Relative references are not dependable for workbooks kept in unrelated directories. Use the full external path for a source workbook in another folder and update the link after files are moved.
Where can I change a broken workbook link in Excel?
Open the destination workbook and go to Data > Workbook Links or Data > Edit Links, depending on the Excel version. Select the source workbook, choose Change Source, and browse to its current location.
Does INDIRECT work with a closed source workbook?
Usually no. INDIRECT generally cannot return data from a closed external workbook, so it is not a reliable choice for links to source files in another folder.
When should I use Power Query instead of external formulas?
Use Power Query when importing structured tables, lists, or recurring reports. In Excel, use Data > Get Data > From File > From Workbook, then refresh the query when source data changes.
