Apply a Colour Scale per Row or per Column with openpyxl

A colour scale is one of the quickest ways to make a table of numbers readable: low values red, high values green, everything in between shaded. Applied to a whole table, though, it compares every cell with every other cell. In a table of monthly sales by product, the best-selling product's months are all green and every smaller product's months are all red, so the chart tells you which products are big — which you knew — and hides what you wanted to see: each product's good and bad months. The fix is to give each row its own colour scale, or each column, depending on which comparison matters. This page shows the problem on a real table, applies per-row and per-column scales with openpyxl, compares min-max anchoring with percentiles, keeps the rule count under control on larger tables, and renders the result to check it. It belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

pip install openpyxl pandas pillow

LibreOffice (soffice) and pdftoppm render the sheets for the visual check. The data is twelve months of sales for five products whose sizes differ by a factor of more than fifty.

Step 1 — The table, and one scale over everything

import numpy as np
import pandas as pd
from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule

months = pd.period_range("2025-10", "2026-09", freq="M").strftime("%b")
rng = np.random.default_rng(4)
base = {"Bolts": 52000, "Nuts": 18000, "Washers": 6500, "Rivets": 2400, "Brackets": 900}
season = 1 + 0.25 * np.sin(np.arange(12) / 12 * 2 * np.pi)
sales = pd.DataFrame({p: (b * season * rng.uniform(0.85, 1.15, 12)).round(-1) for p, b in base.items()}, index=months).T

def write_table(ws, df):
    ws.append(["Product", *df.columns])
    for name, row in df.iterrows():
        ws.append([name, *row.tolist()])
    for row in ws.iter_rows(min_row=2, min_col=2):
        for c in row:
            c.number_format = "#,##0"
    ws.column_dimensions["A"].width = 11
    ws.page_setup.orientation = "landscape"                 # one printed page per sheet for the check
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_setup.fitToWidth, ws.page_setup.fitToHeight = 1, 1

RED_YELLOW_GREEN = dict(start_type="min", start_color="F8696B", mid_type="percentile", mid_value=50,
                        mid_color="FFEB84", end_type="max", end_color="63BE7B")
wb = Workbook()
whole = wb.active
whole.title = "One scale"
write_table(whole, sales)
whole.conditional_formatting.add("B2:M6", ColorScaleRule(**RED_YELLOW_GREEN))
print(sales.iloc[:, :4].astype(int).to_string())

With one rule over B2:M6, the bottom of the scale is the smallest number in the whole table — a Brackets month of a few hundred — and the top is the best Bolts month. Every Bolts cell lands near green, and Rivets and Brackets are red all year, however their months compare with each other. The colour encodes product size, not performance — the rendered sheet in Step 4 shows Bolts in green and the two smallest products in solid red across all twelve months.

Step 2 — One scale per row

Give every row its own rule, so each product is compared only with its own months:

per_row = wb.create_sheet("Per row")
write_table(per_row, sales)
last_col = per_row.cell(1, per_row.max_column).column_letter
for r in range(2, per_row.max_row + 1):
    per_row.conditional_formatting.add(f"B{r}:{last_col}{r}", ColorScaleRule(**RED_YELLOW_GREEN))
print("rules on 'Per row':", sum(len(cf.rules) for cf in per_row.conditional_formatting))

Now each row runs from red in its own weakest month to green in its strongest, and the seasonal pattern — strongest from December to February, weakest from May to August — shows up across all five products, including the small ones that were solid red before. The rules are identical apart from their range; openpyxl stores each as a separate entry, so five rows mean five rules.

Step 3 — Per column, and percentiles instead of min and max

When the comparison that matters is between products within a month, scale each column instead:

per_col = wb.create_sheet("Per column")
write_table(per_col, sales.div(sales.sum()))           # share of each month's sales
for c in range(2, per_col.max_column + 1):
    letter = per_col.cell(1, c).column_letter
    per_col.conditional_formatting.add(f"{letter}2:{letter}{per_col.max_row}", ColorScaleRule(**RED_YELLOW_GREEN))
for row in per_col.iter_rows(min_row=2, min_col=2):
    for cell in row:
        cell.number_format = "0.0%"

PERCENTILE = dict(start_type="percentile", start_value=10, start_color="F8696B", mid_type="percentile", mid_value=50,
                  mid_color="FFEB84", end_type="percentile", end_value=90, end_color="63BE7B")
robust = wb.create_sheet("Per row, percentiles")
write_table(robust, sales.assign(**{"Dec": sales["Dec"].where(sales.index != "Nuts", sales.loc["Nuts", "Dec"] * 4)}))
for r in range(2, robust.max_row + 1):
    robust.conditional_formatting.add(f"B{r}:M{r}", ColorScaleRule(**PERCENTILE))
wb.save("colour_scales.xlsx")

Column scales compare items within each month — here each product's share of the month, so the colour shows which products gained or lost share. The last sheet shows a second refinement: anchoring the ends at the 10th and 90th percentiles rather than the minimum and maximum. One exceptional value — a Nuts order four times the usual size in December — would otherwise take the whole green end for itself and push every other Nuts month towards red; with percentile anchors, the outlier is simply green and the other months keep their shading. Use min and max when the extremes are the point, percentiles when one-off spikes should not dominate. Colour scales in general are introduced in Add Data Bars and Colour Scales with openpyxl.

Step 4 — Check the result, and mind the rule count

import subprocess
from openpyxl import load_workbook
from PIL import Image

check = load_workbook("colour_scales.xlsx")
for ws in check.worksheets:
    print(f"{ws.title:<22} rules: {sum(len(cf.rules) for cf in ws.conditional_formatting)}")

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "colour_scales.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "60", "colour_scales.pdf", "scales"], check=True)
def reds_and_greens(png):
    counts = Image.open(png).convert("RGB").getcolors(maxcolors=10**6)
    red = sum(n for n, (r, g, b) in counts if r > 200 and g < 160 and b < 160)
    green = sum(n for n, (r, g, b) in counts if g > 150 and r < 170 and b < 160)
    return red, green
for page, name in [(1, "One scale"), (2, "Per row")]:
    red, green = reds_and_greens(f"scales-{page}.png")
    print(f"{name:<10} red-ish px {red:>6} | green-ish px {green:>6}")

The One scale sheet has one rule and the per-row sheets five each — one per product — while the column sheet has twelve. In the rendered pages, the single-scale sheet has more than twice as many red as green pixels, because four of the five products sit at the bottom of the shared range, while the per-row sheet shows a balance of red and green, since every row spans the full scale. On a large table the rule count matters: a rule per row on a 10,000-row sheet means 10,000 conditional formatting entries, which makes the file slower to open and edit. For long tables, scale per column — usually a few dozen rules — or colour in pandas and write fills directly when the colours never need to update.

Labelling what the colours mean

Per-row and per-column scales change what a colour means, and readers need to know which they are looking at: "green" on a per-row sheet means this product's best month, not a big month. Put a one-line note above the table — "Colours compare each product with its own months" — or in the sheet title, and keep the same convention across a workbook. If both views are useful, give them separate sheets, as here, rather than mixing per-row and per-column scales on one table, where no single reading of the colours would be right. When the numbers are going to be read in print or by people with red–green colour blindness, a blue–white–orange scale keeps the order visible without relying on red and green.

Common pitfalls

• One scale over items of very different sizes. It shows size, not performance.
• A rule per row on huge tables. Thousands of rules slow Excel down noticeably.
• Min/max anchors with outliers. One spike claims the whole end of the scale.
• Percentages and totals in the same range. Keep total rows and columns outside the scaled range.
• Red for "low" when low is good. Swap the colours for costs, returns or error rates.

Conclusion

A colour scale compares every cell in its range, so apply it to the range that holds the comparison you want: one ColorScaleRule per row to compare each item with itself, one per column to compare items within each period. Anchor the ends at percentiles when single outliers would otherwise dominate, keep totals outside the scaled ranges, and on large tables prefer per-column rules or static fills so the workbook stays responsive.

Frequently asked questions

How do I apply a separate colour scale to each row in openpyxl? Loop over the rows and add a ColorScaleRule to each row's range, for example B2:M2, B3:M3 and so on.

Why is my whole colour scale red except one row? A single scale uses the minimum and maximum of the whole range, so one large row takes the green end; apply a scale per row or per column.

Should colour scales use percentiles or min and max? Use percentiles such as the 10th and 90th when outliers would otherwise take the ends of the scale, and min and max when the extremes are what matters.

Do many conditional formatting rules slow Excel down? Yes. Thousands of rules make files slower to open and edit; use per-column rules or static fills for very long tables.

Related

• Up one level: Applying Conditional Formatting with openpyxl — rules from Python.
• Add Data Bars and Colour Scales with openpyxl — the basics.
• Turn a Pivot Table into a Heatmap in Excel — heatmaps from pivots.
• Highlight Values Outside a Tolerance of Target with openpyxl — rule-based highlights.