How to find what changed between two versions of a spreadsheet

To find what changed between two versions of a spreadsheet, match each row in the old file with its partner in the new file, then compare the cells of each matched pair. Matching is the hard part. A row-by-row comparison by position fails as soon as a row is inserted or the file is re-sorted, because every later row is compared with the wrong partner. Matching by a key column, such as an order number, keeps each row paired with its own record even when rows move.

Compare spreadsheetsSpot what changed between filesDo it free

Why row order breaks a plain comparison

Comparing row 2 with row 2 is fast and easy to fool. Add one row at the top, and every later row is paired with the wrong one. The result is hundreds of "changes" that are really one change. Sorting causes the same problem. Re-sorting changes no data, yet a position-based comparison reports every moved row as changed. If a diff looks far too big, check the order first.

Use a key column

A key column identifies a row and does not change for that row: an order number, a product code, a customer ID. Pick a column where each value appears once. If the key repeats, the comparison cannot tell which row is which, so look for a better column or combine two.

With a key, every row falls into one of four groups:

Added. The key exists in the new file only.
Removed. The key exists in the old file only.
Changed. The key exists in both files, and at least one cell differs.
Unchanged. The key exists in both, and every compared cell matches.

Compare the two files lets you choose the key column, shows each group with its count, and lists which cells differ in each changed row.

Changed cells, and columns that always change

A changed row may differ in one cell out of thirty, so look at the cells. A price moving from 12.50 to 13.00 is a different kind of change from a new address in the same row. Ignore columns that change on every save, such as a "last modified" timestamp, so they do not fill your list.

Case and spaces also matter. "Berlin" and "berlin " differ to a computer. Ignoring case and edge spaces removes these false changes, but a corrected spelling is a real change, so use those options only when the difference does not matter to you.

Numbers that look different but are equal

Two cells can show different text and hold the same value. This is the most common source of false changes:

Thousands separators. 1,000 and 1000 are the same number.
Decimal places. 12.5 shown as 12.50 is the same value in a different display.
Currency formats. A cell formatted as £5 holds the number 5. A cell typed as text with the £ sign does not.
Dates. A spreadsheet stores a date as a serial number and shows it in a chosen format, so 03/04/2026 and 2026-04-03 can be the same day.

The compare tool reads numbers the way a spreadsheet writes them, and it reads the values Excel saved rather than the formulas that produced them. A number stored as text compares as text, so check each column's data type before you trust that column's comparison.

Computers also store decimals in binary, so 0.1 + 0.2 is not exactly 0.3 in most programming languages. The tolerance setting treats numbers as equal when they differ by no more than an amount you choose, such as 0.01 for a cent.

When there is no shared key

Use the closest stable pair you have, such as name plus date of birth, and check a sample by hand. If you compare by position anyway, read the result as "these rows differ at the same place", not as proof of what changed.

A comparison asks what differs between two versions of one table. A join asks how two different tables fit together. VLOOKUP is the simplest join: it finds a matching key in another table and returns a value from it. Two traps cause most of its surprises. It only looks at the first column of the range, so the key must sit on the left. And it returns the first match it finds, so a key that appears twice is quietly reduced to one row. Leaving out the last argument also switches it to approximate matching. XLOOKUP does not need the key on the left, but it still returns one match per lookup.

Join two spreadsheets on a shared column does the same job without formulas. Choose the key in each table and a join type:

Keep all left rows keeps every row of the first table and adds matching columns where they exist.
Only matches keeps just the rows found in both.
Left rows with no match lists what is missing from the second table, often the most useful result.

For duplicate keys, First match behaves like VLOOKUP. All matches adds a row for each match, so a customer who appears three times on the right appears three times in the result. Count the rows before and after, and check duplicates first.

Names spelled differently

Exact keys fail when the same thing is written two ways: "Jon Smith" and "John Smith", "Müller" and "Mueller". Fuzzy matching gives each name in one list its closest name in the other, with a score. The score is the higher of two measures. One compares letters in order and rewards a shared start, which suits "Smith" and "Smyth". The other compares words, so a name whose words all appear in the other name scores 100. Read a 100 with care when one name has extra words. The tool ignores accents and punctuation and can drop company words such as Inc or Ltd. A score is still an estimate, so read the pairs before you accept them.

Edge cases

Invisible characters make equal-looking keys unequal. Remove hidden characters if identical-looking keys refuse to match.
Leading zeros disappear when a file opens in a spreadsheet, so "00421" no longer matches its text version. Tick Ignore leading zeros in the join, so both spellings count as the same key.
Hidden columns still count in a comparison unless you ignore them.

The short method

Pick a key column in both files, and check that it is unique.
Compare by key, and turn on case and space rules only where the difference does not matter.
Ignore columns that change on every save.
Check the totals. With unique keys, unchanged plus changed plus removed equals the old row count, and unchanged plus changed plus added equals the new row count.

Quick answers

Why does one inserted row mark everything as changed?

A position comparison pairs each row with the one at the same number, and the insert shifts every later row. Compare by a key column instead.

Why do two files with the same numbers show a change?

The values are probably stored as text, or formatted differently. Check the column type.