Update Excel Workbook References for a New Year
Question details
Update external references after copying a group of linked Excel workbooks to a new yearly folder.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user copies workbooks that reference one another into a folder for a new year and wants to avoid editing each formula.
- Observed behavior
- The copied workbooks may still reference source files in the previous year's folder.
Copy all related workbooks into the new yearly folder and check that the source workbooks contain the expected sheet names. Open a copied workbook that contains external references.
Change the source of external workbook links in Excel
Use Excel's link management tools to redirect formulas to the copied source workbooks without changing formulas one by one.
In the copied workbook, select Data > Edit Links. In newer Excel versions, select Data > Queries & Connections > Workbook Links instead.
In the list of links, select a workbook that still points to the previous year's folder.
Select Change Source, browse to the corresponding workbook in the new yearly folder, and select Open. In the Workbook Links pane, use the source's menu to find Change source.
Repeat Change Source for each source workbook and in each copied workbook that contains links. Check a few linked cells and save the updated workbooks.

Consolidate yearly data into an Excel Table
Use a single table for multiple years if you want to reduce reliance on separate yearly workbook links over time.
Update workbook links in WPS Spreadsheets
Open the copied workbooks in WPS Spreadsheets and use the link management controls to point external references to the new source files.
- 1. Open a copied workbook: In WPS Spreadsheets, select Open and choose a workbook from the new yearly folder.
- 2. Find external links: Select Data > Edit Links to view the workbook's linked sources. The control's location can vary by WPS Office version.
- 3. Select the new source: Select an old source, choose Change Source, and browse to the corresponding workbook in the new folder.
- 4. Check and save: Repeat for other sources, check linked cells, and select Save. Repeat the process in other copied workbooks that contain external links.

Frequently Asked Questions
Will Change Source update every formula linked to a workbook?
Change Source redirects references to the selected source in the current workbook, so you generally do not need to edit those formulas individually. Check linked cells afterward, especially if sheet names differ.
Do I need to update links in each copied workbook?
Yes. Open each copied workbook that contains external references and check its links under Data > Edit Links or Data > Queries & Connections > Workbook Links.
Why might a link still show an error after I change its source?
The new source may not contain the expected sheet or range, or it may not be accessible. Open the source workbook and compare its sheet names and referenced cells with the original.
Can I avoid separate workbook links for every year?
If the data has a consistent structure, combine it in an Excel Table with a Year column. Use the column filter or Insert > PivotTable to report on individual years.




