How to compare two Excel files
- Put the older or original file in A and the newer one in B. Drop both at once and the one saved earlier becomes A.
- Check Match rows by. If a column identifies each row, such as an ID, SKU or invoice number, it's picked for you.
- Read the result: rows that changed, rows only in B and rows only in A. Click a count to see only that kind.
- Download the highlighted report, or the list of changes as CSV.
How rows are matched
To say a row changed, the tool first has to know which row in B is the same row from A. There are three ways, and the right one depends on your data.
- By an ID column. The most reliable. Rows are paired by a value that identifies them, so sorting, inserted rows and deleted rows don't matter. Any column that is filled in and unique in both files is offered, with ID-like names first.
- By whole rows. For lists without an ID. A row in A matches a row in B when every shared column is the same. It finds rows only in one file, but it can't tell an edited row from a new one: an edit shows as one row only in A and one only in B.
- By row position. Row 2 with row 2, row 3 with row 3. Only right when neither file was sorted and no rows were inserted; otherwise every row after the first insert looks changed.
Values are compared after setting aside extra spaces, and numbers are compared as amounts: 12, 12.00 and $12.00 are the same price. Capital letters count as a difference unless you tick Ignore capital letters.
A worked example
A shop's inventory exported at the end of March and again at the end of April, sorted differently:
inventory-march.csv
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | SKU | Item | Price | In stock |
| 2 | TS-101 | Cotton tee (black) | 12.00 | 96 |
| 3 | HD-200 | Pullover hoodie | 34.00 | 40 |
| 4 | TB-500 | Canvas tote bag | 14.00 | 60 |
inventory-april.csv
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | SKU | Item | Price | In stock |
| 2 | HD-200 | Pullover hoodie | 36.00 | 40 |
| 3 | TS-101 | Cotton tee (black) | 12.50 | 96 |
| 4 | WB-600 | Water bottle | 18.00 | 50 |
Matched by SKU, the report shows two price changes, one new item and one that was dropped:
march-vs-april-differences.xlsx
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Change | SKU | Item | Price | In stock |
| 2 | Changed | HD-200 | Pullover hoodie | 36.00 (changed) | 40 |
| 3 | Changed | TS-101 | Cotton tee (black) | 12.50 (changed) | 96 |
| 4, added | Only in B | WB-600 | Water bottle | 18.00 | 50 |
| 5, removed | Only in A | TB-500 | Canvas tote bag | 14.00 | 60 |
The sample files in the tool are a longer version of this, including a Supplier column that only the April file has. It's listed, and left out of the comparison.
Reading the highlighted report
The report is the newer file with three columns added at the front, Change, Row in A and Row in B, and a What changed column at the end that spells out each edit, such as Price: 34.00 → 36.00. Rows only in A are added at the bottom.
- a yellow cell is a value that changed
- a green row is only in B: it was added
- a red, struck-through row is only in A: it was removed
The header row is frozen and has filters, so filtering the Change column to “Changed” gives you just the edits. A second tab, Summary, has the counts and the names of the files you compared.
Comparing two lists: matches and missing items
The same tool answers “who is on this list but not that one?” Put your full list in A and the other in B, for example everyone you invoiced and everyone who paid, then match by a shared column such as Email or Invoice number.
- Only in A: on the first list and missing from the second. Who hasn't paid.
- Only in B: on the second list only.
- Same or Changed: on both lists. Changed means the other columns differ, such as a different amount.
If one list has repeats of its own, clean it first with Remove duplicates; repeated IDs are paired in order, which is rarely what you want for a list.
Comparing in Excel itself
Microsoft's Spreadsheet Compare does this inside Office, but only comes with Office Professional Plus and Microsoft 365 Apps for enterprise on Windows. Most small business editions don't include it.
With plain Excel, View > View Side by Side puts two workbooks next to each other and scrolls them together, which works for a quick look at short files. For two lists, a formula finds what's missing: next to each value in the first list, =COUNTIF(Sheet2!A:A, A2)=0 returns TRUE when it isn't in column A of Sheet2.
Both approaches break down when files are sorted differently or have hundreds of rows, which is where matching by an ID column earns its keep.