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

Highlight Duplicate Values in Excel with openpyxl

Flag repeated values in a worksheet: the built-in duplicateValues rule, formula rules for duplicates across columns, whole-row highlighting, and a pandas alternative.

Duplicate rows are the quietest data problem: nothing errors, totals are simply wrong. Excel's conditional formatting can make them visible in the delivered workbook, so the person who owns the data sees the problem in the file rather than in a message about the file. openpyxl writes those rules directly. This guide covers the built-in duplicate rule, formula rules for the cases it cannot express, and when to skip conditional formatting and paint the cells yourself. It belongs to Applying Conditional Formatting with openpyxl.

A rule travels with the file; a fill is baked in A conditional formatting rule is stored as an instruction that Excel evaluates on open and re-evaluates after edits, while a static fill records the decision the script made at write time. conditional rule static fill Excel evaluates it on open updates when the reader edits invisible to a PDF export Python decided at write time fixed — an edit does not change it survives PDF and screenshots

Prerequisites

Bash
pip install openpyxl pandas

The built-in duplicate rule

openpyxl exposes Excel's own duplicate rule, which needs no formula:

Python
"""Highlight every value that appears more than once in a column."""
from openpyxl import load_workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.differential import DifferentialStyle

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

pink = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
red_text = Font(color="9C0006")
style = DifferentialStyle(fill=pink, font=red_text)

ws.conditional_formatting.add(
    f"A2:A{ws.max_row}",
    Rule(type="duplicateValues", dxf=style, stopIfTrue=False),
)
wb.save("orders.xlsx")

The colours are Excel's own "Light Red Fill with Dark Red Text", which readers recognise instantly — worth using rather than inventing a palette. Note the range is computed from ws.max_row, so it covers the data and nothing else; a rule over an entire column also flags the blank cells against each other in some versions.

Highlight only the repeats, not the first occurrence

The built-in rule marks every copy, including the first. Usually the first row is the one to keep and the later ones are the problem, which needs a COUNTIF over the rows above:

Python
from openpyxl.formatting.rule import FormulaRule

ws.conditional_formatting.add(
    f"A2:A{ws.max_row}",
    FormulaRule(formula=["COUNTIF($A$2:$A2,$A2)>1"], fill=pink, font=red_text),
)

The anchoring is the whole trick. $A$2 is fixed, $A2 grows as the rule is applied down the range, so each row counts only itself and the rows above it — a first occurrence scores 1 and stays unhighlighted, while every later copy scores 2 or more. Write the formula as it would appear in the top-left cell of the range, and Excel adjusts it for the rest.

Colour the whole row

A highlighted cell in column A is easy to miss on a wide sheet. Apply the same test across the row, with the column anchored:

Python
from openpyxl.formatting.rule import FormulaRule

last_col = ws.cell(row=1, column=ws.max_column).column_letter
ws.conditional_formatting.add(
    f"A2:{last_col}{ws.max_row}",
    FormulaRule(formula=[f"COUNTIF($A$2:$A${ws.max_row},$A2)>1"], fill=pink),
)

$A2 keeps every cell in the row testing column A, while the row number varies down the sheet. Getting the dollar signs wrong here is the single most common reason a rule "does nothing" — the formula ends up testing each cell against its own column.

What each rule highlights on the same data The built-in rule marks all three copies of a repeated reference, the COUNTIF rule marks only the second and third, and the row rule extends the mark across every column. Same sheet, three rules row value duplicateValues COUNTIF above whole row 2 00417 marked first — clean clean 3 00418 clean clean clean 4 00417 marked marked whole row Choose by what the reader is meant to do: review both copies, or delete the later one

Duplicates across a combination of columns

Business duplicates are rarely one column — the same customer, the same date and the same amount is a duplicate even when the reference differs. COUNTIFS handles it:

Python
from openpyxl.formatting.rule import FormulaRule

last = ws.max_row
ws.conditional_formatting.add(
    f"A2:F{last}",
    FormulaRule(
        formula=[f"COUNTIFS($B$2:$B${last},$B2,$C$2:$C${last},$C2,$D$2:$D${last},$D2)>1"],
        fill=pink,
    ),
)

Keep the number of criteria small: COUNTIFS over several columns on tens of thousands of rows is genuinely slow to recalculate, and Excel re-evaluates the whole rule on every edit.

Or find them in Python and paint the cells

When you already know which rows are duplicates — or the rule would be too slow, or the file will be exported to PDF where conditional formats do not render — compute in pandas and write a static fill:

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

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
dupes = df.duplicated(subset=["customer", "ordered", "total"], keep="first")
print(f"{dupes.sum()} duplicate row(s)")

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
pink = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")

for offset, is_dupe in enumerate(dupes, start=2):     # +2 for header and 1-based rows
    if is_dupe:
        for cell in ws[offset]:
            cell.fill = pink

wb.save("orders_flagged.xlsx")

keep="first" marks the later copies only, matching the COUNTIF behaviour above. This route also lets you report the count in a log or an email, which a conditional rule cannot do — the detection side is covered in Find duplicate rows in Excel with Python.

Add a summary the reader sees first

Highlighting helps someone already looking at the right sheet. A count at the top tells them whether to look at all:

Python
ws["H1"] = f"Duplicate rows: {int(dupes.sum())}"
ws["H1"].font = Font(bold=True, color="9C0006" if dupes.any() else "0B6157")

Pair it with a summary sheet when the workbook has several tabs, as in Add a summary sheet to an Excel report with Python.

Check the rule actually landed

A conditional format is written into the file rather than applied to it, so the only way to be sure is to read the rules back:

Python
from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

for rng in ws.conditional_formatting:
    for rule in rng.rules:
        print(rng.sqref, rule.type, getattr(rule, "formula", ""))
Text
A2:A501 duplicateValues []
A2:F501 expression ['COUNTIF($A$2:$A$501,$A2)>1']

Two things to look for. The range must cover the rows the data actually occupies — a rule ending at row 501 on a sheet with 900 rows silently ignores the rest. And a rule whose type is expression must carry a formula; an empty list means the rule was constructed without one and Excel will ignore it entirely.

Two ways a duplicate rule silently does nothing A rule whose range stops short of the data ignores later rows, and a formula rule anchored to the wrong cell tests each cell against its own column instead of the key column. Both look correct in the code range stops short A2:A501 on 900 rows rows 502+ never checked wrong anchor A2 instead of $A2 each column tests itself

Add the read-back to the report job's own checks and both failures become impossible to ship — the same pre-flight idea as Validate an Excel report before sending it.

Common pitfalls and gotchas

  • Wrong anchors. Write the formula for the top-left cell of the range; $A2 for a row test, $A$2 for a fixed corner.
  • A range that misses new rows. Compute it from ws.max_row at write time, or apply it to the table's range so it grows with the data.
  • Whitespace hiding duplicates. "00417 " and "00417" are different values to Excel; normalise text before writing.
  • Case sensitivity. COUNTIF is case-insensitive, so ACME and Acme count as the same; pandas' duplicated is case-sensitive. The two approaches can disagree.
  • PDF export. Conditional formats are applied by Excel at display time; a static fill is safer when the destination is a PDF.

Performance and scale notes

Conditional formatting rules cost nothing to write and are evaluated by Excel on the reader's machine, which is exactly where the cost lands: a COUNTIF rule over 100,000 rows makes the workbook sluggish to edit, because every keystroke re-evaluates it. Above a few thousand rows, prefer the pandas route and a static fill — the detection is a single vectorised pass and the resulting file is inert. If the sheet must stay interactive, restrict the rule's range to the columns that matter and consider sorting so duplicates are adjacent, which makes them visible without any formatting at all.

Conclusion

Excel's built-in duplicate rule is one call and marks every copy; a COUNTIF rule anchored to the rows above marks only the repeats, and extending it across the row makes them visible on a wide sheet. When the duplicate definition spans columns, COUNTIFS expresses it — but past a few thousand rows, find the duplicates in pandas and write a static fill instead, so the workbook stays fast and the marks survive a PDF export.

Frequently asked questions

What is the difference between the built-in rule and a COUNTIF rule? The built-in duplicateValues rule highlights every cell whose value appears more than once in the range, and Excel evaluates it. A COUNTIF formula rule can do more — highlight only the second and later copies, span several columns, or colour the whole row.

Does openpyxl detect the duplicates itself? No. It writes a rule into the file and Excel applies it when the workbook opens. If you need the duplicates in Python, find them with pandas and write a static fill instead.

How do I highlight the whole row, not just the cell? Apply a formula rule across the row range with the column reference anchored — for example =COUNTIF($A:$A,$A2)>1 applied to A2:F500. The dollar sign fixes the column so every cell in the row evaluates the same test.

Why is nothing highlighted when I open the file? The usual causes are a range that does not include the data, a formula written with the wrong anchor for the top-left cell of the range, or a rule added to a worksheet that was then replaced.

Can I highlight duplicates across two sheets? Not reliably with a plain COUNTIF against another sheet. It is more dependable to compute the comparison in pandas and write a static fill.