How to compare two Excel files and find every changed row
Compare spreadsheet versions by selecting a stable business key, normalizing only comparison fields, and classifying records as added, removed, unchanged, or modified. For modified rows, show field-level before and after values. Never compare row position when files can be sorted or filtered differently.
Why the obvious method fails
Side-by-side visual review misses subtle edits and treats reordered rows as changes. A lookup can find missing keys but often hides duplicate keys and does not explain which fields changed.
A useful diff explains change, not just inequality
A spreadsheet comparison becomes decision-useful when it identifies the entity, the type of change, and the fields responsible. making values consistent should remove irrelevant formatting differences while preserving real changes such as units, currency, or effective date.
Where this usually goes wrong
If no stable key exists, do not invent one from row number. Establish a composite key, use controlled entity matching, or state that the comparison can identify row-level differences but not reliably classify business records.
A useful mental model
- Confirm uniqueness and document how duplicate keys will be handled before joining versions.
- Map renamed fields, data types, and comparison units without overwriting original values.
- Produce added, removed, unchanged, and modified sets using the approved key.
- For each modified record, list the old value, new value, field name, and source versions.
A method you can follow
- Choose and test the key: Confirm uniqueness and document how duplicate keys will be handled before joining versions.
- Align schemas: Map renamed fields, data types, and comparison units without overwriting original values.
- Classify rows: Produce added, removed, unchanged, and modified sets using the approved key.
- Expose field differences: For each modified record, list the old value, new value, field name, and source versions.
From the field · Procurement coordinator
Compare supplier price lists before accepting the update
A supplier sends a new workbook with reordered products, new SKUs, discontinued items, price changes, and a renamed Description column. Procurement must isolate material changes before loading the list.
Columns used in the example
supplier_sku · description · unit_price · currency · minimum_order · effective_date
Follow it with your own file
- Test supplier_sku uniqueness in both versions and separate duplicate-key exceptions.
- Map the renamed description field and standardize numeric comparison precision.
- Create added, removed, and modified product tables.
- Calculate price change amount and percentage only for comparable currencies and units.
Check: Old row count must reconcile to unchanged plus modified plus removed records; new row count must reconcile to unchanged plus modified plus added records. Review every duplicate-key and currency exception.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Visual side by side | Tiny files with few edits | Reordering and subtle value changes |
| Lookup formulas | Simple missing-key checks | Duplicate keys and field-level differences |
| Structured diff | Repeatable version audits | Key quality and schema mapping |
Patterns that need context
- The two files have different sorting or row counts.
- A supplier or product ID appears more than once in one version.
- Reviewers need an audit record of old and new values.
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Compare two Excel workbooks and identify added, removed, and changed rows. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data profiling · Reviewable cleaning · Reusable workflows · Version history
What to verify before acting
- Stable key tested
- Schemas aligned
- Duplicate keys isolated
- Field-level changes produced
- Version counts reconciled
A reviewable change register showing exactly what was added, removed, or modified and why each record received that classification.
Questions readers usually ask next
How can I compare Excel files when rows are in a different order?
Join the files using a stable key instead of row position, then compare aligned fields for each matched key.
What if the same key appears twice?
Move duplicate keys to an exception set and resolve their grain before classifying changes. A many-to-many join can create false differences.