Compare Excel lists by a confirmed key

Step by step

1. Select the previous and current CSV or XLSX files; the included example uses different column orders.
2. Choose the intended sheet in each workbook and inspect both source previews. Review encoding and separator settings for CSV.
3. Map left columns to right columns. Select one or more key columns and the values to compare. Confirm any proposed mapping.
4. Review unchanged, changed, new, missing, duplicate and empty-key results separately.
5. Create the XLSX report. It includes the comparison settings, source row references, old and new values, and both original value tables.

Optional extension: use a composite key

The basic training exercise uses id alone as its key. If your task instead identifies records by ID and region together, select both as key columns in a separate comparison. Both parts must then agree. A blank key part is reported separately; it is not silently treated as a reliable identifier.

By default, 0012 and 12 are different strings. Ignoring letter case or outside whitespace is an explicit choice that affects both keys and compared values. It does not rewrite the source tables in the report.

Duplicates are not matched by guesswork

When a key occurs more than once on either side, the affected source rows remain ambiguous. Pairing the first left occurrence with the first right occurrence would create a confident-looking answer without enough evidence.

The sample contains two left rows and one right row with key 0014. Their region value is CH; region is a compared field, not part of the basic exercise's key. All three are reported as ambiguous source rows. The displayed count means three rows, not three matched pairs or three distinct duplicate groups.

Read the changed amount with its source

In the sample, ID 0013 in DE has amount 20 in left source row 3 and 25 in right source row 2. Reordered columns are mapped from left column 4 to right column 3. ID 0012, whose region is CH, is unchanged despite its different row position.

The verified sample has one changed, one unchanged, one new and one missing record, plus the three ambiguous source rows. A report filter changes what you view; inspect the complete exported workbook before acting on the differences.

Use the same exercise in a class or handover

The synthetic workflow training ZIP linked below includes both example lists and their expected results. For the basic exercise, keep the header enabled, leave whitespace trimming and case folding off, and confirm only id as the key. Map id, region, name and amount to their matching right-hand columns and leave all four selected for value comparison.

Expect ID 0013 to change from 20 to 25, 0012 to stay unchanged, 0016 to be new and 0015 to be missing. The three source rows with key 0014 remain ambiguous. Their regions are ordinary source values; they do not define the key in this exercise. Keep the source row references in the report; do not resolve duplicates by position.

Workbook values and limits

Imported formulas are not evaluated. Saved display values may be stale; this tool cannot know whether the source workbook was recalculated. The report stores explicit text cells and does not preserve macros, charts or workbook formatting.

Each input is limited to 5 MiB. Both selected sheets together may contain at most 10,000 rows and 100,000 cells, with 256 columns per sheet. The expanded report also has limits; a large comparison may need smaller inputs. Processing is local in a cancellable worker, and exported cell values are reopened for verification.

Further reading

• Download synthetic workflow training ZIP — examples, expected results and usage terms