How to compare Excel files when the rows are in a different order

Two spreadsheets can contain the same records without putting them in the same cells. A cell-by-cell comparison then reports movement as change. Start by deciding what identifies a record.

Choose the key before comparing values

A product ID, invoice ID or a documented combination of fields can connect the records. An identifier is not always a number: '00123' and '123' may name different products. Keep leading zeros unless your business rule explicitly says they are irrelevant. A missing key needs review, not a guessed match.

Map the columns

One file may call a column 'SKU' and another 'Product Code'. Establish the mapping and types first. Treat a decimal as a decimal and a date as a date only after choosing the source format. The string '1,234' can mean different quantities under different formats.

Preserve duplicates

If file A has two identical rows and file B has one, there is one extra occurrence. A set comparison that discards duplicates loses that information. If several different rows share a key, a proposed match may be ambiguous. Review it before applying changes.

Use tolerances without changing the source

For some measured values a small numerical tolerance is legitimate. If the tolerance is 0.01, values 10.000 and 10.009 can be classified as equal under that rule. The source still says 10.009. Comparison tolerance is not permission to rewrite it as 10.

Clean a copy and keep an explanation

Preview trimming, type conversion and duplicate removal before applying them. Export a new dataset with a before-and-after list and the source row references. Keep unresolved records separate. If an application exports values only, do not expect the full formulas, styles and links of the original workbook to survive.

Spreadsheet Compare & Clean Desk follows this workflow in your browser. Open its fictional examples to inspect key matching, duplicate handling and the change report. Matching shows which records correspond under your rules; it does not prove that the underlying accounts are correct. For a month-end check of bank and accounting exports, see Month-End Reconciliation & Evidence Desk.

Explore the fictional demo · See the full tool