Data Import & Export

Keep Excel Columns Synchronized When External Data Changes

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Keep related Excel columns matched to the correct records when data linked from an external workbook changes.

Keep Excel Columns Synchronized When External Data Changes
Product
Microsoft Excel
Device & OS
not provided
Scenario
Columns A:D contain data linked to an external workbook, while columns E onward contain related values.
Observed behavior
Rows added or changed in the source can leave the values in later columns aligned with the wrong records.
Before you start

Identify a unique, stable ID for each record in the source and in the related data. If the columns are already misaligned, verify the original relationships before creating the ID-based table; row position alone may not be enough to recover them.

Solution 1Recommended

Match Related Values to Source Records by Unique ID

Use a lookup formula when each source record has an ID that also appears in a separate table of related values.

Store related values with their IDs outside the imported range. A lookup can then retrieve the correct value even when the source records change order.

1
Create a keyed related-data table

On a sheet named Related, put each unique ID in column A and its related value in column B. Add further related fields in later columns as needed.

2
Check the source ID

Confirm that column A of the sheet containing the external data holds the matching ID for each record, starting in A2.

3
Enter the lookup

In E2, enter =XLOOKUP(A2,Related!$A$2:$A$1000,Related!$B$2:$B$1000,""). Adjust the ranges to cover your related-data table.

4
Apply formulas to the records

Fill the formula down column E and create similar formulas for other related fields. If the output is an Excel Table, check after each refresh that its calculated columns have extended to any new rows.

Match Related Values to Source Records by Unique ID
Keep editable values separate: Do not rely on manually entered values beside a refreshable range to stay with their original records. Maintain those values with their IDs in the Related sheet instead.
Work with linked data in WPS Spreadsheets

Match Related Columns by ID in WPS Spreadsheets

Use an INDEX and MATCH formula in WPS Spreadsheets to retrieve related values by a stable ID instead of depending on row position.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select the workbook containing the source records.
  2. 2. Organize related values: On a sheet named Related, place unique IDs in column A and their corresponding values in column B.
  3. 3. Match values by ID: On the source sheet, enter =IFERROR(INDEX(Related!$B$2:$B$1000,MATCH(A2,Related!$A$2:$A$1000,0)),"") in E2, assuming A2 contains the source ID.
  4. 4. Fill and check the formula: Fill E2 down for the remaining records, then check that new rows have formulas and that blank results are not caused by missing or mismatched IDs.
Match related values to records even when source rows change orderOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and worksheet tabs
microsoft office alternative - wps office

Frequently Asked Questions

Why do my columns become misaligned after external data changes?

Imported rows may be added, removed, or reordered while manually entered values beside them remain in their existing cells. Store those values with a unique ID and retrieve them by that ID.

What can I use if my Excel version does not have XLOOKUP?

Use INDEX and MATCH instead. For example, enter =IFERROR(INDEX(Related!$B$2:$B$1000,MATCH(A2,Related!$A$2:$A$1000,0)),"") in E2 to match the ID in A2.

Will converting the range to an Excel Table preserve the relationships?

A table can help extend calculated-column formulas, but it does not preserve a relationship based only on row position. Use a unique ID in the formulas and verify that formulas extend after a refresh.

What happens if an ID appears more than once in the related table?

A lookup may return the first matching value, while a Power Query merge can produce multiple rows. Make the related-table ID unique, or use a combination of fields that uniquely identifies each record.

How do I update a Power Query merge after the source changes?

In Excel, choose Data > Refresh All. Check the refreshed results for unmatched IDs and confirm that the source files are accessible.