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

Control Conditional Formatting Rule Order and Stop If True

When several conditional-formatting rules match the same cell, their priority decides what the reader sees — and Excel and LibreOffice resolve overlaps differently. See how openpyxl numbers rules, set priority explicitly, use stopIfTrue so the first matching rule wins everywhere, design self-contained rules, and check the order in a saved file and a rendered page.
Conditional formattingopenpyxl

A margin column has three rules: blue bold text for anything positive, a red fill for anything above 100, an amber fill for anything above 50. A value of 150 matches all three. What the reader sees depends on the rules' priority — and, surprisingly, on which application opens the file: Excel layers the formats of every matching rule as long as they do not conflict, while LibreOffice applies only the first matching rule. A report that looks right in one can look wrong in the other. openpyxl numbers rules in the order you add them, lets you set priorities explicitly, and supports Excel's Stop If True flag, which together make the result predictable everywhere. This page belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

Bash
pip install openpyxl pillow

LibreOffice (soffice) and pdftoppm are used in Step 5 to render the sheet and check which rule won; they are optional.

Step 1 — openpyxl numbers rules in the order you add them

Python
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.formatting.rule import CellIsRule

def fill(hex_colour):
    return PatternFill("solid", start_color=hex_colour, end_color=hex_colour)

wb = Workbook()
ws = wb.active
ws.title = "Margins"
for i, v in enumerate([150, 75, 20, -10], start=1):
    ws.cell(i, 1, v)
    ws.row_dimensions[i].height = 28
ws.column_dimensions["A"].width = 18

positive = CellIsRule(operator="greaterThan", formula=["0"], font=Font(bold=True, color="1D4ED8"))
high = CellIsRule(operator="greaterThan", formula=["100"], fill=fill("FCA5A5"))
medium = CellIsRule(operator="greaterThan", formula=["50"], fill=fill("FCD34D"))
for rule in (positive, high, medium):
    ws.conditional_formatting.add("A1:A4", rule)
for cf in ws.conditional_formatting:
    for r in cf.rules:
        print(cf.sqref, r.operator, r.formula, "priority", r.priority, "stopIfTrue", r.stopIfTrue)

Each add call gives the rule the next priority number unless it already has one: here 1 for "positive", 2 for "above 100", 3 for "above 50". Priority 1 is evaluated first. The numbers are unique across the whole sheet, not per range, which matters when rules on different ranges overlap. In this order, a value of 150 matches all three rules, starting with the blue-bold rule.

Step 2 — How Excel and LibreOffice resolve overlaps

The order produces different results in the two applications:

  • Excel evaluates every rule in priority order and applies all matching formats that do not conflict. For 150: blue bold text from rule 1, red fill from rule 2; rule 3's amber fill conflicts with the red fill already chosen, so the higher-priority red wins. The cell shows blue bold on red.
  • LibreOffice applies only the first rule that matches. For 150: rule 1 matches, so the cell is blue bold with no fill at all — the red and amber rules are never applied.

Same file, two different pictures: a reviewer in LibreOffice sees no warning colour on the highest values. The general cure is to make the outcome independent of the merging behaviour, which the next two steps do.

The same three rules on the value 150 In Excel, every matching rule contributes non-conflicting formats, so 150 is blue bold on a red fill, with the higher-priority red fill beating the amber one. In LibreOffice only the first matching rule applies, so 150 is blue bold with no fill. Excel rule 1: blue bold text rule 2: red fill rule 3: amber fill loses to red result: blue bold on red LibreOffice rule 1: blue bold text rule 2 never applied rule 3 never applied result: blue bold, no fill

Step 3 — Put the most specific rule first and make it complete

Order rules from most specific to most general, and give each rule every format it needs instead of relying on another rule to add the rest:

Python
wb = Workbook()
ws = wb.active
ws.title = "Margins"
for i, v in enumerate([150, 75, 20, -10], start=1):
    ws.cell(i, 1, v)
    ws.row_dimensions[i].height = 28
ws.column_dimensions["A"].width = 18

bold_blue = Font(bold=True, color="1D4ED8")
rules = [
    CellIsRule(operator="greaterThan", formula=["100"], fill=fill("FCA5A5"), font=bold_blue, stopIfTrue=True),
    CellIsRule(operator="greaterThan", formula=["50"], fill=fill("FCD34D"), font=bold_blue, stopIfTrue=True),
    CellIsRule(operator="greaterThan", formula=["0"], font=bold_blue, stopIfTrue=True),
]
for priority, rule in enumerate(rules, start=1):
    rule.priority = priority
    ws.conditional_formatting.add("A1:A4", rule)
wb.save("margins_ordered.xlsx")
print([(r.formula[0], r.priority, r.stopIfTrue) for cf in ws.conditional_formatting for r in cf.rules])

Now the "above 100" rule comes first and carries both the red fill and the blue bold font, so it no longer needs to be combined with anything. stopIfTrue=True tells Excel not to evaluate further rules once this one matches — the behaviour LibreOffice has anyway — so both applications show the same thing: 150 red, 75 amber, 20 blue bold, −10 unformatted. Setting rule.priority before add keeps the numbers you chose, because openpyxl only assigns one when the rule has none.

How a cell is formatted with ordered, stop-if-true rules The value is tested against the rule with priority 1, above 100. If it matches, red fill and blue bold apply and evaluation stops. Otherwise it is tested against priority 2, above 50, which applies amber and stops, then priority 3, above 0, which applies blue bold. A value matching none stays unformatted. Test rules in priority order first match wins (stopIfTrue) > 100 Priority 1 red fill, blue bold — stop > 50 Priority 2 amber fill, blue bold — stop > 0 Priority 3 blue bold — stop no match Unformatted e.g. −10

Step 4 — Inspect and reorder rules in an existing workbook

Templates often arrive with rules in the wrong order. Load them, list them with their priorities, and renumber:

Python
from openpyxl import load_workbook

wb = load_workbook("margins_ordered.xlsx")
ws = wb["Margins"]
all_rules = [(str(cf.sqref), r) for cf in ws.conditional_formatting for r in cf.rules]
for rng, r in sorted(all_rules, key=lambda t: t[1].priority):
    print(rng, r.priority, r.operator, r.formula, "stop" if r.stopIfTrue else "")

order = ["0", "50", "100"]                       # e.g. a template that wants the general rule first
for rng, r in all_rules:
    r.priority = order.index(r.formula[0]) + 1
wb.save("margins_reordered.xlsx")
print([(r.formula[0], r.priority) for _, r in sorted(all_rules, key=lambda t: t[1].priority)])

Priorities are plain attributes on the loaded rules, so renumbering them and saving changes the evaluation order. Keep the numbers unique across the sheet; two rules with the same priority are evaluated in an application-defined order. Listing and removing rules is covered in List and Remove Conditional Formatting with openpyxl.

Step 5 — Check what the reader sees

Rule order is easy to get subtly wrong, and the file alone does not show the outcome. Render the sheet and check whether the warning colours appear:

Python
import subprocess
from PIL import Image

def colours_on_page(xlsx):
    pdf = xlsx.replace(".xlsx", ".pdf")
    subprocess.run(["soffice", "--headless", "--convert-to", "pdf", xlsx], check=True, capture_output=True)
    subprocess.run(["pdftoppm", "-png", "-r", "60", "-f", "1", "-l", "1", pdf, pdf[:-4]], check=True)
    img = Image.open(pdf[:-4] + "-1.png").convert("RGB")
    found = {colour for _, colour in img.getcolors(maxcolors=1_000_000)}
    return {"red fill": (0xFC, 0xA5, 0xA5) in found, "amber fill": (0xFC, 0xD3, 0x4D) in found}

first_unordered = Workbook()
ws0 = first_unordered.active
for i, v in enumerate([150, 75, 20, -10], start=1):
    ws0.cell(i, 1, v)
for rule in (CellIsRule(operator="greaterThan", formula=["0"], font=Font(bold=True, color="1D4ED8")),
             CellIsRule(operator="greaterThan", formula=["100"], fill=fill("FCA5A5")),
             CellIsRule(operator="greaterThan", formula=["50"], fill=fill("FCD34D"))):
    ws0.conditional_formatting.add("A1:A4", rule)
first_unordered.save("margins_general_first.xlsx")

print("general rule first:", colours_on_page("margins_general_first.xlsx"))
print("specific rules first:", colours_on_page("margins_ordered.xlsx"))

In LibreOffice's rendering, the file with the general rule first shows neither warning colour — every positive value stopped at the blue-bold rule — while the ordered file shows both. A test like this, which checks that the colours that matter actually appear, catches a template edit that reorders the rules before anyone notices on a printed report.

The ordered rules in the rendered sheet The margins column after applying the ordered rules. 150 has a red fill and blue bold text, 75 an amber fill and blue bold text, 20 blue bold text only, and -10 no formatting. A B 1 Margin Rule applied 2 150 priority 1: red, blue bold 3 75 priority 2: amber, blue bold 4 20 priority 3: blue bold 5 -10 none

Performance notes

Rule order also affects how fast a sheet feels. Each rule is evaluated for every cell in its range whenever the sheet recalculates, so a long list of overlapping rules over whole columns slows scrolling and editing in Excel. Stop-if-true helps here too: once a cell matches an early rule, later ones are skipped. Keep ranges to the data (A2:A5000, not A:A), merge rules that differ only in colour into an ordered set like the one above, and remove rules that duplicate others — templates edited by many hands often accumulate several identical copies, which Step 4's listing makes easy to spot.

Common pitfalls

  • Relying on rules combining. Excel merges non-conflicting formats; LibreOffice does not. Make each rule complete.
  • General rules first. A broad rule at priority 1 hides specific ones in LibreOffice; order from specific to general.
  • Forgetting stopIfTrue. Without it, Excel keeps adding formats from lower-priority rules.
  • Duplicate priorities. Keep numbers unique across the sheet; renumber after editing.
  • Overlapping ranges. Priority applies across ranges too; a rule on A1:D100 can outrank one on A1:A10.
  • Colour scales, data bars and icon sets. Excel does not offer stop-if-true on these rule types themselves; place them deliberately in the priority order and test how they combine with your other rules.

Conclusion

Conditional-formatting rules are evaluated by priority, and openpyxl assigns 1, 2, 3… in the order you add them unless you set rule.priority yourself. Excel layers all matching, non-conflicting formats while LibreOffice applies only the first match, so order rules from most specific to most general, give each rule every format it needs, and set stopIfTrue=True so the first match wins in both. Renumber priorities in existing templates when needed, and render the sheet in a test to confirm the warning colours appear.

Frequently asked questions

How do I set the priority of a conditional formatting rule in openpyxl? Set rule.priority to the number you want before calling ws.conditional_formatting.add. openpyxl only assigns a priority automatically when the rule has none, numbering rules in the order they are added.

What does stopIfTrue do in conditional formatting? When a rule with stopIfTrue matches a cell, Excel stops evaluating lower-priority rules for that cell. It makes the first matching rule the only one applied.

Why do my conditional formats look different in LibreOffice and Excel? Excel applies all matching rules whose formats do not conflict, while LibreOffice applies only the first matching rule. Put specific rules first, include every format in each rule, and use stopIfTrue for consistent results.

How do I change the order of existing conditional formatting rules? Load the workbook, iterate over ws.conditional_formatting and its rules, assign new unique priority numbers, and save. Lower numbers are evaluated first.

More on conditional formatting

More on conditional formatting →