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
- 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.
Which method should you use?
| Situation | Best method | Why |
|---|---|---|
| Two versions of a fixed-layout report | Difference sheet or conditional formatting | Rows line up, so cell-by-cell is accurate |
| Exports where rows were added, removed or re-sorted | Compare by key column | Rows are matched by ID, not position |
| A financial model where formulas may have changed | Spreadsheet Compare | It reports formula and formatting changes, not just values |
| A quick visual scan of a short sheet | View Side by Side | No formulas to set up |
| The same comparison every month | Power Query joins | Build once, refresh with new files |
Comparing sheets in two different workbooks
- Open both workbooks.
- In the workbook where you want the result, add a Diff sheet.
- 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. - 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:
| SKU | Price v1 | Price v2 | Result |
|---|---|---|---|
| DK-100 | 120.00 | 120.00 | |
| CH-200 | 45.00 | 48.00 | Changed from 45 |
| LP-300 | 18.50 | 18.50 | |
| SH-400 | 60.00 | Removed | |
| ST-500 | 32.00 | NEW 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
| Symptom | Cause | Fix |
|---|---|---|
| Every row below row 10 is different | A row was inserted in one version | Compare by a key column instead |
| Identical-looking cells flagged | Hidden spaces or text-formatted numbers | Compare TRIM and VALUE results |
| Conditional formatting rule does nothing | Absolute references ($A$1) in the rule | Use relative A1 references |
| Formula shows #REF! | The compared sheet was renamed or deleted | Re-point the formula to the right sheet |
| Dates differ but look the same | One cell holds a time as well as a date | Compare 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").