Compare two Excel sheets by ID: 4 methods tested on 40 changes

You have last month’s export and this month’s, and you need to know which rows are new, which are gone and which cells changed. Many quick methods compare the files position by position, so one sort or one inserted row makes almost everything look different. In our test on two 300-row sample price lists, a same-cell comparison flagged 1,445 of the 1,500 non-ID cells. We had planted 40 changes.

The fix is to match rows on an ID column, not on their position. This guide shows four ways to do that comparison, what each one found on the same pair of files, and where each one breaks. It ends with a script you can run on your own exports. Sources were read on October 24, 2026.

Position comparisons break on the first sort

A spreadsheet has no idea that row 14 in one file “is” row 17 in the other. Any method that lines up cell B14 with cell B14 assumes both files list the same records in the same order, with the same columns in the same places. Real exports rarely keep that promise. A system adds a column, sorts by date instead of name, or drops a discontinued product, and every row below it shifts.

Microsoft’s own comparison tool works sheet by sheet. Spreadsheet Compare compares “a worksheet for each file” with “the worksheet in the other file, beginning with the leftmost worksheet in each”. The page does not say how inserted or reordered rows are handled. The tool is “available only in Excel for Windows in Microsoft 365 Apps for enterprise plans and equivalent editions”.

The Inquire tab’s Compare Files command opens the same tool and needs both workbooks open in Excel. We could not run either one, so we cannot tell you how they cope with a sorted file.

What you want is a keyed comparison. Take a column whose value identifies a row, such as a SKU, an invoice number or an employee ID. Find each ID in both files, and only then compare the other cells. Columns should be matched by their header names, so a swapped column does not register as a change.

Our test: two price lists with 40 planted changes

We generated two workbooks with a short Python script. Every product, price and date in them is made up. The old file has 300 rows with six columns: sku, name, category, price, stock and updated. The new file started as a copy, and then we made these changes and wrote each one to an answer key:

Change	Count	Real change?
Rows removed	10	Yes
Rows added	10	Yes
Cells changed: 6 prices, 5 stock counts, 3 categories, 2 dates, 2 names	18	Yes
Names changed only in letter case (Monitor Arm C1 to MONITOR ARM C1)	2	Yes
Names with a trailing space added	5	No, cosmetic
Stock numbers stored as text (226 to "226")	3	No, cosmetic

That makes 40 real changes and 8 cosmetic differences. The new file was then shuffled into a random order, and its price and stock columns were swapped, as happens when an export template changes.

We ran four methods on the pair on October 24, 2026. Three were formulas, recalculated in LibreOffice Calc 24.2.7.2; the fourth was the script below, on Python 3.11 with openpyxl 3.1.5. A scoring script compared each result with the answer key.

Method	Real changes found (of 40)	False alarms	Cosmetic differences
Same cell, =New!B2<>Old!B2	Unusable	1,445 of 1,500 cells flagged	Lost in the noise
Lookup on sku, compared with <>	38	8	Reported as changes
Lookup on sku, compared with EXACT(TRIM())	40	0	Hidden
Our script, keyed on sku	40	0	All 8 listed separately

The rest of this guide goes through each method, starting with the formulas, because they need no installation.

Method 1: the lookup formula finds most changes

Put both sheets in one workbook, named Old and New. Then add two helper sheets. On the first, next to each row of New, a COUNTIF tells you whether the ID exists in the old sheet at all. In a sheet called Lookup, cell A2:

=IF(COUNTIF(Old!$A$2:$A$301,New!$A2)=0,"added","")

On a second helper sheet, the same test the other way round marks removed rows:

=IF(COUNTIF(New!$A$2:$A$301,Old!$A2)=0,"removed","")

For the changed cells, INDEX and two MATCH calls fetch the old value from the row with the same ID and the column with the same header. In Lookup, cell B2, filled right to column F and down:

=IF($A2="added","",IF(INDEX(Old!$A$1:$F$301,MATCH(New!$A2,Old!$A$1:$A$301,0),MATCH(New!B$1,Old!$A$1:$F$1,0))<>New!B2,"diff",""))

Adjust the ranges to your row count. In our run this found all 10 added rows, all 10 removed rows and 18 of the 20 changed cells. It took the swapped columns in its stride, because the header MATCH looks each column up by name.

It got 10 things wrong. It missed both case-only name changes, because in our LibreOffice run = treated MONITOR ARM C1 and Monitor Arm C1 as equal. Microsoft’s own COUNTIF page also says “criteria aren’t case sensitive”, so the added and removed checks share the blind spot: sku-0001 and SKU-0001 count as one ID. And it flagged all 8 cosmetic differences as changes: the trailing spaces and the numbers stored as text.

If your Excel has XLOOKUP, it can replace the INDEX and MATCH pair for a single column, for example =XLOOKUP($A2,Old!$A$2:$A$301,Old!$D$2:$D$301,"not found"). Microsoft’s XLOOKUP page says it matches exactly by default and “is not available in Excel 2016 and Excel 2019”. Our LibreOffice version returned #NAME? for it, so that line is untested.

Method 2: EXACT and TRIM silence the false alarms

Wrapping both values in TRIM and comparing them with EXACT changes the result. Replace the comparison in cell B2 with this:

=IF($A2="added","",IF(EXACT(TRIM(INDEX(Old!$A$1:$F$301,MATCH(New!$A2,Old!$A$1:$A$301,0),MATCH(New!B$1,Old!$A$1:$F$1,0))),TRIM(New!B2)),"","diff"))

On our files it found all 40 real changes with no false alarms. EXACT “is case-sensitive but ignores formatting differences”, so the two case-only edits now show up. TRIM removes the trailing spaces. In our run, turning both sides into text also made 226 and "226" equal.

The cost is that the 8 cosmetic differences vanish entirely. Most of the time that is what you want. But they are not always harmless: Microsoft’s COUNTIF page warns that leading and trailing spaces cause unexpected results, so a stray space can break the next lookup someone builds on the file.

TRIM has two more limits. It removes “all spaces from text except for single spaces between words”, so it also hides doubled spaces inside a value. And Microsoft says it “does not remove” the nonbreaking space (character 160), so that one still shows as a change.

The limits all lookup formulas share

Duplicate IDs fail silently. MATCH and XLOOKUP return the first row they find; Microsoft’s XLOOKUP page says it “returns the item corresponding to the first match it finds”. If an ID appears twice, the second row is never compared, and nothing tells you. Check first with =COUNTIF($A$2:$A$301,A2)>1 filled down each sheet.

Lost leading zeros look like a removal plus an addition. If one export holds 00123 and the other 123, the formulas report one row removed and one added. Our guide to Excel CSV imports that mangle zeros and dates explains why it happens and how to import IDs as text.

Long text and wildcards break COUNTIF. Microsoft says it “returns incorrect results when you use it to match strings longer than 255 characters”. It also reads ? and * as wildcards, so an ID like AB*12 needs a tilde before the asterisk.

Google Sheets has the same functions, but conditional formatting cannot point at another sheet directly. Google’s help says “to reference another sheet in the formula, use the INDIRECT function”. We did not test the formulas in Sheets.

Method 3: a script that reports every difference by kind

For a comparison you repeat every month, a script is easier to trust. The one below reads two .xlsx or .csv files and matches rows on the key column you name and columns on their headers. It writes a report workbook with five sheets: Summary, Added, Removed, Changed and Cosmetic. It needs Python 3 and one library:

pip install openpyxl

Save this as compare_sheets.py:

#!/usr/bin/env python3
"""Compare two spreadsheets by a key column: added, removed and changed rows.

Usage: python3 compare_sheets.py old.xlsx new.xlsx --key sku [--sheet NAME] [--out diff.xlsx]
Reads .xlsx or .csv (UTF-8). Columns are matched by header name, rows by the key.
Exit code: 0 no differences, 1 differences found, 2 the files cannot be compared.
"""
import argparse
import csv
import datetime as dt
import math
import sys
from pathlib import Path

import openpyxl


def fail(msg):
    print(f"error: {msg}", file=sys.stderr)
    sys.exit(2)


def load(path, sheet):
    p = Path(path)
    if not p.exists():
        fail(f"{path}: file not found")
    if p.suffix.lower() == ".csv":
        with open(p, newline="", encoding="utf-8-sig") as f:
            rows = list(csv.reader(f))
    else:
        wb = openpyxl.load_workbook(p, read_only=True, data_only=True)
        if sheet and sheet not in wb.sheetnames:
            fail(f"{path}: no sheet named {sheet!r} (has {wb.sheetnames})")
        ws = wb[sheet] if sheet else wb.worksheets[0]
        rows = [list(r) for r in ws.iter_rows(values_only=True)]
        wb.close()
    # Keep each row's number in the file, then drop empty rows.
    rows = [(n, r) for n, r in enumerate(rows, start=1) if any(v not in (None, "") for v in r)]
    if not rows:
        fail(f"{path}: the sheet is empty")
    header = ["" if h is None else str(h).strip() for h in rows[0][1]]
    dupes = sorted({h for h in header if h and header.count(h) > 1})
    if dupes:
        fail(f"{path}: duplicate column names {dupes}")
    width = len(header)
    body = [(n, (list(r) + [None] * width)[:width]) for n, r in rows[1:]]
    return header, body


def key_of(v):
    if v is None:
        return ""
    if isinstance(v, float) and v.is_integer():
        v = int(v)
    return str(v).strip()


def index(path, header, body, key):
    if key not in header:
        fail(f"{path}: no column named {key!r} (has {[h for h in header if h]})")
    k = header.index(key)
    rows, seen, blank = {}, {}, 0
    for n, r in body:
        kv = key_of(r[k])
        if not kv:
            blank += 1
            continue
        if kv in rows:
            seen.setdefault(kv, [rows[kv][0]]).append(n)
            continue
        rows[kv] = (n, dict(zip(header, r)))
    if seen:
        sample = "; ".join(f"{kv} on rows {ns}" for kv, ns in list(seen.items())[:5])
        fail(f"{path}: {len(seen)} key(s) appear more than once, so rows cannot be matched: {sample}")
    if blank:
        print(f"warning: {path}: {blank} row(s) with an empty {key!r} were skipped", file=sys.stderr)
    return rows


def is_num(v):
    return isinstance(v, (int, float)) and not isinstance(v, bool)


def as_num(s):
    try:
        x = float(s.strip().replace(",", ""))
    except ValueError:
        return None
    return x if math.isfinite(x) else None


def show(v):
    if isinstance(v, dt.datetime):
        return v.date().isoformat() if v.time() == dt.time() else v.isoformat()
    return repr(v)


def same_num(a, b):
    return math.isclose(a, b, rel_tol=1e-9, abs_tol=1e-9)


def classify(a, b):
    """None if equal, else 'changed' or a cosmetic kind."""
    a = None if a == "" else a
    b = None if b == "" else b
    if a is None or b is None:
        return None if a is b else "changed"
    if is_num(a) and is_num(b):
        return None if same_num(a, b) else "changed"
    if isinstance(a, dt.datetime) and isinstance(b, dt.datetime):
        return None if a == b else "changed"
    if (is_num(a) and isinstance(b, str)) or (is_num(b) and isinstance(a, str)):
        num, text = (a, b) if is_num(a) else (b, a)
        x = as_num(text)
        if x is not None and same_num(num, x):
            return "number stored as text"
        return "changed"
    sa, sb = str(a), str(b)
    if sa == sb:
        return None
    if sa.strip() == sb.strip():
        return "whitespace only"
    xa, xb = as_num(sa), as_num(sb)
    if xa is not None and xb is not None and same_num(xa, xb):
        lost_zero = any(s.strip().startswith("0") and len(s.strip()) > 1 and s.strip()[1] != "." for s in (sa, sb))
        return "changed" if lost_zero else "number written differently"
    return "changed"


def main():
    ap = argparse.ArgumentParser(description=__doc__.splitlines()[0])
    ap.add_argument("old")
    ap.add_argument("new")
    ap.add_argument("--key", required=True, help="header of the column that identifies a row")
    ap.add_argument("--sheet", help="sheet name in both files (default: the first sheet)")
    ap.add_argument("--out", default="diff-report.xlsx")
    a = ap.parse_args()
    if Path(a.out).resolve() in (Path(a.old).resolve(), Path(a.new).resolve()):
        fail("--out must not be one of the input files")

    oh, ob = load(a.old, a.sheet)
    nh, nb = load(a.new, a.sheet)
    old = index(a.old, oh, ob, a.key)
    new = index(a.new, nh, nb, a.key)

    common = [h for h in oh if h and h in nh and h != a.key]
    cols_removed = [h for h in oh if h and h not in nh]
    cols_added = [h for h in nh if h and h not in oh]
    removed = [k for k in old if k not in new]
    added = [k for k in new if k not in old]
    changed, cosmetic = [], []
    for k in old:
        if k not in new:
            continue
        for h in common:
            kind = classify(old[k][1][h], new[k][1][h])
            if kind == "changed":
                changed.append((k, h, old[k][1][h], new[k][1][h]))
            elif kind:
                cosmetic.append((k, h, old[k][1][h], new[k][1][h], kind))

    # A key that differs only by leading zeros usually means a program stripped them.
    stripped = {k.lstrip("0"): k for k in added if k.isdigit()}
    zero_pairs = [(k, stripped[k.lstrip("0")]) for k in removed if k.isdigit() and k.lstrip("0") in stripped]

    out = openpyxl.Workbook()
    s = out.active
    s.title = "Summary"
    for row in [("old file", a.old), ("new file", a.new), ("key", a.key),
                ("rows in old", len(old)), ("rows in new", len(new)),
                ("rows added", len(added)), ("rows removed", len(removed)),
                ("cells changed", len(changed)), ("cosmetic differences", len(cosmetic)),
                ("columns added", ", ".join(cols_added)), ("columns removed", ", ".join(cols_removed))]:
        s.append(row)
    for title, hdr, data in [
        ("Added", ["new row"] + nh, [[new[k][0]] + [new[k][1][h] for h in nh] for k in added]),
        ("Removed", ["old row"] + oh, [[old[k][0]] + [old[k][1][h] for h in oh] for k in removed]),
        ("Changed", [a.key, "column", "old value", "new value"], changed),
        ("Cosmetic", [a.key, "column", "old value", "new value", "kind"], cosmetic),
    ]:
        ws = out.create_sheet(title)
        ws.append(hdr)
        for r in data:
            ws.append(list(r))
    try:
        out.save(a.out)
    except OSError as e:
        fail(f"cannot write {a.out} (is it open in Excel?): {e}")

    print(f"{len(old)} rows in old, {len(new)} in new, matched on {a.key!r}")
    print(f"added {len(added)}, removed {len(removed)}, cells changed {len(changed)}, cosmetic {len(cosmetic)}")
    if cols_added or cols_removed:
        print(f"columns added {cols_added}, removed {cols_removed}")
    for o, n in zero_pairs:
        print(f"warning: key {o} removed and {n} added; leading zeros may have been lost", file=sys.stderr)
    for k, h, ov, nv in changed[:10]:
        print(f"  {k} {h}: {show(ov)} -> {show(nv)}")
    if len(changed) > 10:
        print(f"  ... {len(changed) - 10} more in {a.out}")
    print(f"report: {a.out}")
    differs = added or removed or changed or cosmetic or cols_added or cols_removed
    sys.exit(1 if differs else 0)


if __name__ == "__main__":
    main()

Run it with the old file first:

python3 compare_sheets.py old.xlsx new.xlsx --key sku --out diff-report.xlsx

On our test files it printed this (first lines shown):

300 rows in old, 300 in new, matched on 'sku'
added 10, removed 10, cells changed 20, cosmetic 8
  SKU-0002 category: 'Storage' -> 'Paper'
  SKU-0006 price: 214.38 -> 231.53
  SKU-0019 price: 339.36 -> 366.51

The scoring script confirmed it: 40 of 40 real changes, 0 false alarms, and the 8 cosmetic differences on their own sheet, labeled “whitespace only” or “number stored as text”. On two generated files of 100,000 rows and four columns each, with 100 changed cells, it ran in about 10 seconds on our 4-core cloud machine.

How the script avoids the formula traps

Each design choice answers one of the failures above:

• Duplicate keys stop the run. If an ID appears twice in either file, the script prints the rows and exits with code 2 instead of guessing. In our test with a duplicated ID, it reported 2 on rows [4, 6].
• Lost leading zeros get a warning. When 00123 disappears and 123 appears, it reports both and adds leading zeros may have been lost.
• Case counts. MONITOR ARM C1 is a change. Spaces at the ends and a number stored as text are reported, but as cosmetic.
• Numbers compare as numbers. 20 and 20.0 are equal, and an ID stored as a number in one file still matches the same ID stored as text in the other.
• Exit codes work in a scheduled job. 0 means no differences, 1 means differences, 2 means the files could not be compared (missing file, missing key column, empty sheet, duplicate keys).

We also ran it on an empty file, a header-only file, a misspelled key column and an output path equal to an input. None crashed: the header-only file gave a normal report, and the other three stopped with a one-line error. If you run it on a schedule, our piece on scheduled scripts that fail silently covers how to make sure a failure reaches you.

Know its limits too. It compares stored values, not formulas or formatting, and it reads one sheet per file. For a cell with a formula, openpyxl’s documentation says it gets the value stored “the last time Excel read the sheet”. A file written by a program that never calculated its formulas may therefore show empty cells.

A date in an .xlsx and the same date as text in a .csv count as different, so compare like with like. And the script needs a key. Without a column that identifies each row, no method here can tell an edited row from a deleted one plus a new one.

Which method to use

• Files you compare once, and you can see they share the same order: the same-cell formula in conditional formatting is fine. Sort both by the ID first and check that the row counts match.
• A one-off comparison of reordered exports: Method 2 (EXACT and TRIM) in a helper sheet, after the duplicate check. Use Method 1 instead if you need to see stray spaces and numbers stored as text.
• Excel for Windows on a Microsoft 365 Apps for enterprise plan, comparing two versions of the same workbook: Spreadsheet Compare. Microsoft says its results also cover formulas and VBA code, which none of the methods above do. We have not tested how it handles reordered rows.
• The same comparison every week or month, or files over a few thousand rows: the script. It gives the same answer every time and does not depend on ranges that someone has to stretch when the file grows.

Whichever you choose, test it once the way we did. Copy a real export, change five cells, delete one row, add one, sort it differently, and check that your method finds exactly those seven differences.

How we made this

On October 24, 2026, we generated two 300-row sample workbooks with 40 planted changes and 8 cosmetic differences, and kept an answer key. We scored four methods against it: three formulas recalculated in LibreOffice Calc 24.2.7.2, and the script in this guide on Python 3.11.17 with openpyxl 3.1.5. We also timed the script on two generated 100,000-row files and ran it on small edge-case files. We read the Microsoft, Google and openpyxl pages listed below that day. We did not have Excel or Google Sheets, so Spreadsheet Compare, Inquire, XLOOKUP and Sheets are described from documentation, untested. The formulas may also behave differently in Excel than in LibreOffice.

Sources

• Microsoft Support, Compare two versions of a workbook by using Spreadsheet Compare, read October 24, 2026
• Microsoft Support, Compare workbooks using Spreadsheet Inquire, read October 24, 2026
• Microsoft Support, XLOOKUP function, read October 24, 2026
• Microsoft Support, COUNTIF function, read October 24, 2026
• Microsoft Support, EXACT function, read October 24, 2026
• Microsoft Support, TRIM function, read October 24, 2026
• Google Docs Editors Help, Use conditional formatting rules in Google Sheets, read October 24, 2026
• openpyxl, Tutorial: loading from a file and data_only, version 3.1.5, read October 24, 2026
• 28creation test, October 24, 2026: two generated workbooks of 300 rows, 40 planted changes and 8 cosmetic differences, scored against an answer key by our own script