List and Remove Conditional Formatting with openpyxl

Conditional formatting accumulates. Every rerun of a report script adds its rules again, people copy and paste formatted ranges, and after a year a sheet can carry dozens of overlapping rules that slow Excel down and make the Manage Rules dialog unreadable. openpyxl can read every rule, but it has no method for deleting one — you rebuild the list without the rules you do not want. This guide, part of Applying Conditional Formatting with openpyxl, turns the rules into an inventory you can read and then cleans them up precisely.

Prerequisites

pip install openpyxl pandas

A sheet with the kind of clutter a rerun script leaves behind — the same rule three times — plus a formula rule, a colour scale and a data bar:

from openpyxl import Workbook, load_workbook
from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule, FormulaRule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Sales"
for r in range(1, 21):
    ws.append([r, r * 10, r * 5])
red = PatternFill("solid", fgColor="FCE9E9")
for _ in range(3):                                   # the same rule added by three runs
    ws.conditional_formatting.add("B2:B20", CellIsRule(operator="greaterThan", formula=["150"], fill=red))
ws.conditional_formatting.add("A2:B20", FormulaRule(formula=["$B2<50"], fill=red))
ws.conditional_formatting.add("B2:B20", ColorScaleRule(start_type="min", start_color="FFFFFF",
                                                       end_type="max", end_color="5B5CF0"))
ws.conditional_formatting.add("C2:C20", DataBarRule(start_type="min", end_type="max", color="0B6157"))
wb.save("report.xlsx")

Step 1 — List every rule as a table

ws.conditional_formatting iterates over range groups; each has an sqref and a list of rules. Flatten them into rows, across every sheet, and a DataFrame makes the inventory easy to sort and filter:

import pandas as pd

def rule_inventory(path):
    rows = []
    wb = load_workbook(path)
    for ws in wb.worksheets:
        for cf in ws.conditional_formatting:
            for rule in cf.rules:
                fill = rule.dxf.fill.fgColor.rgb if rule.dxf and rule.dxf.fill else None
                rows.append({"sheet": ws.title, "range": str(cf.sqref), "type": rule.type,
                             "priority": rule.priority, "operator": rule.operator,
                             "formula": "; ".join(rule.formula or []), "fill": fill})
    return pd.DataFrame(rows).sort_values(["sheet", "priority"])

inventory = rule_inventory("report.xlsx")
print(inventory[["range", "type", "priority", "formula"]].values.tolist())

Each rule shows its range, type — cellIs, expression, colorScale, dataBar, iconSet and so on — priority, formula and the fill colour of its format. Rules with a lower priority number are evaluated first. Writing the inventory to a sheet of its own is a useful audit before a clean-up, and a before-and-after pair of inventories documents what changed.

Step 2 — Find duplicates

Two rules are duplicates when they cover the same range with the same type, operator, formula and format. Build a key from those parts and count:

def rule_key(sqref, rule):
    fill = rule.dxf.fill.fgColor.rgb if rule.dxf and rule.dxf.fill else None
    return (str(sqref), rule.type, rule.operator, tuple(rule.formula or []), fill)

key_cols = ["range", "type", "operator", "formula", "fill"]
dupes = inventory[inventory.duplicated(key_cols, keep=False)]
print(len(dupes), "rows share a key")          # 3 rows share a key

Colour scales, data bars and icon sets carry their settings in sub-objects rather than a formula; for those, add the relevant parts — the scale's colours, the icon set name and thresholds — to the key if you need to tell similar rules apart.

Step 3 — Remove rules by rebuilding the list

ConditionalFormattingList has an add method but no remove. To delete rules, create a new list, add back only the rules you want to keep, and assign it to the sheet. A small function with a "keep" test covers every case:

def filter_rules(ws, keep):
    old = ws.conditional_formatting
    new = ConditionalFormattingList()
    for cf in old:
        for rule in cf.rules:
            if keep(cf.sqref, rule):
                new.add(str(cf.sqref), rule)
    ws.conditional_formatting = new

wb = load_workbook("report.xlsx")
ws = wb["Sales"]

seen = set()
def keep_first_copy(sqref, rule):
    key = rule_key(sqref, rule)
    if key in seen:
        return False
    seen.add(key)
    return True

filter_rules(ws, keep_first_copy)
filter_rules(ws, lambda sqref, rule: rule.type != "dataBar")       # drop data bars
wb.save("report_clean.xlsx")
print(rule_inventory("report_clean.xlsx")[["range", "type"]].values.tolist())
# [['B2:B20', 'cellIs'], ['A2:B20', 'expression'], ['B2:B20', 'colorScale']]

The kept rules keep their original priorities and formats — the dxf loaded with each rule is written back with it. Saving to a new file, as here, leaves the original untouched until the inventory confirms the result.

Step 4 — Remove the rules that touch a range

To clear formatting from a region — before rewriting a table, for example — drop every rule whose range overlaps it. openpyxl's range objects can test that for you:

from openpyxl.worksheet.cell_range import CellRange

def overlaps(sqref, target):
    t = CellRange(target)
    return any(not r.isdisjoint(t) for r in sqref.ranges)

wb = load_workbook("report.xlsx")
ws = wb["Sales"]
filter_rules(ws, lambda sqref, rule: not overlaps(sqref, "C1:C20"))
print(sorted({str(cf.sqref) for cf in ws.conditional_formatting}))      # ['A2:B20', 'B2:B20']

This removes the whole rule even if it also covers cells outside the target, which is usually what you want: a rule half-applied to a rewritten table is more confusing than none. To clear a sheet completely, skip the function and assign an empty ConditionalFormattingList().

Step 5 — Renumber priorities

After removals, priorities have gaps — 1, 4, 5 — which Excel accepts. If a script is about to add new rules and you want predictable ordering, renumber the kept ones by resetting their priority to 0 before re-adding; add treats 0 as unset and assigns 1, 2, 3 in order:

wb = load_workbook("report_clean.xlsx")
ws = wb["Sales"]
ordered = sorted(((cf.sqref, r) for cf in ws.conditional_formatting for r in cf.rules),
                 key=lambda pair: pair[1].priority)
ws.conditional_formatting = ConditionalFormattingList()
for sqref, rule in ordered:
    rule.priority = 0                   # 0 tells add() to assign the next number
    ws.conditional_formatting.add(str(sqref), rule)
print(sorted((r.priority, r.type) for cf in ws.conditional_formatting for r in cf.rules))
# [(1, 'cellIs'), (2, 'expression'), (3, 'colorScale')]

Sorting by the old priority first keeps the evaluation order the same, so nothing changes visually; only the numbers are tidied. How priorities and stopIfTrue interact is shown in Highlight Overdue Dates in Excel with openpyxl.

Step 6 — Stop the duplicates at the source

Cleaning a file once does not help if the script that created the clutter runs again tomorrow. Make scripts that edit existing workbooks idempotent: before adding its rules, a script removes any rule on the ranges it owns, then adds its current set. Running it once or fifty times leaves the same rules behind:

def set_rules(ws, target, rules):
    filter_rules(ws, lambda sqref, rule: not overlaps(sqref, target))
    for rule in rules:
        ws.conditional_formatting.add(target, rule)

wb = load_workbook("report_clean.xlsx")
ws = wb["Sales"]
for _ in range(3):                                   # three runs of the same script
    set_rules(ws, "B2:B20", [CellIsRule(operator="greaterThan", formula=["150"], fill=red)])
print(sum(len(cf.rules) for cf in ws.conditional_formatting))   # 1

After three runs there is exactly one rule, not three. Note what else went: the colour scale on B2:B20 and the formula rule on A2:B20 both touched the target, so both were removed on the first run. That is the trade-off of range ownership: a script clears everything touching its range, so give each script its own columns or sheet, and keep rules that span several owners in one place. Adding the inventory from Step 1 to the script's test run shows at once whether a change removed more than intended.

Common pitfalls

• Assuming the inventory is complete. Rules stored in Excel's extension format are invisible to openpyxl and are dropped when it saves. For workbooks built in recent Excel versions, check the cleaned file in Excel; see What openpyxl Loses When You Save an Existing Workbook.
• Deduplicating on the formula alone. Two rules with the same formula but different fills are different rules. Include the format in the key.
• Fixing the symptom only. If a script created the duplicates, clean the file and change the script to clear its own rules before adding them.

Conclusion

openpyxl exposes every classic conditional formatting rule through ws.conditional_formatting: iterate it into an inventory of ranges, types, priorities, formulas and fills. There is no delete method, so remove rules by building a new ConditionalFormattingList with the ones you keep — filtering by duplicate key, type or overlapping range — and assign it back. Renumber priorities in their old order if you need tidy numbers, save to a copy, and compare inventories before replacing the original.

Frequently asked questions

How do I list all conditional formatting rules with openpyxl? Iterate ws.conditional_formatting. Each item has an sqref (the range) and a rules list; each rule has type, priority, operator, formula and a dxf with its fill and font.

How do I delete a conditional formatting rule with openpyxl? openpyxl has no remove method, so build a new ConditionalFormattingList, add back only the rules you want to keep, and assign it to ws.conditional_formatting before saving.

How do I remove all conditional formatting from a sheet? Assign an empty ConditionalFormattingList to ws.conditional_formatting and save the workbook.

Why do I have the same rule many times? A script that opens an existing workbook and adds a rule adds another copy each time it runs. List the rules, drop exact duplicates, and change the script to clear its own rules before adding them.

Related

• Up one level: Applying Conditional Formatting with openpyxl — rules that follow the data.
• openpyxl: Apply Conditional Formatting to a Range — adding rules in the first place.
• Add Icon Sets to Excel Cells with openpyxl — icon rules and their thresholds.
• Highlight Duplicate Values in Excel with openpyxl — a rule type worth auditing.