Data Import & Export

Update Excel Workbook References for a New Year

Bushra ParveenBushra Parveen Nov 18, 2026 868 views

Question details

Update external references after copying a group of linked Excel workbooks to a new yearly folder.

Update Excel Workbook References for a New Year
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the link manager

In the copied workbook, select Data > Edit Links. In newer Excel versions, select Data > Queries & Connections > Workbook Links instead.

2
Select a source workbook

In the list of links, select a workbook that still points to the previous year's folder.

3
Change the source

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.

4
Repeat and verify

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.

Change the source of external workbook links in Excel
Links are updated by source: Changing a source updates references to that source in the current workbook; it does not automatically update every workbook in the folder. Keep the expected sheet names in the new source files.
Work with linked spreadsheets

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. 1. Open a copied workbook: In WPS Spreadsheets, select Open and choose a workbook from the new yearly folder.
  2. 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. 3. Select the new source: Select an old source, choose Change Source, and browse to the corresponding workbook in the new folder.
  4. 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.
Redirect external workbook links without editing each formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.