Python Doc & Data Automation

Find Differences Between Two Excel Files with Python

A budget model comes back from review as budget-v7-final-JM.xlsx, and someone needs to know exactly what the reviewer changed across its nine sheets. Excel's own "Compare Files" tool is only in some Office editions and cannot be scripted. pandas.DataFrame.equals says False and nothing else. DataFrame.compare raises ValueError: Can only compare identically-labeled (both index and columns) DataFrame objects the moment the reviewer inserted a row — and inserting rows is exactly what reviewers do.

Root Cause

Two versions of a workbook differ along several independent axes at once: sheets added, removed or renamed; rows and columns inserted or deleted, which shifts the position of everything after them; and cell values changed. Positional comparison — cell B12 against cell B12 — reports everything below an inserted row as changed. DataFrame.compare requires identical row and column labels precisely to avoid that ambiguity, so it rejects the input rather than guess. A useful diff therefore needs to align structure first, using a key column for rows and header names for columns, and only then compare values. Workbooks without any key column need a different alignment strategy, which changes what "changed" can mean.

Minimal Diagnostic

Start with the structure: which sheets exist on each side, and for common sheets, how shapes and headers differ. That tells you whether a key-based or a positional comparison is appropriate for each sheet.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

OLD = Path("in/budget-v6.xlsx")
NEW = Path("in/budget-v7-final-JM.xlsx")

def read_all(path: Path) -> dict[str, pd.DataFrame]:
    try:
        return pd.read_excel(path, sheet_name=None, dtype=object)   # every sheet, raw values
    except (OSError, ValueError) as exc:
        raise SystemExit(f"cannot read {path}: {exc}")

def structure_report(old: dict, new: dict) -> None:
    print("sheets removed:", sorted(set(old) - set(new)))
    print("sheets added:  ", sorted(set(new) - set(old)))
    for name in sorted(set(old) & set(new)):
        a, b = old[name], new[name]
        cols_removed = [c for c in a.columns if c not in b.columns]
        cols_added = [c for c in b.columns if c not in a.columns]
        print(f"[{name}] shape {a.shape} -> {b.shape}"
              + (f", columns removed {cols_removed}" if cols_removed else "")
              + (f", columns added {cols_added}" if cols_added else ""))

if __name__ == "__main__":
    structure_report(read_all(OLD), read_all(NEW))

sheets removed: []
sheets added:   ['Scenario B']
[Assumptions] shape (42, 3) -> (42, 3)
[Headcount] shape (118, 14) -> (121, 14)
[Opex] shape (64, 15) -> (64, 16), columns added ['FY27 Q4']
[Summary] shape (20, 6) -> (20, 6)

Headcount gained three rows, so a positional comparison of that sheet would flag nearly everything below the insertion point; Opex gained a column. Both need alignment by label before comparing values.

Positional versus key-aligned comparison The left panel compares rows by position after a new employee row was inserted at row 5, so rows 5 onward all appear changed. The right panel aligns rows on the employee ID column first, so only the inserted row is reported as added and the single salary change on another row is reported as changed. By position (row 5 inserted) row 4 E-104 = E-104 same row 5 E-105 vs E-190 CHANGED row 6 E-106 vs E-105 CHANGED row 7 E-107 vs E-106 CHANGED ... 114 false changes Aligned on employee_id E-104 same E-190 ADDED E-105 same E-106 salary 52000 -> 54500 1 added, 1 changed

Fix: Align Each Sheet by Key and Header, Then Diff Cells

For each common sheet, choose a key column (configured per sheet), set it as the index, align columns by header name, and compare the intersection. Record added and removed rows and columns separately. Changed lines are commented.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import numpy as np
import pandas as pd

OLD = Path("in/budget-v6.xlsx")
NEW = Path("in/budget-v7-final-JM.xlsx")
KEYS = {"Headcount": "employee_id", "Opex": "cost_line", "Assumptions": "parameter"}  # changed
TOLERANCE = 0.005

def as_comparable(value):
    if isinstance(value, float) and np.isnan(value):
        return None
    if isinstance(value, str):
        return value.strip()                                        # changed: ignore stray spaces
    return value

def diff_sheet(name: str, a: pd.DataFrame, b: pd.DataFrame) -> list[dict]:
    key = KEYS.get(name)
    if key is None or key not in a.columns or key not in b.columns:
        return [{"sheet": name, "kind": "skipped", "detail": "no key configured"}]
    a = a.assign(**{key: a[key].astype(str).str.strip()}).set_index(key)   # changed: align rows
    b = b.assign(**{key: b[key].astype(str).str.strip()}).set_index(key)
    if a.index.duplicated().any() or b.index.duplicated().any():
        return [{"sheet": name, "kind": "error", "detail": f"duplicate values in {key}"}]
    changes = []
    for k in b.index.difference(a.index):
        changes.append({"sheet": name, "kind": "row added", "key": k})
    for k in a.index.difference(b.index):
        changes.append({"sheet": name, "kind": "row removed", "key": k})
    for col in b.columns.difference(a.columns):
        changes.append({"sheet": name, "kind": "column added", "column": col})
    for col in a.columns.difference(b.columns):
        changes.append({"sheet": name, "kind": "column removed", "column": col})
    rows = a.index.intersection(b.index)
    cols = a.columns.intersection(b.columns)                         # changed: align columns by name
    for col in cols:
        for k in rows:
            old, new = as_comparable(a.at[k, col]), as_comparable(b.at[k, col])
            if isinstance(old, (int, float)) and isinstance(new, (int, float)):
                same = abs(float(old) - float(new)) <= TOLERANCE      # changed: numeric tolerance
            else:
                same = old == new
            if not same:
                changes.append({"sheet": name, "kind": "cell changed", "key": k,
                                "column": col, "old": old, "new": new})
    return changes

def diff_workbooks(old_path: Path, new_path: Path) -> pd.DataFrame:
    old = pd.read_excel(old_path, sheet_name=None, dtype=object)
    new = pd.read_excel(new_path, sheet_name=None, dtype=object)
    changes = [{"sheet": s, "kind": "sheet removed"} for s in sorted(set(old) - set(new))]
    changes += [{"sheet": s, "kind": "sheet added"} for s in sorted(set(new) - set(old))]
    for name in sorted(set(old) & set(new)):
        changes += diff_sheet(name, old[name], new[name])
    return pd.DataFrame(changes, columns=["sheet", "kind", "key", "column", "old", "new", "detail"])

if __name__ == "__main__":
    try:
        log = diff_workbooks(OLD, NEW)
    except (OSError, ValueError) as exc:
        raise SystemExit(f"comparison failed: {exc}")
    print(log["kind"].value_counts().to_string())

cell changed     11
row added         3
column added      1
sheet added       1

Reading with dtype=object preserves what Excel stored — numbers stay numbers, text stays text — instead of letting pandas coerce a mostly-numeric column to float and turn a text note into NaN. The per-sheet KEYS mapping is the one piece of configuration a comparison genuinely needs; guessing keys automatically is fragile, because the first unique-looking column is often a row number.

Variant Fix 1: Highlight Changes in a Copy of the New Workbook

Reviewers prefer to see changes in place. Open a copy of the new workbook with openpyxl, locate each changed cell by key and header, and apply a fill plus a cell comment holding the old value:

# pip install "openpyxl>=3.1" "pandas>=2.2"
import shutil
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill

CHANGED = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
ADDED = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")

def highlight(new_path: Path, log, keys: dict[str, str], dest: Path) -> None:
    shutil.copyfile(new_path, dest)
    wb = load_workbook(dest)                              # keeps formulas and formatting
    try:
        for sheet, entries in log.groupby("sheet"):
            if sheet not in wb.sheetnames or sheet not in keys:
                continue
            ws = wb[sheet]
            headers = {str(c.value).strip(): c.column for c in ws[1] if c.value is not None}
            key_col = headers[keys[sheet]]
            row_of = {str(ws.cell(r, key_col).value).strip(): r for r in range(2, ws.max_row + 1)}
            for e in entries.itertuples():
                row = row_of.get(str(e.key))
                if e.kind == "cell changed" and row and e.column in headers:
                    cell = ws.cell(row, headers[e.column])
                    cell.fill = CHANGED
                    cell.comment = Comment(f"was: {e.old}", "diff")
                elif e.kind == "row added" and row:
                    for cell in ws[row]:
                        cell.fill = ADDED
        wb.save(dest)
    finally:
        wb.close()

Comments carrying the old value let a reviewer hover to see what changed without switching files. Keep the highlighted copy clearly named — budget-v7-diff.xlsx — so nobody mistakes it for the working model.

The highlighted review copy The review copy keeps the new workbook's layout. The header row is unchanged. An inserted employee row is filled green. A changed salary cell is filled amber and carries a comment reading was 52000. A separate Changes sheet lists every change with sheet, kind, key, column, old and new values so the review can be filtered and signed off. budget-v7-diff.xlsx, Headcount sheet 1 1 Header row unchanged 2 2 Added row E-190 green fill across the row 3 3 Changed cell amber fill, comment: was 52000 4 4 Changes sheet filterable change log

Variant Fix 2: Sheets Without a Key Column

Assumption blocks and free-form summary sheets have no row key. Two practical options: compare by cell address with openpyxl, accepting that inserted rows shift results, or compare by label in the first column when it is unique enough. The address-based version is simple and honest about its limits:

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook

def cell_address_diff(old_path: Path, new_path: Path, sheet: str) -> list[tuple[str, object, object]]:
    a_wb = load_workbook(old_path, data_only=False)       # compare formulas as written
    b_wb = load_workbook(new_path, data_only=False)
    try:
        a, b = a_wb[sheet], b_wb[sheet]
        max_row = max(a.max_row, b.max_row)
        max_col = max(a.max_column, b.max_column)
        diffs = []
        for r in range(1, max_row + 1):
            for c in range(1, max_col + 1):
                va, vb = a.cell(r, c).value, b.cell(r, c).value
                if va != vb:
                    diffs.append((b.cell(r, c).coordinate, va, vb))
        return diffs
    finally:
        a_wb.close()
        b_wb.close()

data_only=False compares formulas rather than their cached results, which is what a model reviewer usually wants: a changed formula matters even when today's result is the same. Switch to data_only=True to compare results — but note that cached values are only present if the file was last saved by Excel, a limitation explained in fix openpyxl data_only formula returns None.

Summarising the Change Log for Sign-Off

Eleven changed cells are easy to read; four hundred are not. Before sending the diff to reviewers, summarise the log by sheet and column, and flag numeric changes by magnitude so material changes surface first:

# pip install "pandas>=2.2"
import pandas as pd

def summarise_log(log: pd.DataFrame, material: float = 1000.0) -> pd.DataFrame:
    cells = log[log["kind"] == "cell changed"].copy()
    old = pd.to_numeric(cells["old"], errors="coerce")
    new = pd.to_numeric(cells["new"], errors="coerce")
    cells["delta"] = new - old
    cells["material"] = cells["delta"].abs() >= material
    return (cells.groupby(["sheet", "column"], dropna=False)
                 .agg(changes=("key", "count"),
                      material_changes=("material", "sum"),
                      net_delta=("delta", "sum"))
                 .sort_values(["material_changes", "changes"], ascending=False)
                 .reset_index())

Put this summary on the first sheet of the review copy. The approver reads the handful of material lines, and the full change log remains one tab away for audit.

Changed cells by sheet in the v6 to v7 review Comparing budget version 6 with version 7 found 6 changed cells on the Headcount sheet, of which 2 were material, 4 on Opex with 3 material, 1 on Assumptions which changed a growth rate, and none on Summary, whose values changed only through formulas. budget-v6 vs budget-v7-final-JM, changed input cells Headcount (2 material) 6 cells Opex (3 material) 4 cells Assumptions (growth rate) 1 cells Summary (formulas only) 0 cells

Verification

Test the comparison against a pair of files where the differences are known, created in code, so the expected change log is exact:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def make_fixture(tmp: Path) -> tuple[Path, Path]:
    base = pd.DataFrame({"employee_id": ["E-104", "E-105", "E-106"],
                         "salary": [48000, 51000, 52000]})
    changed = pd.DataFrame({"employee_id": ["E-104", "E-190", "E-105", "E-106"],
                            "salary": [48000, 45000, 51000, 54500]})
    old, new = tmp / "old.xlsx", tmp / "new.xlsx"
    base.to_excel(old, sheet_name="Headcount", index=False)
    changed.to_excel(new, sheet_name="Headcount", index=False)
    return old, new

def test_diff(tmp: Path = Path("out/fixture")) -> None:
    tmp.mkdir(parents=True, exist_ok=True)
    old, new = make_fixture(tmp)
    log = diff_workbooks(old, new)                        # from the fix
    kinds = log["kind"].value_counts().to_dict()
    assert kinds == {"row added": 1, "cell changed": 1}, kinds
    change = log[log["kind"] == "cell changed"].iloc[0]
    assert (change["key"], change["column"], change["old"], change["new"]) == ("E-106", "salary", 52000, 54500)
    print("diff fixture passed")

if __name__ == "__main__":
    test_diff()

The fixture inserts a row before the changed row on purpose — it is the case positional comparison gets wrong, and the one the key alignment exists to handle.

FAQ

Can this detect renamed sheets? Not directly; a rename shows as one sheet removed and one added. Compare the removed and added sheets' contents and treat a near-identical pair as a rename.

Do formatting changes show up? No — pandas reads values only. Comparing fills, fonts and number formats requires iterating cells with openpyxl and comparing cell.fill, cell.font and cell.number_format, which is rarely worth the noise.

How do I ignore columns that always change? Drop them before diffing: a.drop(columns=["last_updated"], errors="ignore"). Timestamps and "modified by" columns otherwise dominate the log.

Will this work on .xls files? pandas needs xlrd for .xls, and highlighting needs openpyxl, which cannot write .xls. Convert old files to .xlsx first, as covered in fix xlrd error reading xlsx files.

Part of Comparing and Reconciling Spreadsheets with Python.