Skip to content
Klargrid

Compare two Excel files

Monday's version and Friday's: what changed? Excel has no button for it, and rows that moved throw off any cell-by-cell comparison.

In Excel, step by step

  1. Side by side

    View, View Side by Side, with synchronous scrolling: for a few dozen rows.

  2. By formula

    In a third sheet: =IF(Monday!A2<>Friday!A2,"≠","") copied over the whole range, then conditional formatting.

  3. By a key

    If a column identifies each row (a code), look up each code of one in the other with XLOOKUP to see what is missing.

Pitfalls

  • An inserted row shifts all the following ones: the cell-by-cell comparison marks everything as different.
  • A number and the same number stored as text are "different" for a formula.
  • The Inquire tool (Spreadsheet Compare) only exists in some editions of Excel for Windows.

Faster, on the whole file

Compare two Excel files aligns rows by a key (or by position), lists added, removed and changed rows, shows every changed cell, and produces an annotated workbook.

Frequently asked questions

And to compare two lists that are not two versions of the same file?
A bank statement and the books, for example: Reconcile two lists.