How to compare two Excel sheets for differences

First answer one question: are the rows in the same order in both sheets?

• Yes, same layout (two versions of the same report, nobody inserted or sorted rows) – compare cell by cell. Use conditional formatting or Spreadsheet Compare.
• No, or you are not sure (two exports, last month vs this month, old system vs new system) – compare by key: match rows on an ID column first, then compare the values. Use lookup formulas, Power Query or a key-based compare tool.

Getting this wrong is the most common reason a comparison "finds" thousands of differences that are not there. If one row is inserted near the top, a cell-by-cell comparison flags every row below it:

One real change, three reported differences. With 50,000 rows, that is 50,000 false alarms.

1. Highlight differences with conditional formatting (same layout)

Works in every Excel version and in Google Sheets. Put both versions in one workbook, in sheets named Old and New.

1. Go to sheet New and select the used range starting at A1 (for example A1:H5000).
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Enter the formula below, pick a fill colour, and click OK.

=A1<>Old!A1

Keep the references relative (no $ signs) so each cell compares itself with the same address in Old. Every changed cell is now coloured.

If you would rather have a list than colours, add a third sheet and put this in A1, then fill it across and down:

=IF(Old!A1<>New!A1, Old!A1 & " → " & New!A1, "")

Watch out: Excel's = and <> ignore letter case, so ACME and Acme count as equal. If case matters, use =NOT(EXACT(A1, Old!A1)) instead.

2. Find missing rows and changed values with formulas (by key)

Use this when the rows can be in a different order. (Only comparing a single column of values? See how to compare two lists in Excel.) You need a key column that identifies each row – customer ID, SKU, invoice number, email. Below the key is in column A of both sheets.

Rows missing from the new sheet – in sheet Old, in the first empty column:

=IF(COUNTIF(New!A:A, A2)=0, "missing in New", "")

Rows added in the new sheet – in sheet New:

=IF(COUNTIF(Old!A:A, A2)=0, "added", "")

Changed values – to check one column (say D) in sheet New:

=IFERROR(IF(XLOOKUP(A2, Old!A:A, Old!D:D) <> D2, "changed", ""), "")

XLOOKUP needs Microsoft 365 or Excel 2021 and later. In older versions use INDEX/MATCH:

=IFERROR(IF(INDEX(Old!D:D, MATCH(A2, Old!A:A, 0)) <> D2, "changed", ""), "")

Checking the whole row at once. Writing one formula per column gets tedious. Instead, add a helper column to both sheets that glues the row together (here in column I, for data in B–H), then compare just that column by key:

=TEXTJOIN("|", FALSE, B2:H2)

=IFERROR(IF(XLOOKUP(A2, Old!A:A, Old!I:I) = I2, "", "changed"), "added")

This tells you which rows changed, not which cells. The columns must be in the same order in both sheets for the helper to line up.

Watch out for: keys stored as numbers in one sheet and text in the other (1001 vs '1001 never match), trailing spaces (wrap keys in TRIM()), and duplicate keys – COUNTIF and XLOOKUP only see the first match.

3. Power Query: missing rows without formulas

Power Query (Data → Get Data, built into Excel 2016 and later on Windows) handles larger sheets better than formulas and can be refreshed when the data changes.

1. Click inside the old data and choose Data → From Table/Range. Close and load it as a connection. Repeat for the new data.
2. Data → Get Data → Combine Queries → Merge. Pick the new table on top, the old table below, and click the key column in both.
3. For Join Kind choose Left Anti (rows only in first). The result is every row added in the new sheet.
4. Repeat with the tables swapped (or Right Anti) to get the rows that were removed.

For changed values, merge with Inner join, expand the old columns and add a custom column that compares each pair. It works, but it is a lot of clicking for a wide table.

4. Microsoft Spreadsheet Compare

Microsoft ships a separate program that compares two workbooks and reports changed values, formulas, macros and formatting. According to Microsoft's documentation, it is only available with Office Professional Plus 2013, 2016 and 2019, and with Microsoft 365 Apps for enterprise – so most home and small-business installs do not have it. It runs on Windows only.

Open it from the Start menu (type "Spreadsheet Compare"), choose Home → Compare Files, pick the older and the newer workbook, and run the comparison.

It is excellent for checking that a model's formulas did not change. For data exports it has the same weakness as conditional formatting: it compares cell positions, so inserted or re-sorted rows show up as changes.

5. Save as CSV and compare by key in the browser

For exports with thousands or millions of rows, formulas get slow and Excel itself stops at 1,048,576 rows per sheet. A key-based compare tool does steps 2 and 3 in one go.

1. In each workbook, open the sheet and choose File → Save As → CSV UTF-8 (Comma delimited). Excel saves only the active sheet.
2. Open Calamo's free compare tool, drop both CSV files, and pick the key column (or several columns, if no single one is unique).
3. You get added, removed and changed rows, and for each changed row the exact cells with the old and new value. Column order does not matter, and you can ignore whitespace, letter case and number formatting.

The files are read inside your browser and never uploaded, which matters when the sheets contain customer or payroll data.

Before saving as CSV: Excel writes values the way they are displayed. Long numeric IDs in General format can turn into 1.23457E+15, and a column formatted to two decimals loses the rest. Format ID columns as Text (or Number with 0 decimals) first.

Which method should you use?

Working with CSV files from the start? See how to compare two CSV files, including pandas and command-line options.

Questions