Highlight Top and Bottom Values in Excel with openpyxl

"Who are our top three reps?" and "which products are in the bottom 10%?" are questions a report should answer at a glance. Sorting answers them once; conditional formatting answers them permanently, because the spreadsheet re-evaluates the rule whenever the numbers change. openpyxl can write every variant — top or bottom N, top or bottom percent, above or below average — plus the one Excel's menu does not offer: the top value within each group. This page, part of Applying Conditional Formatting With openpyxl, writes each rule and checks that it marks the cells it should.

Prerequisites

pip install openpyxl

sales.xlsx has seven reps across three regions, with two reps tied on 980.

from openpyxl import load_workbook
from openpyxl.formatting.rule import Rule, FormulaRule
from openpyxl.styles import PatternFill, Font
from openpyxl.styles.differential import DifferentialStyle

GREEN = DifferentialStyle(fill=PatternFill("solid", bgColor="C6EFCE"), font=Font(color="006100"))
PINK = DifferentialStyle(fill=PatternFill("solid", bgColor="FFC7CE"), font=Font(color="9C0006"))

The two styles are Excel's own "Good" and "Bad" colours. Conditional formats take a DifferentialStyle — only the parts you set are applied over the cell's normal formatting — and for solid fills the colour goes in bgColor.

Step 1 — Highlight the top and bottom N values

The rule type is called top10 whatever N is. rank sets N, and bottom=True flips it to the lowest values:

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]
ws.conditional_formatting.add("C2:C8", Rule(type="top10", rank=2, dxf=GREEN))
ws.conditional_formatting.add("C2:C8", Rule(type="top10", rank=1, bottom=True, dxf=PINK))
wb.save("top_bottom.xlsx")
print([(str(cf.sqref), [r.type for r in cf.rules]) for cf in ws.conditional_formatting])

The rules are stored against the range, not copied into cells, so they apply to whatever values C2:C8 hold when the workbook is opened. Extend the range when the data grows — a rule on C2:C8 ignores a new row 9 — or apply it to a generous range such as C2:C1000; empty cells are never highlighted by a top rule.

When a cell matches more than one rule — 980 is both a top value and, say, above a 500 threshold — the rule with the higher priority wins. openpyxl numbers rules in the order you add them, starting at priority 1, and priority 1 is applied first:

print([[r.priority for r in cf.rules] for cf in ws.conditional_formatting])
# [[1, 2]] — the top rule outranks the bottom rule

Add the rule you want to dominate first. Rendering a test file with a green top rule added before a pink "greater than 500" rule shows 980 in green and 660, which matches only the second rule, in pink.

Step 2 — Understand ties and percentages

With two reps on 980, "top 2" marks both of them and no one else, because both cells hold one of the two highest values. A rule for the top three would mark three cells here, not four — 980, 980 and 660 — but if two reps shared third place it would mark four. That is the behaviour most readers expect from a league table. When exactly N rows must be marked, rank in Python and write a flag, or break ties in the data.

percent=True turns the rank into a percentage of the cells in the range. A top 20% rule over ten values marks two of them:

ws.conditional_formatting.add("C2:C8", Rule(type="top10", rank=20, percent=True, dxf=GREEN))

Percent rules are the better choice for ranges whose size changes from run to run — "the top fifth of products" stays meaningful whether the report has 50 rows or 500.

Step 3 — Highlight values above or below the average

The aboveAverage rule compares each cell with the mean of the range. aboveAverage=False gives the below-average version; equalAverage=True includes values exactly at the mean:

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]
ws.conditional_formatting.add("C2:C8", Rule(type="aboveAverage", aboveAverage=True, dxf=GREEN))
ws.conditional_formatting.add("C2:C8", Rule(type="aboveAverage", aboveAverage=False, dxf=PINK))
wb.save("average.xlsx")

Average rules suit sheets where "better or worse than typical" matters more than rank — a column of delivery times, say. The mean is recalculated from the range every time, so outliers pull it; for skewed data a top or bottom percent rule is often more informative.

Step 4 — Highlight whole rows

Marking the entire row of a top performer reads better than colouring one number. The built-in rules only test the cell they sit on, so use a formula rule over the full row range. The formula is written for the top-left cell of the range, and the $ before C keeps every cell in a row looking at that row's sales:

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]
ws.conditional_formatting.add("A2:C8", FormulaRule(formula=["$C2>=LARGE($C$2:$C$8,3)"], fill=PatternFill("solid", bgColor="C6EFCE")))
wb.save("top_rows.xlsx")

LARGE(range, 3) is the third-highest value, so the rule marks every row at or above it — the same tie behaviour as the built-in rule. Swap in SMALL for the bottom rows. Highlight an Entire Row Based on a Cell Value with openpyxl explains the anchoring rules for row formulas in more depth.

Step 5 — Highlight the top value in each group

A league table per region — the best rep in North, in South, in East — is not something the built-in rules can do, because they rank the whole range. A COUNTIFS formula can: a row is the top of its region when no other row in the same region has higher sales:

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]
rule = FormulaRule(formula=['COUNTIFS($A$2:$A$8,$A2,$C$2:$C$8,">"&$C2)<1'],
                   fill=PatternFill("solid", bgColor="C6EFCE"))
ws.conditional_formatting.add("A2:C8", rule)
wb.save("top_per_region.xlsx")

Change <1 to <2 for the top two per region, and flip ">" to "<" for the bottom. The formula counts, for each row, how many rows in the same region beat it; ties again count as equal, so Ben and Cleo are both marked as North's top.

Step 6 — Check the highlighting without Excel

A rule that is syntactically fine but logically wrong — a missing $, an off-by-one range — produces a file that opens without complaint and highlights the wrong cells. On a server without Excel, LibreOffice can evaluate the rules for you: its HTML export writes each cell's resulting background colour, so a few lines of Python can list which cells ended up highlighted:

import re, subprocess, html, pathlib

def highlighted(path, colour="#C6EFCE"):
    subprocess.run(["soffice", "--headless", "--convert-to", "html", "--outdir", "render", path],
                   check=True, capture_output=True, timeout=120)
    page = pathlib.Path("render", pathlib.Path(path).stem + ".html").read_text(encoding="utf-8", errors="ignore")
    cells = re.findall(r'<td[^>]*bgcolor="' + colour + r'"[^>]*>(.*?)</td>', page, re.S)
    return [html.unescape(re.sub(r"<[^>]+>", "", c)).strip() for c in cells]

print(highlighted("top_bottom.xlsx"))
print(highlighted("top_per_region.xlsx"))

The first file marks both 980 cells; the second marks the rows of Ben, Cleo, Dev and Gus — exactly the per-region winners. Turn the expected lists into assertions in a test and a broken rule fails the build instead of reaching readers.

Common pitfalls

• A range that stops short of the data. New rows outside the rule's range are never highlighted.
• Missing $ in row formulas. =C2>=… without the dollar shifts the tested column across the row.
• Expecting exactly N cells. Ties mark every tied cell.
• Solid fills in fgColor. For conditional formats, put the colour in bgColor.
• Too many colours. One highlight for "good" and one for "bad" is readable; five rules on one column are not.

Performance notes

Rules are stored once per range and cost nothing at write time. COUNTIFS rules are evaluated per cell by the spreadsheet, which is fine for thousands of rows; on sheets of hundreds of thousands, a flag column computed in pandas and a simple rule on it (=$D2="top") recalculates faster.

Conclusion

Rule(type="top10") with rank, bottom and percent covers top and bottom N and percentages, ties included; Rule(type="aboveAverage") compares with the mean; a LARGE-based formula rule colours whole rows; and a COUNTIFS formula rule finds the top value in each group. Anchor columns with $, extend ranges to cover growing data, and verify the result by rendering the workbook with LibreOffice before it is sent.

Frequently asked questions

Why does a top 3 rule highlight four cells? Ties. The rule highlights every cell whose value is among the top three values, so if two reps share the third-highest figure, both are marked. That is usually what readers expect; rank the data in Python if exactly N rows must be marked.

Can I highlight the top value in each region? Not with the built-in top10 rule, which ranks the whole range. Use a formula rule with COUNTIFS that counts how many rows in the same region have higher sales, and highlight the rows where that count is zero, as shown in Step 5.

How do I highlight the whole row instead of one cell? Apply a formula rule to the full row range with the column locked, for example =$C2>=LARGE($C$2:$C$8,3) on A2:C8. The dollar sign before C makes every cell in a row test the same sales value.

Does the highlighting update when the numbers change? Yes. Conditional formatting is evaluated by the spreadsheet whenever values change, so a rule written once keeps marking the current top values after edits or refreshes.

Related

• Up one level: Applying Conditional Formatting With openpyxl — every rule type.
• Highlight Cells Above a Threshold with openpyxl — fixed cut-offs instead of ranks.
• Get the Top N Rows per Group in pandas — computing the same answer in Python.
• RANK and PERCENTILE Formulas in pandas — ranking without formatting.