Apply Built-In Cell Styles Like Good, Bad and Neutral

Excel ships with a gallery of named cell styles — Good in green, Bad in red, Neutral in yellow, Title, Heading 1 to 4, Input, Calculation, Check Cell, Total, Note and a family of accent colours. People who build models by hand use them because they carry meaning: an Input cell is one you may change, a Calculation cell is a formula, Bad flags a problem. A generated report that uses the same styles fits in with the rest of a finance team's workbooks, and anyone who edits it later finds the styles in Excel's Cell Styles gallery rather than a scatter of one-off colours. openpyxl knows all of them and can apply them by name. The catch is that some of its names differ from what Excel's gallery shows. This page lists the names, applies status styles to a KPI table by value, combines styles with number formats and direct formatting, checks the colours in a rendered copy, and compares the approach with conditional formatting. It belongs to Styling Excel Cells with openpyxl.

Prerequisites

pip install openpyxl pillow

LibreOffice (soffice) and pdftoppm are used only to render the workbook and check the colours. Custom named styles of your own — a company theme — are covered in Apply a Reusable Style Theme Across an Excel Report; this page is about the ones Excel already has.

Step 1 — The names openpyxl uses

from openpyxl.styles.builtins import styles as BUILTIN

print(len(BUILTIN), "built-in styles")
print([n for n in BUILTIN if not n.startswith(("20 %", "40 %", "60 %"))])
print(BUILTIN["Good"].fill.fgColor.rgb, BUILTIN["Bad"].fill.fgColor.rgb, BUILTIN["Neutral"].fill.fgColor.rgb)

openpyxl defines Excel's built-in styles in openpyxl.styles.builtins, each with the fonts, fills, borders and number formats Excel uses, and the builtinId that identifies it to Excel. Most names match Excel's gallery exactly — Good, Bad, Neutral, Title, Input, Output, Calculation, Check Cell, Linked Cell, Total, Note, Warning Text, Explanatory Text, Comma, Currency, Percent. Two families do not: Excel's Heading 1 to Heading 4 are Headline 1 to Headline 4 in openpyxl, and 20% - Accent1 is 20 % - Accent1, with a space before the percent sign. Using Excel's spelling raises ValueError: Heading 1 is not a known style, so copy names from this list. The last entry, Pandas, is not an Excel style at all — openpyxl defines it for the header cells pandas writes — and is best ignored. In the file the style is identified by its builtinId, so Excel shows it under its own name — in the user's language — whatever openpyxl calls it.

Step 2 — Apply status styles by value

A KPI table where each result is marked Good, Neutral or Bad against its target:

from openpyxl import Workbook

kpis = [("Revenue (k)", 1284, 1200, "up"), ("Gross margin %", 31.2, 33.0, "up"), ("Orders", 3412, 3400, "up"),
        ("Return rate %", 2.1, 2.5, "down"), ("Days sales outstanding", 52, 45, "down")]

wb = Workbook()
ws = wb.active
ws.title = "KPIs"
ws["A1"] = "Monthly KPIs — September 2026"
ws["A1"].style = "Title"
ws.append([])
ws.append(["Measure", "Actual", "Target", "Status"])
for cell in ws[3]:
    cell.style = "Headline 3"

def status(actual, target, good_when, tolerance=0.02):
    ratio = actual / target if good_when == "up" else target / actual
    return "Good" if ratio >= 1 else "Neutral" if ratio >= 1 - tolerance else "Bad"

for name, actual, target, good_when in kpis:
    ws.append([name, actual, target, status(actual, target, good_when)])
    r = ws.max_row
    ws[f"B{r}"].style = "Calculation"
    ws[f"C{r}"].style = "Input"
    ws[f"D{r}"].style = ws[f"D{r}"].value
ws.column_dimensions["A"].width = 26
for col in "BCD":
    ws.column_dimensions[col].width = 12
print([(ws[f"A{r}"].value, ws[f"D{r}"].style) for r in range(4, ws.max_row + 1)])

Assigning a style name to cell.style registers the built-in style in the workbook the first time it is used and points the cell at it. The status rule allows a 2% tolerance, so being just short of target is Neutral rather than Bad; for measures where lower is better, the ratio is inverted. Targets are styled Input — orange fill, blue text — because they are the cells a reader might legitimately change, and actuals Calculation, the style Excel uses for computed values. The heading row uses Headline 3, Excel's Heading 3.

Step 3 — Number formats and direct formatting on top

A named style sets a full set of formatting. Changing one attribute of a cell afterwards — its number format, say — keeps the style but adds a direct override:

from openpyxl.styles import Font

for r in range(4, ws.max_row + 1):
    fmt = "0.0" if "%" in ws[f"A{r}"].value else "#,##0"
    ws[f"B{r}"].number_format = fmt
    ws[f"C{r}"].number_format = fmt
ws["A1"].font = Font(name="Calibri", size=16, bold=True, color="1F3864")
wb.save("kpis.xlsx")

check = __import__("openpyxl").load_workbook("kpis.xlsx")["KPIs"]
print("B5:", check["B5"].style, check["B5"].number_format, "| A1:", check["A1"].style, check["A1"].font.sz)

The actual for gross margin is still in the Calculation style but now shows one decimal place, and the title is still Title with a larger, darker font. In Excel both cells show their style as applied, with the overrides on top; if someone later modifies the Calculation style in the gallery, cells using it update except for the attributes overridden directly. Keeping overrides to number formats, and leaving colours to the style, keeps the workbook consistent.

Step 4 — Check the colours, and when to use conditional formatting

Render the sheet and count the status colours:

import subprocess
from PIL import Image

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "kpis.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "80", "-f", "1", "-l", "1", "kpis.pdf", "kpis"], check=True)
counts = {c: n for n, c in Image.open("kpis-1.png").convert("RGB").getcolors(maxcolors=2_000_000)}
for name in ["Good", "Neutral", "Bad", "Input"]:
    rgb = tuple(int(BUILTIN[name].fill.fgColor.rgb[i:i + 2], 16) for i in (2, 4, 6))
    print(f"{name:<8} fill #{BUILTIN[name].fill.fgColor.rgb[2:]}  {counts.get(rgb, 0):>5} px")

The green Good fill and the red Bad fill appear, along with the orange Input fill on the targets; Neutral has no pixels because no KPI fell inside the 2% tolerance this month. These styles are applied once, from values known when the report is written, so they do not change if someone edits a number in Excel. When the colour must follow the value as it changes — an input sheet, a model people play with — use conditional formatting instead, which can apply the same colours through a rule, as in Highlight Cells Above a Threshold with openpyxl. For a finished report, built-in styles are simpler, faster to open and visible in the gallery.

Styles in a larger report

Built-in styles pay off most in workbooks with several sheets and several authors. A finance team that uses Input for assumptions and Calculation for formulas across all its models can open any generated workbook and know immediately which cells are safe to change, without reading documentation. The same convention makes review easier: filtering a sheet by cell colour in Excel, or listing cells by cell.style in Python, shows every assumption in one pass. Because each cell refers to the style by name rather than carrying its own copy of the formatting, styled workbooks also stay smaller and faster than ones where every cell has individually set fonts and fills — a difference that becomes noticeable on sheets with tens of thousands of formatted cells. And when the house colours change, editing the style once in Excel's gallery restyles every cell that uses it.

Common pitfalls

• Excel's spelling of names. openpyxl uses Headline 1 and 20 % - Accent1.
• Expecting styles to update with values. They are fixed; use conditional formatting for live colours.
• Overriding colours directly. Override number formats, leave colours to the style.
• Inventing near-duplicates. Use the built-ins where they fit before defining your own.
• A status rule with no tolerance. Missing a target by 0.1% need not be Bad.

Conclusion

openpyxl applies Excel's built-in cell styles by name with cell.style = "Good", registering each in the workbook on first use. Use the names in openpyxl.styles.builtins — Headline 1 for Heading 1, 20 % - Accent1 for the accent tints — decide Good, Neutral or Bad from values in Python, style inputs and calculations so readers know which cells to change, and add only number formats on top. Choose conditional formatting instead when the colours must follow edits.

Frequently asked questions

How do I apply the Good, Bad or Neutral style with openpyxl? Assign the style name to the cell: cell.style = "Good". openpyxl adds the built-in style to the workbook the first time it is used.

Why does openpyxl reject "Heading 1"? openpyxl names Excel's Heading 1 to 4 styles Headline 1 to 4; Excel still shows them as Heading 1 to 4 because it identifies them by built-in id.

Can I change the number format of a cell that uses a built-in style? Yes. Set cell.number_format after applying the style; the style stays applied and the number format is a direct override.

Should I use built-in styles or conditional formatting for status colours? Built-in styles for finished reports where values do not change, conditional formatting when the colour must update as people edit the values.

Related

• Up one level: Styling Excel Cells with openpyxl — cell styling from Python.
• Apply a Reusable Style Theme Across an Excel Report — custom named styles.
• Build a Monthly KPI Scorecard with Targets — a full KPI report.
• Copy Cell Styles with openpyxl — reusing formatting.