First decide: same cells, or same rows?

There are two quite different questions hiding behind “compare two sheets”, and picking the wrong method is why most comparisons go wrong.

Same cells. Both sheets have exactly the same layout, and you want to know whether C14 on one equals C14 on the other. This is true of a template that two people filled in, or of last month’s and this month’s copy of a fixed report.
Same rows. The sheets hold records (products, invoices, staff, customers) and rows may have been added, deleted or re-sorted. Comparing cell by cell is useless here: one inserted row near the top makes every row below it look different. You need to match each row to its partner by an identifier first, then compare the cells.

RowDiff does the second kind by default (choose a key column, or let it align rows in order like a text diff), and it handles the first kind too: with matching by position, two sheets with an identical layout line up row for row.

Method 1: a formula sheet (same layout only)

Add a new sheet to the workbook that holds both sheets (call them Old and New). In cell A1 of the new sheet, enter:

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

Fill it right and down to cover the used range. Every cell that differs now shows both values; matching cells stay blank. It is quick and it needs nothing installed, but it only works when both sheets are laid out identically, it says nothing about rows that moved, and with many columns the result sheet is hard to read.

Method 2: conditional formatting

To colour the differences in place, select the data on the New sheet starting from A1, choose Home › Conditional Formatting › New Rule › Use a formula to determine which cells to format, and enter =A1<>Old!A1 with a fill colour. Same limitation: it compares positions, not records. Sort one sheet differently and the whole sheet lights up.

Method 3: look up each row by its ID

When rows can move, match them by an identifier such as a SKU, invoice number or email address. On the New sheet, next to each row, look up the old value with XLOOKUP (or VLOOKUP in older Excel):

=XLOOKUP(A2, Old!A:A, Old!C:C, "not in old")

then compare it with the current value: =IF(C2=XLOOKUP(A2, Old!A:A, Old!C:C, ""), "", "changed"). You need one pair of formulas per column you care about, a second pass in the other direction to find rows that were deleted, and care with repeated IDs (a lookup only ever finds the first match). It works, but it is slow to build and easy to get subtly wrong.

Method 4: RowDiff (any layout, any order)

Drop the first workbook on A and the second on B. If both sheets live in one workbook, open it on A and click Or compare another sheet of file A.
Choose the sheet on each side. Columns are matched by their header names, so they can be in a different order.
Pick the column that identifies a row under Match rows by. RowDiff suggests one when a column is unique on both sides and looks like an ID. Add a second column if a row is only unique by two values together (an order number plus a line number, say).
Read the result: changed rows with each changed cell marked (old value struck through, new value below), added rows in green, removed rows in red. Click the counts to filter.

Numbers within a tolerance you set can count as equal, so rounding noise like 1.4500000000000002 against 1.45 doesn’t clutter the result, and you can ignore case and stray spaces. Switch to Formulas to see where a formula was edited even if it still returns the same number.

Open both files in RowDiff above

What gets compared

Cell values (as numbers, text, dates and TRUE/FALSE) or, in formula mode, the formulas themselves. Formatting, colours, comments and column widths are not compared. Hidden rows and columns are compared like any other. Blank rows are skipped, and the first non-blank row is used as the header row unless you untick First row is headers.

Comparing on screen is free for sheets up to 5,000 rows. To hand the result on, download a highlighted .xlsx report (changed cells filled, the old value in a cell note, a sheet listing every changed cell) or a CSV of just the differences. One credit covers a pair of sheets, with downloads repeated as often as you like.

Compare two Excel sheets