How to compare two Excel sheets for differences
By the Analistable team · Updated · 3 min read
If both sheets have the same layout, add a new sheet and enter =IF(Sheet1!A1<>Sheet2!A1, Sheet1!A1&" | "&Sheet2!A1, "") in A1, then fill it across the used range — every non-empty cell is a difference. To highlight changes in place, use a conditional formatting rule =A1<>Sheet1!A1. If rows may have been inserted or re-sorted, compare by a key column instead.
Part of our guide: How to compare two spreadsheets for differences and matches
Same layout or different order?
A cell-by-cell comparison assumes row 5 in one sheet describes the same record as row 5 in the other. That's true for two versions of a fixed report. It's false when someone inserted a row or sorted the data: every row below the change shows as different. In that case, skip to “Compare by key column” below.
Method 1: a difference sheet
- Insert a new sheet called Diff.
- In A1, enter
=IF(Old!A1<>New!A1, Old!A1&" | "&New!A1, ""). - Fill the formula right and down to cover the same range as the data.
- Every cell that shows text is a difference, showing the old and new value.
| A (SKU) | B (Price) | C (Stock) | |
|---|---|---|---|
| Row 2 | |||
| Row 3 | 12.50 | 13.00 | ||
| Row 4 | 40 | 0 |
Add =COUNTIF(Diff!A1:Z500, "?*") somewhere to count how many cells differ.
Method 2: highlight differences with conditional formatting
- On the New sheet, select the data range starting at A1.
- Choose Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Enter
=A1<>Old!A1(relative references, no $ signs). - Click Format, choose a fill colour and click OK.
Changed cells light up on the New sheet. Older Excel versions don't allow references to other sheets in conditional formatting; in that case define a name for the Old range and refer to it.
Method 3: compare by key column
When rows can move, match them by an ID first. In the New sheet, add a column:
=LET(old, XLOOKUP(A2, Old!A:A, Old!C:C, "NEW ROW"), IF(old="NEW ROW", old, IF(old<>C2, "Changed from "&old, "")))This looks up each SKU in the Old sheet and reports whether the price changed or the row is new. Rows removed from Old are found the other way round with =IF(COUNTIF(New!A:A, A2)=0, "Removed", ""). For a refreshable report of added, removed and changed rows, Power Query anti joins work well — see comparing two spreadsheets.
Method 4: Excel's built-in tools
- View Side by Side (View tab) shows two workbooks next to each other with synchronous scrolling. For two sheets in one workbook, first choose View → New Window.
- Spreadsheet Compare (Inquire add-in) produces a full report of changed values, formulas and formatting. It's included with Microsoft 365 Apps for enterprise and Office Professional Plus on Windows.
Avoid false differences
- Trailing spaces: compare
TRIM(A1)withTRIM(Old!A1). - Numbers vs text: “100” and 100 differ. Convert with VALUE first.
- Calculated decimals: compare
ROUND(A1,2)values. - Case:
<>ignores case. UseNOT(EXACT(A1, Old!A1))when case matters.
Drop both files into our free compare tool to list rows that exist in only one of them, without formulas or uploads.
Frequently asked questions
- How do I compare two Excel sheets and highlight the differences?
- Select the range on one sheet, add a conditional formatting rule with the formula =A1<>OtherSheet!A1, and choose a fill colour.
- Is there a built-in tool to compare two Excel files?
- Spreadsheet Compare (Inquire) is included in some Windows editions, such as Microsoft 365 Apps for enterprise. Other editions can use formulas, conditional formatting or View Side by Side.
- How do I compare two sheets when the rows are in a different order?
- Compare by a key column with XLOOKUP or COUNTIF rather than cell by cell.