Compare Two Excel Files

About comparing two spreadsheets

Compare Lists answers "what is in one and not the other" for a single column of values. This answers the harder question that comes up far more often: given two versions of the same table, what actually changed?

That question has three parts, and a useful answer separates them:

• Rows that appeared in the newer file
• Rows that disappeared
• Rows that are still there but now say something different — and in which cell

The third is the one that matters and the one a list comparison cannot give you. A price that moved from 10 to 12 is not a removed row and an added row; it is one row with one changed cell, and that is how it should be reported.

How the rows get paired

This decides everything, so it is the first control.

• By a key column — the usual case. Two exports of the same data, matched on order number, SKU or email, so a row that moved to a different position is still recognised as the same row
• By position — row 1 against row 1. The right choice when there is no key at all, or when the order itself is what you are checking

With a key, a repeated value is reported rather than quietly matched to whichever row came first. If the key is not unique, no pairing is trustworthy, and picking one silently is the kind of answer that gets believed.

Comparing values fairly

Three settings decide when two cells count as different, and the defaults are the forgiving ones:

• Ignore extra spaces — on. A trailing space is not a change anybody means
• Treat 1,000 and 1000 as the same number — on. The same value formatted two ways is the same value; without this, a file exported with thousands separators would appear to have changed in every numeric cell
• Match case — off, so ACME and Acme match

Turn them off when the formatting itself is what you are auditing.

How to use it

• Load file A and file B — upload both, or paste both, or one of each
• Choose how to pair the rows, and which column is the key
• Read the summary: changed, only in A, only in B, identical
• Narrow the view to one kind if the list is long
• Download the CSV for a record of every difference

What people use it for

• Checking what changed between this month's export and last month's
• Reviewing a price list before it goes live
• Verifying that a data migration moved everything intact
• Finding which rows a colleague edited in a shared workbook
• Auditing a product catalogue against the supplier's version
• Confirming a backup matches the original, row for row

Good to know

• Works on Windows, macOS, Linux, ChromeOS, and on phones and tablets — it takes pasted text or files, not a folder
• Nothing is uploaded. Both files are read and compared in the page
• Read-only. Neither file is modified in any way
• The comparison uses the header from file A for column names, so the two files should have their columns in the same order
• Only values are compared. Formulas are read as their calculated result, and formatting, colours and comments are not compared at all
• On screen, up to 200 changed cells and 100 rows per section are listed; the CSV always contains every difference

Related tools

• Compare Lists — a simpler comparison for one column of values
• Compare Two Folders — the same question about files rather than rows
• Remove Duplicate Rows — clean each file before comparing
• VLOOKUP Online — join the two tables instead of diffing them