Compare two Excel files by record ID, not row position

For repeated comparisons of tabular data, use Power Query to match records by a stable ID, then classify them as added, removed, changed or unchanged. This avoids treating a reordered row as a data change and gives you a reviewable table before publication.

The workflow below compares selected data values—not formula text, cell formatting or every worksheet in a workbook. If those are part of your audit, a clean data comparison alone is not enough.

Define the comparison first

Choose the table in each file, the columns to compare and a record key: a field that identifies the same record across releases. Row numbers are not stable keys. If an ID changes, this workflow reports the old ID as removed and the new one as added.

Here is a fictional example using two XLSX files. Store the IDs as text in the source files, not as numbers with a display format that adds zeros.

Old file

ID	Label	Value
001	North	10
002	South	20
003	West	15

New file

ID	Label	Value
003	West	15
001	North	12
004	East	8

The expected result is one changed record, one removed record, one added record and one unchanged record. The move of 003 to the first row does not count as a change.

Before importing, decide whether differences in case, whitespace, decimal precision or missing values should count. Apply any agreed normalization to both files, and retain the untouched originals.

1. Import and validate both tables

In a separate comparison workbook in desktop Excel, start from Data → Get Data and choose the Excel workbook connector. Select the old file, choose the intended table in Navigator, and select Transform Data. Repeat for the new file. Name the queries Old and New. Microsoft documents this Excel import flow; connector availability varies by Excel version.

For this example, keep ID, Label and Value. Inspect the Applied Steps first: Power Query can automatically add a Changed Type step for Excel data. Remove or correct any step that converts the IDs to numbers before setting ID and Label to text and Value to a whole number in both queries. See Microsoft’s data-type detection guidance. Converting a number back to text will not restore lost leading zeros.

Matching key columns must have the same data type for reliable merge results.

Check four things before merging:

• Schema: note added, removed or renamed columns before selecting your comparison fields. Comparing only common columns does not prove that the entire dataset is unchanged.
• Completeness: confirm each imported row count against its source table. Do not treat the editor’s preview as the complete dataset.
• Key validity: every ID must be nonblank and unique within each file after normalization.
• Errors: resolve import and type-conversion errors rather than silently dropping those rows.

To audit duplicates, make a separate reference query for each input. Use Group By on ID, with Count rows, then filter the count to values greater than one. Microsoft documents grouping and row counts. Stop if either duplicate report contains rows: repeated keys can produce multiple matches instead of one comparison per record.

Check null, empty and whitespace-only IDs separately. Resolve invalid keys before continuing; do not simply remove those records from the comparison.

2. Use a full outer merge

Rename the columns in Old to Old_ID, Old_Label and Old_Value. Rename those in New to New_ID, New_Label and New_Value.

Then, in Power Query Editor:

1. Select Home → Merge Queries → Merge Queries as New.
2. Choose Old as the left table and New as the right table.
3. Select Old_ID and New_ID as the matching columns.
4. Choose Full outer as the join kind. Leave fuzzy matching off, then select OK.
5. Expand the merged New table column to include all three fields. Clear Use original column name as prefix so the expanded names remain New_ID, New_Label and New_Value.

A full outer join retains rows from both inputs, including records found in only one file. An inner join would discard additions and removals—the very changes you need to review.

For a composite key, such as Country plus Year, select the key columns in the same order in both tables. Audit uniqueness using that same combination.

3. Add status and record ID columns

Select Add Column → Custom Column, name it Status, and enter this Power Query M expression:

if [Old_ID] = null then "Added"
else if [New_ID] = null then "Removed"
else if [Old_Label] <> [New_Label]
     or [Old_Value] <> [New_Value] then "Changed"
else "Unchanged"

The absence checks are safe here because you have already rejected blank source IDs. Extend the comparison with another or pair for every additional non-key field you want to audit.

Power Query supports custom columns written in M. Its equality rules treat null as equal only to itself and compare text case-sensitively. This expression therefore flags a missing value becoming zero, or North becoming NORTH.

Add another custom column named Record ID to retain the key for additions as well as removals:

if [Old_ID] = null then [New_ID] else [Old_ID]

For the sample, the value comparison should be:

Record ID	Old value	New value	Status
001	10	12	Changed
002	20	—	Removed
003	15	15	Unchanged
004	—	8	Added

Here, — means that the record was absent, not that its value was zero. Keep both label columns in your actual review table too.

Keep the workflow efficient without hiding changes

Keep only the key and required comparison fields before the merge. Avoid sorting inputs just to align rows; the key handles alignment. Sort the finished review table if needed. Microsoft recommends filtering irrelevant data early and doing expensive operations such as sorting last.

If you restrict the comparison to a subset, document and apply the same scope to both inputs. A filter based on a changing field can make a record look removed when it merely moved outside your chosen scope.

Before filtering out Unchanged, group the full result by Status using Count rows, and verify:

• Old rows = removed + changed + unchanged.
• New rows = added + changed + unchanged.

These checks assume unique, nonblank keys and one output row per key. They help catch missing records or multiplied matches, but do not replace inspecting the changes.

Name the merged query Comparison. Use Home → Close & Load → Close & Load To to load it as a table in a worksheet for review; Microsoft documents query loading options.

For subsequent comparisons, keep archived originals and replace the working input files at the same paths, preserving table names and the expected schema. In Excel, use Data → Refresh All, wait for completion, and repeat the key, schema and count checks. Refreshing only the editor preview does not update the worksheet result.

Publish the reviewed result, not the audit workbook

Keep the full comparison privately for review. Prepare a separate data-only file containing either the approved new release or a public change log with record ID, status, old value and new value. Include release dates, source references, units and the comparison rules in its accompanying notes.

Remove sensitive information from both old and new values before sharing. TablePage accepts CSV, TSV, XLSX and XLS and creates a public dataset page with a shareable link and filterable table. Use it to publish the reviewed result, not to upload confidential files for comparison.

Follow the CSV export checklist when preparing the final file, or use the public CSV version-comparison guide when your releases are already CSVs.