Compare Two Excel Files for Differences with Python

"What changed since last month?" is a question a spreadsheet cannot answer about itself. Two files, hundreds of rows, and the differences that matter are usually a handful of cells. This guide, part of Validating Excel Data with Python, builds a diff that reports added rows, removed rows and changed cells — with the old and new values side by side.

Prerequisites

pip install pandas openpyxl

Two versions of the same report

import pandas as pd

pd.DataFrame([
    {"Order_ID": 2001, "Region": "North", "Quantity": 4, "Unit_Price": 19.99},
    {"Order_ID": 2002, "Region": "South", "Quantity": 2, "Unit_Price": 49.50},
    {"Order_ID": 2003, "Region": "West", "Quantity": 7, "Unit_Price": 12.25},
]).to_excel("orders_january.xlsx", index=False, sheet_name="Orders")

pd.DataFrame([
    {"Order_ID": 2002, "Region": "South", "Quantity": 3, "Unit_Price": 49.50},  # quantity changed
    {"Order_ID": 2003, "Region": "West", "Quantity": 7, "Unit_Price": 11.95},   # price changed
    {"Order_ID": 2004, "Region": "Central", "Quantity": 5, "Unit_Price": 8.75}, # added
]).to_excel("orders_february.xlsx", index=False, sheet_name="Orders")

Order 2001 has gone, 2004 is new, and two cells changed. A good diff reports exactly those four facts and nothing else.

Align on a key, never on position

Comparing row 5 with row 5 is only valid if nothing was inserted, removed or re-sorted — which is exactly what you are trying to detect. Index both frames on the business key:

import pandas as pd

KEY = "Order_ID"

def load(path, sheet="Orders", key=KEY):
    df = pd.read_excel(path, sheet_name=sheet)
    if df[key].duplicated().any():
        dupes = df.loc[df[key].duplicated(keep=False), key].unique()
        raise ValueError(f"{path}: key {key} is not unique — {list(dupes)[:5]}")
    return df.set_index(key).sort_index()

old = load("orders_january.xlsx")
new = load("orders_february.xlsx")
print(old.index.tolist(), new.index.tolist())

The uniqueness check is not optional. A duplicated key makes the comparison meaningless — pandas will align one row against several — and it is far better to stop here than to publish a diff nobody can reconcile. When no single column is unique, build a composite key first:

composite = ["Order_Date", "Customer", "SKU"]
df = df.assign(_key=df[composite].astype(str).agg("␟".join, axis=1)).set_index("_key")

Added and removed rows

With both frames indexed, set arithmetic answers two of the three questions:

import pandas as pd

added = new.loc[new.index.difference(old.index)]
removed = old.loc[old.index.difference(new.index)]
common = old.index.intersection(new.index)

print(f"{len(added)} added, {len(removed)} removed, {len(common)} in both")
print(added)

That is already a useful report on its own. For a monthly reconciliation, removed is often the interesting half: rows that quietly disappeared from a source system rarely announce themselves, and the total moving by exactly one order's value is much harder to spot than a missing key.

Changed cells, with old and new values

DataFrame.compare does the cell-level work, given two frames aligned on the same index and columns:

import pandas as pd

shared_columns = old.columns.intersection(new.columns)
left = old.loc[common, shared_columns]
right = new.loc[common, shared_columns]

# Round floats so invisible precision differences do not read as changes
left = left.round({c: 2 for c in left.select_dtypes("number").columns})
right = right.round({c: 2 for c in right.select_dtypes("number").columns})

diff = left.compare(right, result_names=("old", "new"))
print(diff)

changes = (
    diff.stack(0, future_stack=True)
        .rename_axis([KEY, "Column"])
        .reset_index()
        .dropna(subset=["old", "new"], how="all")
)
print(changes.to_string(index=False))

compare returns only the rows and columns that differ, with a two-level column index of old and new. Stacking that into a long frame gives one row per change — key, column, old value, new value — which is the shape a person can read and a spreadsheet can filter.

Rounding before comparing is the detail that decides whether the report is trusted. A price that went through a currency conversion may differ in the fifteenth decimal place; reporting that as a change teaches readers to ignore the diff.

Write a difference report

Four sheets, colour-coded, and the summary first:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

summary = pd.DataFrame([
    {"Measure": "Rows in old", "Value": len(old)},
    {"Measure": "Rows in new", "Value": len(new)},
    {"Measure": "Added", "Value": len(added)},
    {"Measure": "Removed", "Value": len(removed)},
    {"Measure": "Cells changed", "Value": len(changes)},
])

with pd.ExcelWriter("orders_diff.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    changes.to_excel(writer, sheet_name="Changed", index=False)
    added.reset_index().to_excel(writer, sheet_name="Added", index=False)
    removed.reset_index().to_excel(writer, sheet_name="Removed", index=False)

wb = load_workbook("orders_diff.xlsx")
for sheet, colour in (("Added", "C6EFCE"), ("Removed", "FFC7CE"), ("Changed", "FFEB9C")):
    ws = wb[sheet]
    fill = PatternFill("solid", start_color=colour)
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.fill = fill
wb.active = 0
wb.save("orders_diff.xlsx")
print("wrote orders_diff.xlsx")

Green for added, red for removed and amber for changed are the conventions readers already know from Excel's own cell styles, so the report needs no key. The summary sheet is what makes it skimmable: most months the honest answer is "three cells changed", and a reader should be able to see that without scrolling.

Comparing formulas instead of values

pandas sees values. When the question is whether someone edited the model — a changed SUM range, a hardcoded number replacing a formula — read both files with openpyxl instead:

from openpyxl import load_workbook

a = load_workbook("model_v1.xlsx")["Summary"]
b = load_workbook("model_v2.xlsx")["Summary"]

rows = max(a.max_row, b.max_row)
cols = max(a.max_column, b.max_column)

for r in range(1, rows + 1):
    for c in range(1, cols + 1):
        old_value, new_value = a.cell(r, c).value, b.cell(r, c).value
        if old_value != new_value:
            print(f"{a.cell(r, c).coordinate}: {old_value!r} -> {new_value!r}")

This is the check to run on a template before it goes into production, and it pairs well with freezing formulas into values for the copy you distribute — the model stays comparable, the distributed file cannot drift.

Decide what counts as a change

Not every difference is worth reporting, and a diff that reports everything gets ignored. Agree the tolerances once, per column type:

Identifiers are the one column type that should never be normalised for comparison. If A-100 becomes a-100 between two versions, that genuinely is a change — one that will break the next join — and quietly folding the case hides exactly the problem the diff exists to find.

Common pitfalls and gotchas

Keep the diff in the pipeline

Running the comparison by hand answers this month's question. Running it automatically answers the question nobody thought to ask — the one where a source system silently changed its history:

import sys

def diff_exit_code(added, removed, changes, tolerance=0.02):
    """0 = as expected, 1 = worth a look, 2 = the past changed."""
    if len(removed):
        return 2                                   # historical rows disappeared
    churn = (len(added) + len(changes)) / max(len(new), 1)
    return 1 if churn > tolerance else 0

code = diff_exit_code(added, removed, changes)
print("diff exit code:", code)
sys.exit(code)

Treating removals as the most serious outcome reflects how these files behave in practice: rows are appended constantly, edited occasionally, and deleted almost never. A month where three historical orders vanished is worth a phone call even if every other number matches, and a scheduler that reads exit codes turns that judgement into an alert without anyone opening the workbook.

Performance and scale notes

Indexing and set operations are hash-based and scale well; the cost is dominated by reading two workbooks. For large files, read only the columns you intend to compare with usecols, and consider caching the previous month's frame as Parquet so only the new workbook has to be parsed. DataFrame.compare materialises a frame containing every differing cell, which is small in a healthy diff — if it is large, that is itself the finding, and the summary sheet should say so rather than the report attempting to list a hundred thousand changes.

Conclusion

A workbook diff is three questions answered in order: which keys left, which arrived, and which shared rows changed. Align on a business key rather than on row position, verify the key is unique before trusting anything, round numerics to the precision you care about, and reshape the comparison into one row per change. Colour the output the way Excel does, put the counts on a summary sheet, and the monthly "what changed?" conversation takes a minute.

Frequently asked questions

Do the two files need the same row order? No, and they should not be compared by position. Align both frames on a business key so a re-sorted export does not read as every row having changed.

What if there is no unique key? Build a composite key from the columns that identify a record — date, customer and product, say — and verify it is unique in both files before comparing.

Why do identical-looking numbers show as changed? Floating-point values that differ far below the visible precision still compare unequal. Round to the precision you actually care about before comparing.

Can I compare formulas rather than values? Yes — read with openpyxl instead of pandas and compare cell.value on both workbooks, which gives you the formula text rather than the cached result.

Related

Up to the parent guide:

• Validating Excel Data with Python — validation across versions as well as within one file.

Related guides:

• Find Duplicate Rows in Excel with Python — the key-uniqueness check this comparison depends on.
• Merge Two Excel Files on a Common Column with Python — joining files rather than diffing them.
• Highlight Invalid Cells in Excel with Python — the same colour conventions applied to validation.
• Combine Multiple Excel Files into One with Python — when the versions should be stacked instead.