Analistable

How to compare two Excel sheets for differences

By the Analistable team · Updated · 5 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

  1. Insert a new sheet called Diff.
  2. In A1, enter =IF(Old!A1<>New!A1, Old!A1&" | "&New!A1, "").
  3. Fill the formula right and down to cover the same range as the data.
  4. Every cell that shows text is a difference, showing the old and new value.
Diff sheet for a price list
A (SKU)B (Price)C (Stock)
Row 2
Row 312.50 | 13.00
Row 440 | 0

Add =COUNTIF(Diff!A1:Z500, "?*") somewhere to count how many cells differ.

Method 2: highlight differences with conditional formatting

  1. On the New sheet, select the data range starting at A1.
  2. Choose Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
  3. Enter =A1<>Old!A1 (relative references, no $ signs).
  4. 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) with TRIM(Old!A1).
  • Numbers vs text: “100” and 100 differ. Convert with VALUE first.
  • Calculated decimals: compare ROUND(A1,2) values.
  • Case: <> ignores case. Use NOT(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.

Which method should you use?

Choosing a comparison method
SituationBest methodWhy
Two versions of a fixed-layout reportDifference sheet or conditional formattingRows line up, so cell-by-cell is accurate
Exports where rows were added, removed or re-sortedCompare by key columnRows are matched by ID, not position
A financial model where formulas may have changedSpreadsheet CompareIt reports formula and formatting changes, not just values
A quick visual scan of a short sheetView Side by SideNo formulas to set up
The same comparison every monthPower Query joinsBuild once, refresh with new files

Comparing sheets in two different workbooks

  1. Open both workbooks.
  2. In the workbook where you want the result, add a Diff sheet.
  3. Type =IF( in A1, then switch to the other workbook and click cell A1 on the sheet to compare. Excel inserts a reference such as [Prices_v1.xlsx]Sheet1!$A$1.
  4. Remove the $ signs so the reference moves when you fill, finish the formula and fill it across the range.

On a Mac, the steps are the same; switch windows from the Window menu. If you close the other workbook, the formulas keep the last values and show the full file path. To avoid broken links later, copy the old sheet into the new workbook first (right-click the tab → Move or Copy → choose the target workbook and tick Create a copy).

Worked example: price list v1 vs v2

A supplier sends a revised price list. Version 1 has 4 SKUs; version 2 has the same SKUs plus one new line, and the rows are sorted differently, so a cell-by-cell diff would flag almost every cell. Comparing by SKU gives a short, accurate answer:

Key-based comparison of two price lists
SKUPrice v1Price v2Result
DK-100120.00120.00
CH-20045.0048.00Changed from 45
LP-30018.5018.50
SH-40060.00Removed
ST-50032.00NEW ROW

Two changes, one removed line and one new line — four rows to review instead of a sheet full of highlights. For a full walkthrough of price-list checks, see comparing two price lists.

Troubleshooting

When the comparison looks wrong
SymptomCauseFix
Every row below row 10 is differentA row was inserted in one versionCompare by a key column instead
Identical-looking cells flaggedHidden spaces or text-formatted numbersCompare TRIM and VALUE results
Conditional formatting rule does nothingAbsolute references ($A$1) in the ruleUse relative A1 references
Formula shows #REF!The compared sheet was renamed or deletedRe-point the formula to the right sheet
Dates differ but look the sameOne cell holds a time as well as a dateCompare INT(A1) values

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.
How do I count how many cells are different?
On a difference sheet, use =COUNTIF(Diff!A1:Z500, "?*"), which counts the cells that contain text.
Can I compare two sheets in Google Sheets the same way?
Yes. A difference tab with =IF(Old!A1<>New!A1, Old!A1&" | "&New!A1, "") works, and conditional formatting accepts a custom formula, though references to another tab must go through INDIRECT, for example =A1<>INDIRECT("Old!A1").

Related guides