Back to Blog

How to Compare Two Excel Files for Differences

5 ways to compare two Excel files: side by side, formulas, XLOOKUP by key, Power Query, Sheetmatcher. Which fits, and the traps behind false differences.

Comparing two Excel files? Sheetmatcher finds the differences in seconds, even when rows and columns are in a different order. Your files never leave your browser.

Try it free

Posted by

Comparing two Excel files side by side

First: are your rows in the same order?

Every way to compare two Excel files falls into one of two groups, and picking the wrong one is why most comparisons go wrong:

  • Same layout, same row order (two versions of the same sheet, a budget before and after an edit). Compare cell by cell: A2 against A2, B7 against B7.
  • Same kind of data, different order (an export from your accounting system vs one from your ERP, last month's price list vs this month's). Rows are sorted differently, some are missing, some are new. Cell-by-cell comparison is useless here: one inserted row makes everything below it look different. You need to match records by a key, such as an invoice number, customer ID or SKU.

Methods 1 and 2 below are for the first case. Methods 3 to 5 match by key and work for the second.

Which method should you use?

MethodRows in a different order?Finds missing records?Effort
1. View Side by SideNoNoNone, but only by eye
2. Formula or conditional formattingNoNoLow
3. XLOOKUP / COUNTIF by keyYesYesMedium, one formula per column
4. Power Query mergeYesYesHigh the first time
5. SheetmatcherYesYes, both directionsLow

Method 1: View Side by Side

Open both files, then go to View → View Side by Side. Excel puts the two windows next to each other and turns on Synchronous Scrolling, so both scroll together.

Fine for a quick look at a few dozen rows. Beyond that you will miss things, and it tells you nothing about rows that exist in only one file.

Method 2: A formula or conditional formatting

If both sheets have the same layout and row order, copy them into one workbook (say Old and New), add a third sheet and put this in A1, then fill it across and down:

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

Every non-empty cell is a difference. To highlight differences directly in the New sheet instead, select its data from A1, choose Home → Conditional Formatting → New Rule → Use a formula, and enter:

=A1<>Old!A1

Both only work while the rows line up. Insert one row in either sheet and every row below it shows up as different.

Method 3: Match by key with COUNTIF and XLOOKUP

This is what most finance teams end up doing. Say both sheets have the invoice number in column A and the amount in column C.

Find records missing from the other file

In a helper column of sheet A:

=IF(COUNTIF(B!A:A, A2)=0, "Missing in B", "")

Then do the same the other way round in sheet B, otherwise you'll never see the records that exist only in B.

Find different values

Look up the amount from B for each invoice and compare it with A (XLOOKUP needs Excel 2021 or Microsoft 365; use INDEX/MATCH in older versions):

=LET(b, XLOOKUP(A2, B!A:A, B!C:C, "missing"), IF(b="missing", "Missing in B", IF(b<>C2, "Different: "&b, "")))

Repeat for every column you want to check. Watch out for these traps, which cause most of the "differences" that aren't real:

  • Numbers stored as text. 1250.5 and "1,250.50" are not equal to Excel. Convert with VALUE or Text to Columns first.
  • Dates as text. An export with "01.09.2026" won't match a real Excel date.
  • Extra spaces. "INV-1001 " doesn't match "INV-1001". Wrap keys in TRIM.
  • Duplicate keys. XLOOKUP silently returns the first match. Check with =COUNTIF(A:A, A2)>1.
  • Keys made of several columns (customer + date). You need a helper column that joins them, in both sheets.

If formulas go wrong along the way, our Excel formula troubleshooting checklist covers the usual suspects.

Method 4: Power Query merge

For large files or a comparison you repeat every month, Power Query is sturdier than formulas:

  1. Load both files with Data → Get Data → From File → From Workbook.
  2. Set the column types (number, date, text) in both queries. This fixes most format problems.
  3. Home → Merge Queries, select the key column in both, and pick the join kind:
    • Left Anti: records in A that are missing in B (swap the tables for the other direction)
    • Inner: records in both, then expand B's columns and add a custom column such as [Amount] <> [B.Amount] to flag differences
  4. Close & Load. Next month, replace the files and click Refresh.

Powerful, but expect an hour or two the first time, and a separate check for each column you care about.

Method 5: Sheetmatcher

Sheetmatcher does method 3 for every column at once, without formulas. You drop in the two files, pick the key column(s), and get one table with every record marked as matching, different, missing in B or only in B.

  • Rows and columns can be in any order.
  • Number and date formats are normalised before comparing, so 1,250.50 equals 1250.5 and 01.09.2026 equals the Excel date.
  • Keys can combine several columns, and duplicate keys are flagged, not hidden.
  • Everything runs in your browser. Your files are never uploaded to a server, which matters for customer and financial data.

It's free to try with sample files, no sign-up needed. The full Excel report of the differences is part of the paid plan. Why row-by-row comparison tools miss real differences explains the idea in more detail.

Summary

  • Two versions of the same sheet, same order: conditional formatting (method 2).
  • Exports from different systems, a one-off: COUNTIF + XLOOKUP (method 3), and check both directions.
  • The same reconciliation every month: Power Query (method 4), or Sheetmatcher if you don't want to build and maintain queries.

Skip the formulas: compare your files in 30 seconds

Sheetmatcher lines up two Excel files by a key column and shows every difference, missing and new record, even with shuffled rows and different number or date formats. It runs in your browser, so your files are never uploaded.

Try it with sample filesNo sign-up. Your first comparison with your own files is free too.