Two versions of the same customer list, last month's stock report and this month's, an export from the CRM and one from accounts: sooner or later you need to know what changed between two sheets. Excel has no single "compare" button in most versions, but a few formulas cover every case. The trick is picking the right comparison first.
Which kind of comparison do you need?
| Your sheets | Compare | Finds |
|---|---|---|
| Same rows and columns in the same order (a budget, a form, a schedule) | Cell by cell | Any cell that changed |
| Lists of records that can be added, removed or reordered (customers, products, orders) | Row by row, on a key column | Missing rows and changed values |
Cell-by-cell comparison falls apart as soon as one row is inserted: every row below it shifts and shows as "different". For lists, always compare on a key column such as an ID or email.
How do you compare two sheets cell by cell?
Add a third sheet, and in its A1 type this formula, then copy it across and down to cover the same area as your data. Blank means the
cells match; anything else shows both versions:
=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" > "&Sheet2!A1,"") - Sheet1!A1<>Sheet…: logical_test
- Sheet1!A1&" > "&…: value_if_true
- "": value_if_false
To highlight the differences on the sheet itself instead, select the data on Sheet1 starting at A1, go to Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format,
and enter =A1<>Sheet2!A1 with a fill colour. Leave out the $ signs so the rule moves with each cell. The comparisons lesson explains how = and <> give TRUE or FALSE, and IF basics turns that into a label.
How do you find rows that are missing from the other sheet?
With IDs in column A of both sheets, put this next to the first sheet's data and copy it down. TRUE means the ID is not on Sheet2:
=COUNTIF(Sheet2!$A$2:$A$500,A2)=0 If you prefer a label to TRUE and FALSE:
=IF(COUNTIF(Sheet2!$A$2:$A$500,A2)=0,"Missing","") Then do the same on Sheet2, pointing at Sheet1. One direction gives the removed rows, the other the added rows. Filter the
column on TRUE or Missing to list them. Counting matches with COUNTIF walks through
the function step by step.
How do you find values that changed in matching rows?
Look up each row's partner on the other sheet and compare one column. This flags every price in column C that differs from the price for the same ID
on Sheet2:
=XLOOKUP(A2,Sheet2!$A$2:$A$500,Sheet2!$C$2:$C$500)<>C2 TRUE means changed; #N/A means the ID is missing from Sheet2 altogether. For numbers that may differ by rounding,
compare with a tolerance instead, for example =ABS(XLOOKUP(A2,Sheet2!$A$2:$A$500,Sheet2!$C$2:$C$500)-C2)>0.01. To check several
columns, copy the formula across and change the return column each time. In Excel 2019 or earlier, use VLOOKUP with FALSE in place of XLOOKUP.
Why do identical-looking rows show up as different?
The same reasons lookups fail: a trailing space, an ID stored as a number on one sheet and as text on the other, or leading zeros that one system
dropped. Excel's comparisons ignore upper and lower case, so that is rarely the cause; use EXACT if it should matter. The full checklist
is in VLOOKUP not working?, and every fix there applies here.
Can Excel compare two sheets without formulas?
For a quick look at two small sheets, open both and use View, View Side by Side with Synchronous Scrolling. Some Office editions for Windows also include Spreadsheet Compare, which lists cell-level differences between two workbooks. Neither handles reordered lists well; for those, use the key-column formulas above.
How do you find what's missing without Excel?
Drop both files into Lookup & Merge, choose the ID column on each side and pick What's missing. It lists the
rows of each sheet that have no partner in the other, explains near-misses ("trailing space", "stored as a number in the other table") and downloads the
lists as .xlsx or .csv. Switch to Keep every row to bring the other sheet's columns alongside and compare
them. It all runs in your browser, so the sheets never leave your computer.
Comparing two single columns rather than whole sheets? ListDiff.com, our sister site for lists, shows what is in one list, the other, or both. And if you need to join the two sheets rather than compare them, see how to merge two Excel files using a common column.