Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Apply a Reusable Style Theme Across an Excel Report

Stop scattering fonts and fills through your script — define a theme once with NamedStyle, apply it by role, and keep every generated report visually consistent.

The third report you write with openpyxl has the same header format as the first two, typed out again slightly differently. By the tenth, no two reports look alike, and changing the brand colour means finding every PatternFill in the codebase. A theme fixes that: define each visual role once — header, total, currency, warning — register them on the workbook as named styles, and apply them by name. This guide builds one, covers the format-ceiling error a per-cell approach eventually hits, and shows how to share the theme across reports. It extends Styling Excel Cells with openpyxl.

One definition per role, referenced everywhere Five named styles — header, body, currency, total and warning — are defined once in a theme module. Each is registered on the workbook a single time and referenced by name from any number of cells, across any number of reports. Changing a brand colour means editing one definition rather than finding every scattered fill assignment, and Excel stores each style once regardless of how many cells use it. theme.py — defined once header body currency total warning regional_report.xlsx cell.style = "header" · "currency" · "total" monthly_summary.xlsx the same names, the same look change one colour in theme.py and every report follows

Prerequisites

Bash
pip install openpyxl pandas

Before writing any code, decide the roles rather than the appearances. A theme keyed on header and total survives a redesign; one keyed on indigo_bold and green_fill does not.

RoleUsed for
titlethe report banner
subtitleperiod and generation stamp
headercolumn headings
bodyordinary data cells
currencymoney columns
percentrate columns
datedate columns
totalsummary rows
warningvalues needing attention

Step 1 — Define the palette and the roles

Keep the colours in one place, and build the styles from them:

Python
# theme.py
from openpyxl.styles import (
    Alignment, Border, Font, NamedStyle, PatternFill, Side
)

PALETTE = {
    "brand": "4338CA",
    "brand_soft": "EBEBFD",
    "ink": "172033",
    "muted": "5B6780",
    "line": "CDD5E6",
    "ok": "0B6157",
    "ok_soft": "D9F4F1",
    "warn": "BE185D",
    "warn_soft": "FEE8F2",
    "white": "FFFFFF",
}

FONT = "Calibri"
THIN = Side(style="thin", color=PALETTE["line"])


def build_styles():
    """Return the report theme as a list of NamedStyle objects."""
    title = NamedStyle(name="title")
    title.font = Font(name=FONT, size=15, bold=True, color=PALETTE["brand"])
    title.fill = PatternFill("solid", start_color=PALETTE["brand_soft"],
                             end_color=PALETTE["brand_soft"])
    title.alignment = Alignment(horizontal="center", vertical="center")

    subtitle = NamedStyle(name="subtitle")
    subtitle.font = Font(name=FONT, size=9, italic=True, color=PALETTE["muted"])
    subtitle.alignment = Alignment(horizontal="center", vertical="center")

    header = NamedStyle(name="header")
    header.font = Font(name=FONT, size=11, bold=True, color=PALETTE["white"])
    header.fill = PatternFill("solid", start_color=PALETTE["brand"],
                              end_color=PALETTE["brand"])
    header.alignment = Alignment(horizontal="center", vertical="center",
                                 wrap_text=True)
    header.border = Border(bottom=THIN)

    body = NamedStyle(name="body")
    body.font = Font(name=FONT, size=11, color=PALETTE["ink"])
    body.alignment = Alignment(vertical="center")

    currency = NamedStyle(name="currency")
    currency.font = Font(name=FONT, size=11, color=PALETTE["ink"])
    currency.number_format = "#,##0.00"
    currency.alignment = Alignment(horizontal="right", vertical="center")

    percent = NamedStyle(name="percent")
    percent.font = Font(name=FONT, size=11, color=PALETTE["ink"])
    percent.number_format = "0.0%"
    percent.alignment = Alignment(horizontal="right", vertical="center")

    date = NamedStyle(name="date")
    date.font = Font(name=FONT, size=11, color=PALETTE["ink"])
    date.number_format = "yyyy-mm-dd"
    date.alignment = Alignment(horizontal="center", vertical="center")

    total = NamedStyle(name="total")
    total.font = Font(name=FONT, size=11, bold=True, color=PALETTE["ok"])
    total.fill = PatternFill("solid", start_color=PALETTE["ok_soft"],
                             end_color=PALETTE["ok_soft"])
    total.number_format = "#,##0.00"
    total.border = Border(top=THIN)
    total.alignment = Alignment(horizontal="right", vertical="center")

    warning = NamedStyle(name="warning")
    warning.font = Font(name=FONT, size=11, bold=True, color=PALETTE["warn"])
    warning.fill = PatternFill("solid", start_color=PALETTE["warn_soft"],
                               end_color=PALETTE["warn_soft"])
    warning.number_format = "#,##0.00"
    warning.alignment = Alignment(horizontal="right", vertical="center")

    return [title, subtitle, header, body, currency, percent, date,
            total, warning]

Building them in a function rather than at module level is the important structural choice. A NamedStyle binds to the workbook it is added to, so a module-level instance added to a second workbook raises. A factory hands each workbook its own instances.

Step 2 — Register the theme on a workbook

Python
# theme.py, continued
def apply_theme(wb):
    """Register every theme style on a workbook. Safe to call twice."""
    existing = set(wb.style_names)
    for style in build_styles():
        if style.name not in existing:
            wb.add_named_style(style)
    return wb

The existing check matters because add_named_style raises on a duplicate name, and a script that opens a template already carrying the theme would otherwise fail on the second run.

Applying a style is then one assignment:

Python
from openpyxl import Workbook
from theme import apply_theme

wb = apply_theme(Workbook())
ws = wb.active

ws["A1"] = "Region"
ws["A1"].style = "header"
ws["B2"] = 5150.00
ws["B2"].style = "currency"

wb.save("report.xlsx")

Step 3 — Apply styles by column role

Assigning cell by cell is where scripts get repetitive. Drive it from a column-to-role mapping instead:

Python
from openpyxl.utils import get_column_letter

COLUMN_ROLES = {
    "region": "body",
    "owner": "body",
    "invoice_date": "date",
    "revenue": "currency",
    "margin": "percent",
}

def style_table(ws, header_row=1, roles=COLUMN_ROLES, default="body"):
    """Apply the theme to a table using a column-name to role mapping."""
    headers = {}
    for cell in ws[header_row]:
        if cell.value is None:
            continue
        cell.style = "header"
        headers[str(cell.value).strip()] = cell.column

    for name, column in headers.items():
        role = roles.get(name, default)
        for (cell,) in ws.iter_rows(min_row=header_row + 1,
                                    min_col=column, max_col=column):
            if cell.value is not None:
                cell.style = role

    ws.freeze_panes = ws.cell(row=header_row + 1, column=1).coordinate
    return headers

Keying on the header text rather than the column position means an inserted column does not misformat everything to its right — the same reasoning as keying data rows by header in iterating rows and columns with openpyxl.

A total row and conditional warnings slot in the same way:

Python
def style_total_row(ws, row, first_col=1, last_col=None):
    last_col = last_col or ws.max_column
    for col in range(first_col, last_col + 1):
        ws.cell(row=row, column=col).style = "total"

def flag_above(ws, column_letter, threshold, header_row=1):
    """Re-style cells above a threshold with the warning role."""
    from openpyxl.utils import column_index_from_string
    col = column_index_from_string(column_letter)
    flagged = 0
    for (cell,) in ws.iter_rows(min_row=header_row + 1, min_col=col, max_col=col):
        if isinstance(cell.value, (int, float)) and cell.value > threshold:
            cell.style = "warning"
            flagged += 1
    return flagged

Note that re-assigning cell.style replaces the previous style entirely rather than merging — a cell styled currency and then warning keeps only the warning's number format, which is why warning defines one. Styles are whole roles, not modifiers.

Step 4 — Avoid the format ceiling

Excel caps a workbook at roughly 64,000 distinct cell formats. A script that constructs style objects inside a loop reaches it on a large sheet, and the file then fails to open with a "too many different cell formats" error.

Where the 64,000-format ceiling comes from Two approaches to styling one hundred thousand cells. Constructing a Font or PatternFill inside the loop registers a distinct format entry per cell, so the count climbs towards and past Excel's ceiling of roughly sixty-four thousand and the workbook becomes unopenable. Assigning a NamedStyle by name registers exactly one entry regardless of how many cells reference it, so the count stays at the number of roles in the theme. styling 100,000 cells Font() in the loop 100,000 distinct format entries Excel's ~64,000 ceiling cell.style = "currency" 9 entries — one per role in the theme the count depends on how many distinct styles exist, not on how many cells use them
Python
from openpyxl.styles import Font, PatternFill

# Wrong: a new Font and PatternFill object per cell.
for row in ws.iter_rows(min_row=2, max_row=100_000):
    for cell in row:
        cell.font = Font(name="Calibri", size=11, color="172033")
        cell.fill = PatternFill("solid", start_color="FFFFFF",
                                end_color="FFFFFF")

# Right: one named style, referenced by name.
for row in ws.iter_rows(min_row=2, max_row=100_000):
    for cell in row:
        cell.style = "body"

openpyxl does deduplicate identical style objects on save, so the wrong version above may survive — but it allocates a hundred thousand short-lived objects to get there, and any variation between iterations (a colour derived from the value, say) defeats the deduplication entirely and hits the ceiling for real.

Step 5 — Share the theme across reports

Name styles by what they mean, not by how they look Two naming schemes after a rebrand changes the accent colour from indigo to teal. Roles named by meaning — header, total, warning — still describe what they are for, and only the palette changed. Roles named by appearance — indigo_bold, green_fill — now lie about their own colour, so every call site has to be found and renamed or the code becomes actively misleading. after a rebrand changes the accent colour named by meaning header · total · warning · currency still accurate; only the palette definition changed one edit, no call sites touched named by appearance indigo_bold · green_fill · pink_cell now lying about their own colour or frozen at the old palette every call site must be renamed

Because build_styles is a function, every workbook gets its own instances from the same definitions:

Python
from openpyxl import Workbook, load_workbook
from theme import apply_theme, style_table, style_total_row

def new_report(title, subtitle, columns):
    """A blank workbook with the theme applied and a banner in place."""
    wb = apply_theme(Workbook())
    ws = wb.active

    ws["A1"] = title
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=columns)
    ws["A1"].style = "title"
    ws.row_dimensions[1].height = 30

    ws["A2"] = subtitle
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=columns)
    ws["A2"].style = "subtitle"

    return wb, ws

# Every report in the project starts the same way.
wb, ws = new_report("Regional Revenue", "August 2026", columns=5)

For a report produced through pandas, apply the theme in a second pass after writing:

Python
import pandas as pd
from openpyxl import load_workbook
from theme import apply_theme, style_table

def write_themed(df, path, sheet_name="Report"):
    df.to_excel(path, sheet_name=sheet_name, index=False, engine="openpyxl")

    wb = apply_theme(load_workbook(path))
    style_table(wb[sheet_name])
    wb.save(path)
    return path

The two-pass shape is unavoidable with openpyxl, because to_excel replaces the sheet and would discard styling applied first. The banner mechanics are in merging cells and centring a report title.

Common pitfalls and fixes

SymptomCauseFix
ValueError: Style X exists alreadyTheme added twiceCheck wb.style_names before adding.
Style raises on a second workbookNamedStyle bound to the firstBuild fresh instances via a factory.
"Too many different cell formats"Style objects created per cellAssign named styles by name.
Number format lost after re-stylingcell.style replaces, not mergesGive each role its own number format.
Styling vanished after a pandas writeto_excel replaced the sheetApply the theme in a second pass.
A column formatted wronglyRoles keyed by positionKey the mapping on header text.
Row-level style ignoredSet on the row dimensionAssign to each cell in the row.
Fill renders as nothingOnly start_color givenSet both start_color and end_color.

Performance and scale notes

Named styles are the cheap path both in memory and in file size, but the per-cell assignment loop still runs in Python. On a hundred-thousand-row sheet that is measurable.

Three ways to reduce it. Style only the columns that need a non-default look — leaving ordinary text columns unstyled costs nothing and looks fine, so a nine-role theme applied to three of twelve columns does a quarter of the work.

Set the workbook's default font instead of assigning a body style to every cell:

Python
from openpyxl import Workbook

wb = Workbook()
wb._fonts[0].name = "Calibri"     # the default font all unstyled cells inherit
wb._fonts[0].sz = 11

Use xlsxwriter for large new reports. Its set_column applies a format to a whole column in one call, independent of row count, which is the single biggest win available:

Python
import pandas as pd

THEME = {
    "header": {"bold": True, "bg_color": "#4338CA", "font_color": "#FFFFFF",
               "border": 1, "align": "center", "valign": "vcenter"},
    "currency": {"num_format": "#,##0.00", "align": "right"},
    "date": {"num_format": "yyyy-mm-dd", "align": "center"},
    "total": {"bold": True, "bg_color": "#D9F4F1", "font_color": "#0B6157",
              "num_format": "#,##0.00", "top": 1},
}

def write_large_themed(df, path, roles, sheet_name="Report"):
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)
        book, sheet = writer.book, writer.sheets[sheet_name]

        formats = {name: book.add_format(spec) for name, spec in THEME.items()}

        for position, column in enumerate(df.columns):
            sheet.write(0, position, str(column), formats["header"])
            role = roles.get(column)
            sheet.set_column(position, position, 16, formats.get(role))

        sheet.freeze_panes(1, 0)
    return path

Building the format objects once into a dict is the same discipline as the named styles — add_format creates a new entry every call, so constructing one per column in a wide report inflates the workbook's format table for no benefit. And where a report is large enough to need streaming, note that write_only mode supports styles on the cells you append but not named styles registered afterwards, so the theme must be applied as each row is built — see writing large DataFrames with write-only mode.

Conclusion

A theme turns styling from something scattered through a script into one module with a palette and a set of roles. Define the roles by meaning — header, currency, total, warning — not by appearance, build them in a factory function so each workbook gets its own NamedStyle instances, and register them once with a guard against duplicates. Apply them through a column-name-to-role mapping so an inserted column cannot misformat everything after it. Then never construct a style object inside a loop: that is what produces the 64,000-format error, and a named style avoids it entirely.

Frequently asked questions

What is a NamedStyle and why use one? A NamedStyle is a style registered once on the workbook and referenced by name from any cell. Excel stores it once regardless of how many cells use it, and a reader sees it in the cell-styles gallery, so it is both cheaper and more discoverable than assigning fonts and fills per cell.

What causes the "too many different cell formats" error? Creating a fresh style object inside a loop. Excel caps a workbook at roughly 64,000 distinct cell formats, and a script that builds a new Font or PatternFill per row reaches it on a large sheet. Define each style once outside the loop.

Can I add the same NamedStyle to two workbooks? Not the same object — a NamedStyle binds to the workbook it is added to. Build them from a plain configuration dictionary with a factory function so each workbook gets its own equivalent instances.

How do I apply a style to a whole row or column? There is no single call; assign the style to each cell in the range. Row and column dimension objects accept a style, but it only affects cells that have never been written to, so it is unreliable for a data region.

Should the theme live in code or in a config file? A dictionary in one module is enough for a single project. Move it to a config file when several scripts must match, or when somebody non-technical needs to change the brand colours without touching Python.