Add Data Bars and Colour Scales with openpyxl

A data bar or a colour scale answers a question a number cannot: which of these rows is big, and where does this one sit in the range. Because they are conditional formatting rules rather than computed colours, Excel re-evaluates them whenever the values change — so the visual stays correct after a reader filters, sorts or edits the table.

This guide covers openpyxl's DataBarRule and ColorScaleRule, the endpoint choice that decides whether the visual is informative or flat, and when each is the right pick. It is part of Applying Conditional Formatting with openpyxl.

Prerequisites

pip install openpyxl pandas

The examples build their own workbook, so they run as written.

Step 1: A sheet with a spread worth showing

import pandas as pd

df = pd.DataFrame({
    "region": ["North", "South", "East", "West", "Central", "Highlands"],
    "revenue": [150_250, 274_750, 75_000, 190_400, 88_100, 1_240_000],
    "variance": [0.041, -0.062, -0.128, 0.012, -0.005, 0.083],
})

with pd.ExcelWriter("visuals.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Regions", index=False)
    ws = writer.sheets["Regions"]
    for cell in ws["B"][1:]:
        cell.number_format = '#,##0'
    for cell in ws["C"][1:]:
        cell.number_format = "0.0%"
    ws.column_dimensions["A"].width = 14
    ws.column_dimensions["B"].width = 14
    ws.column_dimensions["C"].width = 12
    ws.freeze_panes = "A2"

Highlands at 1.24 million is deliberate. It is the outlier that makes the endpoint choice in step 3 visible instead of theoretical.

Step 2: Add a data bar

from openpyxl import load_workbook
from openpyxl.formatting.rule import DataBarRule

wb = load_workbook("visuals.xlsx")
ws = wb["Regions"]
last = ws.max_row

ws.conditional_formatting.add(
    f"B2:B{last}",
    DataBarRule(start_type="num", start_value=0,
                end_type="max",
                color="5B5CF0", showValue=True),
)
wb.save("visuals.xlsx")

Anchoring start_value at zero rather than at the minimum is usually right for a magnitude: with start_type="min", the smallest value gets a zero-length bar, which reads as "nothing" when it may be a perfectly respectable 75,000.

showValue=True keeps the number in the cell alongside the bar. Setting it to False gives a bar-only column, which looks clean and removes the actual figure — worth it only when the exact number genuinely does not matter.

Step 3: Choose endpoints that survive an outlier

With end_type="max", Highlands takes the full bar and every other region is squashed into the first eighth of the column. Percentile endpoints clip the extremes so the rest of the distribution still spreads out:

ws.conditional_formatting.add(
    f"B2:B{last}",
    DataBarRule(start_type="percentile", start_value=10,
                end_type="percentile", end_value=90,
                color="5B5CF0", showValue=True),
)

Values beyond the endpoints simply render at full or empty rather than breaking, so nothing is lost — the outlier still shows as the longest bar, and the other five are now comparable to each other. The 10/90 pair is a reasonable default for report data; tighten it to 25/75 when the tails are long.

Step 4: Colour scales for position

A colour scale suits a measure with no agreed threshold, where the useful question is where a value sits relative to the others:

from openpyxl.formatting.rule import ColorScaleRule

# Three-colour: red low, amber middle, green high
ws.conditional_formatting.add(
    f"B2:B{last}",
    ColorScaleRule(
        start_type="percentile", start_value=10, start_color="FFC7CE",
        mid_type="percentile", mid_value=50, mid_color="FFEB9C",
        end_type="percentile", end_value=90, end_color="C6EFCE",
    ),
)

# Two-colour, anchored on zero — right for a signed measure like variance
ws.conditional_formatting.add(
    f"C2:C{last}",
    ColorScaleRule(start_type="num", start_value=-0.1, start_color="FFC7CE",
                   end_type="num", end_value=0.1, end_color="C6EFCE"),
)
wb.save("visuals.xlsx")

The colours pair with the positions in order, so start_color belongs to start_type. For a measure where high is bad — days overdue, error counts — put the red at the end rather than trying to reverse the data.

Anchoring the variance scale at fixed values rather than percentiles is deliberate: a percentile scale recolours itself every month as the data moves, so an unchanged −2% can look green in one report and amber in the next. Fixed endpoints make two consecutive reports comparable, which is usually what the reader assumes is happening anyway.

Step 5: Icon sets when only the verdict matters

from openpyxl.formatting.rule import IconSetRule

ws.conditional_formatting.add(
    f"C2:C{last}",
    IconSetRule("3Arrows", "num", [-0.02, 0, 0.02],
                showValue=True, reverse=False),
)
wb.save("visuals.xlsx")

The three thresholds are the lower bounds of each icon's band, so the first value should be at or below the smallest number you expect. showValue=False gives an icon-only column, which works well as a status marker beside a labelled figure rather than in place of one.

Icon sets are the least portable of the three: LibreOffice and several viewers render them inconsistently, so if the workbook goes outside your organisation, a colour scale or a plain cell rule travels better.

Why a rule beats a computed colour

It is tempting to skip all of this and colour the cells directly — work out the percentile in pandas, then write a fill per cell. The output looks identical the moment it is generated, and it stops being identical the moment anyone touches it.

Sorting is the obvious case, but the same applies to every ordinary thing a reader does: filtering, pasting in an updated figure, inserting a row. A written fill is a statement about a cell; a rule is a statement about the data, and only the second survives being used.

There are two situations where writing the colour directly is still right. The first is a value that is not on a scale at all — a flag, a status word, an exception marker that came out of a validation step and has no numeric meaning for Excel to re-evaluate. The second is a workbook destined for conversion rather than reading: a sheet exported straight to PDF is a snapshot, and a rule and a fill produce the same picture.

Everywhere else, the rule is the cheaper choice as well as the more correct one. It is a handful of bytes in the file regardless of the range, where a per-cell fill is a style record per cell — the difference between a workbook that opens instantly and one that takes a moment to render every time it is scrolled.

Common pitfalls and gotchas

Performance and scale notes

One rule over a large range is cheap to write and cheap to store. Thousands of single-cell rules are neither — if a loop is calling conditional_formatting.add per row, replace it with one call over the whole range.

Excel re-evaluates colour scales and data bars on every recalculation, and over hundreds of thousands of rows that is noticeable when scrolling. On a large detail sheet, put the visuals on the summary and leave the detail plain; the reader is scanning the summary anyway. Where the range is large and openpyxl's memory footprint matters, note that conditional formatting cannot be added in write-only mode — build the sheet normally, or apply the rules in a second pass.

Conclusion

Data bars answer "which is biggest", colour scales answer "where does this sit", and icon sets answer "is this one all right". Pick endpoints deliberately: percentiles when one outlier would flatten the column, fixed numbers when two consecutive reports need to be comparable. Add one rule per range rather than per cell, keep the header out of the range, and remember that the rule lives in the file — so it stays right when the reader edits, sorts or filters underneath it.

Frequently asked questions

What is the difference between min/max and percentile endpoints?min and max scale the visual to the actual smallest and largest values, so one outlier flattens everything else. Percentile endpoints — typically 10 and 90 — clip the extremes so the middle of the distribution still spreads across the range.

Why are my colours the wrong way round?ColorScaleRule takes its colours in the same order as its positions, so start_color goes with start_type. For a "high is bad" measure, put the red at the max end rather than reversing the values.

Do data bars handle negative numbers? The rule openpyxl writes uses the older data bar specification, which draws every bar from the left edge. For a column with negatives, a three-colour scale reads correctly where a bar does not.

Can I use these on a sheet pandas wrote? Yes. Write the data with pandas, reopen with openpyxl (or reach the sheet through writer.sheets), then add the rule to the range. The rule is stored in the file and applies whenever it is opened.

Related

Up to the parent guide:

• Applying Conditional Formatting with openpyxl — the rule families and how Excel evaluates them.

Related guides:

• openpyxl: Apply Conditional Formatting to a Range — range strings, anchors and multi-block ranges.
• Apply Conditional Formatting with xlsxwriter — the same visuals on the write-only engine.
• Highlight Invalid Cells in Excel with Python — flagging failures rather than showing a distribution.
• Format Excel Cells as Currency with Python — the number formats these visuals sit on top of.