Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Highlight Differences Between Two Columns with openpyxl

Mark every row where system A and system B disagree, or where this month's value differs from last month's sheet. Write the comparison as a conditional format with openpyxl, and decide deliberately how case, blanks versus zero, trailing spaces and rounding differences should count.
Conditional formattingJoins, lookups & comparisonsopenpyxl

Reconciliations start with the question "where do these two columns disagree?" — the export from system A against system B, the forecast against the actual, this month's price list against last month's. A conditional format answers it inside the workbook, for every row, and keeps answering as values are corrected. The formula is one comparison, but what counts as "different" is a decision with at least four parts: letter case, blanks versus zeros, stray spaces and rounding noise. A plain <> makes those decisions for you, and not always the way a reconciliation needs. This page, part of Applying Conditional Formatting With openpyxl, shows each choice; every result below comes from rendering the workbook with LibreOffice.

Rows where system A and system B disagree A comparison sheet with seven items. The plain <> rule highlights rows b (100 against 101), f (50.004 against 50.001) and g ("x " against "x"). It does not highlight c, where a blank is compared with 0, or d, where North is compared with north, because the comparison ignores case and treats blank as zero. Rule =$B2<>$C2 on A2:C8 A B C 1 item system_a system_b 2 a 100 100 3 b 100 101 4 c (blank) 0 5 d North north 6 e 0.3 0.3 7 f 50.004 50.001 8 g "x " "x"

Prerequisites

Bash
pip install openpyxl pandas

compare.xlsx holds seven items chosen to exercise every edge case: an exact match, a real difference, blank against zero, a difference in case only, two equal decimals, a difference in the third decimal place, and a trailing space. A second sheet holds last month's system A values.

Python
from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

RED = PatternFill("solid", bgColor="FFC7CE")

def add_rule(path, out, formula, cell_range="A2:C8", sheet="Compare"):
    wb = load_workbook(path)
    wb[sheet].conditional_formatting.add(cell_range, FormulaRule(formula=[formula], fill=RED))
    wb.save(out)

Step 1 — Highlight rows where the columns differ

Apply the rule to the whole row range, anchoring the two compared columns with $ so every cell in a row tests the same pair:

Python
add_rule("compare.xlsx", "diff_basic.xlsx", "$B2<>$C2")

Rows b, f and g are highlighted. Row c, a blank against a zero, is not; nor is row d, "North" against "north". Both are consequences of how spreadsheet comparisons work, not of openpyxl: = and <> ignore letter case, and an empty cell compares equal to 0 and to "". For many reports that is exactly right. For a reconciliation, it can hide real discrepancies.

Step 2 — Make case and blanks count

EXACT compares text character for character, case included. As a side effect it converts both values to text first, so a blank ("") and a zero ("0") now differ too:

Python
add_rule("compare.xlsx", "diff_exact.xlsx", "NOT(EXACT($B2,$C2))")

Rows b, c, d, f and g are highlighted. If you want case to count but blank and zero to stay equal — or the reverse — say so explicitly. This version keeps the case-insensitive comparison but adds a check that both sides are either blank or filled:

Python
add_rule("compare.xlsx", "diff_blanks.xlsx", "OR($B2<>$C2,ISBLANK($B2)<>ISBLANK($C2))")

Now row c is flagged — a missing value is not the same as a reported zero — while row d, which differs only in case, is not.

Which rows each comparison flags A grid of the seven test rows against four rules. The plain <> rule flags b, f and g. NOT EXACT flags b, c, d, f and g. The tolerance rule flags b and g but not f, whose difference is 0.003. The TRIM rule flags b, c and f, treating "x " and "x" as equal. Row a and row e are never flagged. <> NOT(EXACT()) tolerance 0.005 TRIM both sides a 100 / 100 b 100 / 101 c blank / 0 d North / north e 0.3 / 0.3 f 50.004 / 50.001 g "x " / "x"

Step 3 — Ignore rounding noise in numbers

Two systems that store amounts at different precision disagree in the third decimal place without disagreeing about anything that matters. Compare numbers with a tolerance, and fall back to a plain comparison for text so the same rule works on mixed columns:

Python
add_rule("compare.xlsx", "diff_tolerance.xlsx",
         "IF(AND(ISNUMBER($B2),ISNUMBER($C2)),ABS($B2-$C2)>0.005,$B2<>$C2)")

Row f — 50.004 against 50.001 — is no longer flagged; row b, a whole unit apart, still is. Choose the tolerance from the business rule: half a cent for currency, a rounding unit for quantities. Keep it in a labelled cell ($H$1) rather than in the formula if people will want to change it. Fix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel Output explains why exact comparison of computed decimals is fragile in the first place.

Step 4 — Ignore stray spaces in text

Trailing spaces are the most common false difference in text exports. TRIM both sides before comparing:

Python
add_rule("compare.xlsx", "diff_trim.xlsx", "TRIM($B2)<>TRIM($C2)")

Row g is no longer flagged. Note that TRIM also turns numbers into text, so this rule treats blank and zero as different, like EXACT. For a column that holds only text, that is irrelevant; for mixed columns, combine the tests in one formula — IF(AND(ISNUMBER(...)), tolerance, TRIM comparison) — so each type gets the comparison it needs.

Show which side is higher

For budget against actual, or forecast against result, which way the difference goes matters as much as whether there is one. Two rules on the same range do it — one for each direction — and because each tests a different condition, their priority never conflicts:

Python
wb = load_workbook("compare.xlsx")
ws = wb["Compare"]
num = "AND(ISNUMBER($B2),ISNUMBER($C2))"
ws.conditional_formatting.add("A2:C8", FormulaRule(formula=[f"AND({num},$C2-$B2>0.005)"],
                                                   fill=PatternFill("solid", bgColor="C6EFCE")))
ws.conditional_formatting.add("A2:C8", FormulaRule(formula=[f"AND({num},$B2-$C2>0.005)"], fill=RED))
wb.save("diff_direction.xlsx")

Row b, where system B is higher, turns green; a row where system A is higher would turn red; rows within the tolerance, and text rows, stay plain. Swap the colours to match what "good" means in your report — for costs, higher is the red one.

Step 5 — Compare with another sheet

The same technique compares a column with the same column on another sheet — this month against last month. Quote the sheet name, because it contains a space:

Python
add_rule("compare.xlsx", "diff_vs_last_month.xlsx", "$B2<>'Last month'!$B2", cell_range="B2:B8")

Only B3 (100 against last month's 99) and B7 (50.004 against 50) are highlighted. The comparison relies on both sheets listing items in the same order; if they might not, look the previous value up by key instead — $B2<>INDEX('Last month'!$B:$B,MATCH($A2,'Last month'!$A:$A,0)) — so a reordered sheet does not produce a wall of false differences. Current Excel versions accept references to other sheets in conditional formatting formulas; versions older than Excel 2010 did not, and needed a defined name as a go-between.

Comparing by position versus by key Two panels. Comparing row by row with $B2 against the other sheet's $B2 is simple and fast but produces false differences when rows are sorted or inserted differently. Comparing by key with INDEX and MATCH on the item column survives reordering and inserted rows, at the cost of a longer formula and a lookup per cell. By position: $B2 <> 'Last month'!$B2 short formula fast breaks if rows are reordered inserted rows shift everything By key with INDEX / MATCH survives sorting survives inserted rows longer formula a lookup per cell

Step 6 — List the same differences in pandas

Highlighting helps the person fixing the data; the job that produced the workbook should also know how many rows disagree. The same rules translate directly into pandas, with the same decisions made explicit:

Python
import numpy as np
import pandas as pd

df = pd.read_excel("compare.xlsx", sheet_name="Compare")
a, b = df["system_a"], df["system_b"]
num = pd.to_numeric(a, errors="coerce").notna() & pd.to_numeric(b, errors="coerce").notna()
diff_num = (pd.to_numeric(a, errors="coerce") - pd.to_numeric(b, errors="coerce")).abs() > 0.005
diff_txt = (a.astype("string").str.strip().str.casefold().fillna("")
            != b.astype("string").str.strip().str.casefold().fillna(""))
df["mismatch"] = np.where(num, diff_num, diff_txt)
print(df.loc[df["mismatch"], "item"].tolist())

This version uses the tolerance for numbers and trimmed, case-folded text for everything else, so it flags b and c — the real difference and the blank-versus-zero — and nothing else. The casefold() is not optional: pandas compares strings case-sensitively, unlike the spreadsheet, and without it row d would be reported by the job while the workbook shows it as matching. Write the count to the job log and fail the run above a threshold; Compare Two Excel Files for Differences with Python builds a full cell-by-cell diff for whole workbooks.

Common pitfalls

  • Unanchored columns. B2<>C2 without $ compares the wrong pair in the second and third columns of a row rule.
  • Assuming <> is strict. It ignores case and treats blank as zero.
  • Exact comparison of computed decimals. Use a tolerance.
  • Comparing sorted and unsorted sheets by position. Look values up by key.
  • Different rules in the workbook and in the job. If the conditional format ignores case and the pandas check does not, the job reports mismatches that readers cannot see. Keep the two definitions identical.

Performance notes

Positional comparisons are trivial for the spreadsheet to evaluate. Key-based rules with MATCH over whole columns are evaluated per cell and can slow large sheets noticeably; on tens of thousands of rows, compute the mismatch flag in pandas, write it as a column, and highlight on the flag instead.

Conclusion

A rule such as $B2<>$C2 on the row range highlights disagreements, but it ignores case and treats blanks as zero. Use EXACT when case matters, add ISBLANK tests when missing and zero must differ, compare numbers with a tolerance, TRIM text, and compare with another sheet by position or — safer — by key. Mirror the chosen rule in pandas so the job can count the mismatches it is highlighting.

Frequently asked questions

Is the <> comparison case-sensitive? No. In a spreadsheet formula, "North"<>"north" is FALSE, so a plain <> rule treats them as equal. Use NOT(EXACT($B2,$C2)) when case matters.

Why is a blank cell not flagged against a zero? Spreadsheet comparisons treat an empty cell as equal to 0 and to an empty string. If blank and zero must count as different — a missing value versus a real zero — test ISBLANK on both sides as well.

How do I ignore tiny rounding differences? Compare numbers with a tolerance: ABS($B2-$C2)>0.005 flags only differences larger than half a cent. Wrap it in an IF with ISNUMBER so text columns still use a plain comparison.

Can a conditional format compare with another sheet? Yes. Current versions of Excel accept references to other sheets in conditional formatting formulas, such as $B2<>'Last month'!$B2. Very old versions (before 2010) required a defined name instead.

More on conditional formatting

More on conditional formatting →