Guide
Compare Two Excel Sheets
Same workbook, two tabs — last month’s export and this month’s, the plan and the actuals — and you need every difference, not a squint-and-scroll guess. Three ways to diff two sheets, all with built-in Excel.
1. A diff sheet with one IF formula
Insert a blank sheet and in A1 enter
=IF(Sheet1!A1<>Sheet2!A1,"DIFF",""), then copy it across the full used
range. The result is a map of the workbook: blank where the sheets agree,
DIFF where they don’t. Add =COUNTIF(A:Z,"DIFF") in a corner
and you also get the total number of changed cells.
2. Highlight changes in place with conditional formatting
If you’d rather see differences on the data itself, select the range on Sheet1, create a
conditional formatting rule with the formula =A1<>Sheet2!A1, and choose
a fill color. Changed cells light up right where they are — handy when you’re about to
present the sheet and just need the edits to stand out.
3. Match rows by key when order changed
Position-by-position comparison breaks the moment rows get sorted or inserted. Compare on
a key column instead: =COUNTIF(Sheet2!A:A,A2)=0 flags rows
missing from the other sheet, and VLOOKUP or XLOOKUP pulls the
other sheet’s value next to the original so you can diff amounts or statuses side by side.
Doing this in the browser
The same formulas work in TableDI, an online
data table: load both sheets and use the formula panel — IF,
COUNTIF, VLOOKUP — to build the diff without desktop Excel.
Comparing two separate files instead of two tabs? See
compare Excel files online for the
cross-workbook version of this workflow.
Which method should you use?
- Full-grid audit — the diff sheet with the IF formula.
- Visual review before a meeting — conditional formatting in place.
- Rows were sorted or added — key-column matching with COUNTIF.
FAQ
How do I compare two sheets in the same Excel workbook?
Add a third sheet and fill it with =IF(Sheet1!A1<>Sheet2!A1,"DIFF","") starting at A1. Every cell that differs between the two sheets shows DIFF, and identical cells stay blank — copy the formula across the whole used range to scan everything at once.
Can Excel highlight differences between two sheets automatically?
Yes, with conditional formatting. Select the data range on Sheet1, add a new rule with the formula =A1<>Sheet2!A1, and pick a fill color. Cells that no longer match Sheet2 light up in place — no helper sheet needed.
How do I find rows that are on one sheet but missing from the other?
Use COUNTIF on a key column: =COUNTIF(Sheet2!A:A,A2)=0 on Sheet1 flags rows whose key never appears on Sheet2. Filter for TRUE to list every missing row, then repeat in the other direction.