Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Find Rows in One Excel File Missing from Another

Reconcile two spreadsheets in Python — an indicator merge for both directions, composite keys, near-matches from whitespace, and a formatted exceptions workbook.

Two files that should agree, and a number that does not. The question is always the same: which rows are in one and not the other, and which are in both but different? pandas answers it with a single merge — as long as the keys match, which on real spreadsheet data they frequently do not. This guide covers the reconciliation itself, the invisible key mismatches that make it lie, and the exceptions workbook that turns the result into something a colleague can act on. It extends Merging and Joining Excel DataFrames.

One outer merge answers both directions at once Two source files overlap partially. An outer merge with the indicator flag labels every resulting row as left_only, both, or right_only. Left_only rows exist in the first file and are missing from the second. Right_only rows are the reverse. Rows marked both are present in each and can be compared value by value to find changes. A left join would answer only one of the three questions. file A system export 1,482 rows file B 1,470 rows outer merge indicator=True left_only — missing from B 18 rows both — compare the values 1,464 rows · some may differ right_only — missing from A 6 rows

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

Two files that nearly agree:

Python
import pandas as pd

pd.DataFrame({
    "order_id": ["A-1001", "A-1002", "A-1003", "A-1004"],
    "region": ["North", "South", "West", "North"],
    "revenue": [5150.00, 4268.50, 3511.25, 2980.10],
}).to_excel("system.xlsx", index=False)

pd.DataFrame({
    "order_id": ["A-1001", "A-1002 ", "A-1004", "A-1005"],
    "region": ["North", "South", "North", "East"],
    "revenue": [5150.00, 4268.50, 2980.10, 1820.00],
}).to_excel("ledger.xlsx", index=False)

A-1003 is genuinely missing from the ledger, A-1005 is genuinely extra — and A-1002 has a trailing space that will make it look missing from both directions unless the keys are normalised.

Step 1 — Normalise the keys first

Skip this and the reconciliation reports differences that are not real:

Python
import re
import pandas as pd

def normalise_key(series):
    """A comparison key that survives whitespace, case and formatting drift."""
    text = series.astype("string")
    text = text.str.replace(" ", " ", regex=False)     # non-breaking space
    text = text.str.replace(r"\s+", " ", regex=True).str.strip()
    return text.str.casefold()

system = pd.read_excel("system.xlsx")
ledger = pd.read_excel("ledger.xlsx")

system["key"] = normalise_key(system["order_id"])
ledger["key"] = normalise_key(ledger["order_id"])

Numeric keys read as text on one side and numbers on the other are the other frequent culprit — 1001 and 1001.0 will not match:

Python
def normalise_numeric_key(series):
    """Canonicalise an identifier that may arrive as text or as a number."""
    numbers = pd.to_numeric(series, errors="coerce")
    as_text = series.astype("string").str.strip()
    # Where it parsed as a whole number, use the integer form.
    return numbers.map(
        lambda v: str(int(v)) if pd.notna(v) and float(v).is_integer() else None
    ).fillna(as_text)

The wider treatment of invisible text differences is in stripping whitespace and normalising text columns.

Step 2 — Check the keys are unique

What a duplicated key does to a merge The key A-1002 appears twice in the left file and three times in the right. A merge pairs every left occurrence with every right occurrence, producing six rows from five. The counts that follow are therefore meaningless, and worse, any revenue total computed from the merged frame is inflated. Checking for duplicated keys before merging turns this into an immediate error rather than a wrong number. left: 2 rows A-1002 · 100 A-1002 · 200 right: 3 rows A-1002 × 3 merge on key every pair matched 6 rows out of 5 2 × 3 = 6 combinations every count is now wrong and revenue totals are inflated

A merge on a duplicated key multiplies rows, and the resulting counts are meaningless:

Python
import pandas as pd

def check_unique(df, key, label):
    duplicated = df[key].duplicated(keep=False)
    if duplicated.any():
        counts = df.loc[duplicated, key].value_counts()
        raise ValueError(
            f"{label}: {int(duplicated.sum())} rows share a key. "
            f"Worst offenders:\n{counts.head().to_string()}"
        )
    return True

check_unique(system, "key", "system.xlsx")
check_unique(ledger, "key", "ledger.xlsx")

Raising is usually right — a duplicated key in a file that should have unique ones is itself a finding. Where duplicates are legitimate, aggregate before comparing, or extend the key with the column that distinguishes them.

Step 3 — The reconciliation

One outer merge answers both directions:

Python
import pandas as pd

merged = system.merge(
    ledger, on="key", how="outer", suffixes=("_system", "_ledger"),
    indicator=True,
)

only_system = merged.loc[merged["_merge"] == "left_only"]
only_ledger = merged.loc[merged["_merge"] == "right_only"]
in_both = merged.loc[merged["_merge"] == "both"]

print(f"only in system: {len(only_system)}")
print(f"only in ledger: {len(only_ledger)}")
print(f"in both:        {len(in_both)}")

Two mistakes to avoid. Using how="left" answers only one direction — the rows added on the other side stay invisible, which is exactly the case where a total is too high rather than too low. And comparing raw sets of order IDs rather than merging loses every other column, so you know that a row is missing but not what it contained.

Step 4 — Find the rows that differ

The both group is where the subtler problems live: matching keys, different values.

Added, removed, and changed — three findings, not two The indicator merge gives added and removed directly. The third and often most important group is rows whose key matched but whose values differ, which requires comparing each value column pairwise after the merge. A reconciliation that reports only added and removed will show two files as agreeing when every shared row has a different amount. added in B, not in A _merge == "right_only" new records, or records A has not yet received removed in A, not in B _merge == "left_only" deleted, or dropped by a filter somewhere changed key matches, values do not needs a pairwise compare the group most often missed and the one that moves totals
Python
import numpy as np
import pandas as pd

def find_changes(merged, columns, tolerance=0.005):
    """Rows present in both files whose compared values differ."""
    both = merged.loc[merged["_merge"] == "both"].copy()
    differs = pd.Series(False, index=both.index)
    details = {}

    for name in columns:
        left, right = f"{name}_system", f"{name}_ledger"
        if left not in both or right not in both:
            continue

        if pd.api.types.is_numeric_dtype(both[left]):
            gap = (both[left] - both[right]).abs()
            changed = gap > tolerance
            details[f"{name}_delta"] = np.where(changed, both[left] - both[right],
                                                np.nan)
        else:
            changed = both[left].astype("string").fillna("") != \
                      both[right].astype("string").fillna("")

        differs |= changed
        details[f"{name}_changed"] = changed

    result = both.loc[differs].assign(
        **{k: pd.Series(v, index=both.index)[differs] for k, v in details.items()}
    )
    return result

changes = find_changes(merged, ["region", "revenue"])
print(f"{len(changes)} row(s) differ")

The numeric tolerance is not optional. Floating-point values that round-tripped through Excel differ in the fifteenth decimal place, and an exact comparison reports every single row as changed — which is worse than reporting none, because it buries the real differences.

Step 5 — Write an exceptions workbook

Three sheets and a summary give a colleague everything needed to act:

Python
import pandas as pd

def reconciliation_report(only_left, only_right, changes, dest,
                          left_name="system", right_name="ledger"):
    """Write a three-way reconciliation as a formatted workbook."""
    summary = pd.DataFrame({
        "finding": [f"only in {left_name}", f"only in {right_name}", "changed"],
        "rows": [len(only_left), len(only_right), len(changes)],
    })

    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False)
        only_left.to_excel(writer, sheet_name=f"Only in {left_name}"[:31],
                           index=False)
        only_right.to_excel(writer, sheet_name=f"Only in {right_name}"[:31],
                            index=False)
        changes.to_excel(writer, sheet_name="Changed", index=False)

        book = writer.book
        header = book.add_format({"bold": True, "bg_color": "#EEF2FF",
                                  "border": 1})
        for name, frame in [("Summary", summary),
                            (f"Only in {left_name}"[:31], only_left),
                            (f"Only in {right_name}"[:31], only_right),
                            ("Changed", changes)]:
            sheet = writer.sheets[name]
            for position, column in enumerate(frame.columns):
                sheet.write(0, position, str(column), header)
                sheet.set_column(position, position, 18)
            sheet.freeze_panes(1, 0)
            if len(frame):
                sheet.autofilter(0, 0, len(frame), len(frame.columns) - 1)

    return dest

reconciliation_report(only_system, only_ledger, changes, "reconciliation.xlsx")

Leading with a summary sheet matters: a reader wants the three counts before the detail, and a report that opens on eighteen rows of exceptions with no context invites the question "out of how many?". The multi-sheet mechanics are covered in adding a summary sheet to an Excel report.

Common pitfalls and fixes

SymptomCauseFix
Rows report missing from both sidesKey differs invisiblyNormalise both keys before merging.
Row count explodes after the mergeDuplicated key on one sideCheck uniqueness; aggregate or extend the key.
Only one direction reportedhow="left"Use how="outer" with indicator=True.
Every shared row reports as changedExact float comparisonCompare numerics with a tolerance.
1001 does not match 1001.0Number stored as text on one sideCanonicalise numeric keys.
Missing rows found but not their contentsCompared sets of IDs, not framesMerge the frames, not the key columns.
Report is unusableRaw dump with no summaryLead with counts, then the detail sheets.

Performance and scale notes

merge builds a hash index over the keys, so it is roughly linear and handles a few million rows comfortably. The costs sit around it.

Read only what you compare. A reconciliation on four columns has no reason to load forty:

Python
COMPARE = ["order_id", "region", "revenue"]
system = pd.read_excel("system.xlsx", usecols=COMPARE)
ledger = pd.read_excel("ledger.xlsx", usecols=COMPARE)

Normalise the distinct key values, not every row. Where the key is low-cardinality — a product or account code — cleaning the unique values and mapping is far cheaper than cleaning a million strings.

Use validate to fail fast. pandas will check the join cardinality for you, which is cheaper and clearer than discovering the row explosion afterwards:

Python
merged = system.merge(
    ledger, on="key", how="outer", indicator=True,
    suffixes=("_system", "_ledger"),
    validate="one_to_one",       # raises immediately if either side duplicates
)

For genuinely large files, compare hashes rather than values. Hashing each row to a single digest turns a wide comparison into a one-column one, which both reads and merges faster:

Python
import hashlib
import pandas as pd

def row_digest(df, columns):
    joined = df[columns].astype("string").fillna("").agg("|".join, axis=1)
    return joined.map(lambda s: hashlib.md5(s.encode()).hexdigest())

system["digest"] = row_digest(system, ["region", "revenue"])
ledger["digest"] = row_digest(ledger, ["region", "revenue"])
changed_keys = system.merge(ledger, on="key")
changed_keys = changed_keys.loc[
    changed_keys["digest_x"] != changed_keys["digest_y"], "key"
]

That identifies which rows changed cheaply; fetch the details only for those. Where either file is too large to hold at all, the chunked reading approach in reading large Excel files in chunks lets you build a key-to-digest mapping from one side and stream the other against it.

Conclusion

Reconciling two spreadsheets is one outer merge with indicator=True — surrounded by the work that makes its answer true. Normalise both keys so whitespace and formatting cannot manufacture differences, confirm the keys are unique before merging so the counts mean something, and compare numeric values with a tolerance so floating-point noise does not flag every row. Report all three findings, not two: added, removed, and the matched rows whose values changed. Then lead the workbook with a summary, because the counts are what a reader needs before the detail.

Frequently asked questions

What is the quickest way to find rows in A that are not in B? Merge them on the key with how="left" and indicator=True, then keep the rows whose merge indicator is left_only. It is one pass and it reports both directions when you use how="outer" instead.

Why do rows show as missing when I can see them in both files? The keys differ invisibly — trailing whitespace, a non-breaking space, different case, or a number stored as text on one side. Normalise both keys into a comparison column before merging.

How do I compare on more than one column? Pass a list to the on argument, or build a single composite key by joining the normalised parts with a separator that cannot appear in the values, such as a vertical bar.

What if the key is not unique? A merge on a duplicated key multiplies rows. Check for duplicates first and decide deliberately — aggregate them, keep the latest, or treat the duplication itself as the finding.

Can I compare the values as well as the keys? Yes. Merge on the key with suffixes, then compare the value columns pairwise to classify each matched row as identical or changed. That turns a two-way difference into a three-way one: added, removed and changed.