Data Import & Export

Keep User-Entered Data Aligned After an Excel Table Refresh

Rana GarciaRana Garcia Nov 18, 2026 870 views

Question details

Keep manually entered values connected to the correct records when an imported Excel table changes.

Keep User-Entered Data Aligned After an Excel Table Refresh
Product
Microsoft Excel
Device & OS
not provided
Scenario
User-entered comments or other values must remain associated with imported records after a refresh or sort.
Observed behavior
Manual columns beside imported data can become misaligned when the source rows are refreshed, sorted, or replaced.
Before you start

Identify a stable, unique field such as Job Number in the imported data, and check that the same identifiers are used for manual entries. Decide which columns you want to display on a separate combined sheet.

Solution 1Recommended

Link Manual Entries to Refreshed Data with XLOOKUP

Use a separate manual-entry table and a formula-driven sheet when your Excel version supports XLOOKUP and dynamic arrays.

1
Separate the data

Keep the imported table on its own sheet and name it SourceData using Table Design > Table Name. Ensure it contains columns named Job Number and Status.

2
Create a manual-entry table

On another sheet, enter Job Number and Comment headers, select the range, and choose Insert > Table. Name the table ManualEntries and enter one comment per Job Number.

3
Display current source records

On a new sheet, add Job Number, Status, and Comment headers in A1:C1. In A2, enter =FILTER(SourceData[Job Number],SourceData[Job Number]<>"",""). In B2, enter =FILTER(SourceData[Status],SourceData[Job Number]<>"","").

4
Match the comments

In C2, enter =XLOOKUP(A2#,ManualEntries[Job Number],ManualEntries[Comment],""). Refresh the imported table using Data > Refresh All; the combined sheet will display comments matched by Job Number.

Link Manual Entries to Refreshed Data with XLOOKUP
Keep entry and display separate: Enter or edit comments in ManualEntries, not beside the spilled results. Leave space below the formulas so the results can expand.
WPS Spreadsheet

Match Refreshed Records and Comments in WPS Spreadsheet

Use a separate entry sheet and an exact-match VLOOKUP in WPS Spreadsheet to display comments alongside imported records without relying on row position.

  1. 1. Open the workbook: Open your workbook in WPS Spreadsheet. Keep imported Job Numbers in column A and statuses in column B on a sheet named Imported Data.
  2. 2. Store comments separately: Create a sheet named Manual Entries. Enter Job Numbers in column A and their comments in column B, starting in row 2.
  3. 3. Display the imported records: On a new sheet, enter Job Number, Status, and Comment in A1:C1. In A2, enter =IF('Imported Data'!A2="","",'Imported Data'!A2). In B2, enter =IF(A2="","",'Imported Data'!B2).
  4. 4. Look up and fill comments: In C2, enter =IF(A2="","",IFERROR(VLOOKUP(A2,'Manual Entries'!$A$2:$B$1000,2,FALSE),"")). Select A2:C2 and drag the fill handle down far enough to cover the expected records. Extend the lookup range if manual entries exceed row 1000.
  5. 5. Maintain the separate sheets: Update the imported data on Imported Data and enter comments on Manual Entries. Check the combined sheet to see each comment matched to its Job Number.
Match comments to records by Job NumberKeep manual entries separate from imported dataOpen and save Microsoft Excel XLSX workbooksWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why do comments move to the wrong records after a refresh?

A refresh can add, remove, or reorder imported rows, while adjacent manual cells may stay in their existing positions. Store comments in a separate table keyed by Job Number instead of relying on row position.

What happens if a Job Number is duplicated?

XLOOKUP and VLOOKUP return the first matching entry, while a Power Query merge can produce multiple matches. Check both tables for duplicate Job Numbers and use a truly unique identifier where possible.

Where should I edit a comment after creating the combined view?

Edit it in the separate manual-entry table, alongside its Job Number. Formula results update from that table; for a Power Query output, use Data > Refresh All afterward.

Why is a matching comment blank?

Check that the Job Number exists in the manual-entry table and that both keys use consistent text or number formats. Also check for extra spaces and confirm that the lookup range includes the entry.