Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Style Whole Columns and Rows Efficiently with openpyxl

Format entire columns and rows in large openpyxl workbooks without slowing the script down: what column and row styles actually do (and why they do not restyle cells that already hold data), reusing one Font object instead of creating thousands, named styles for the fastest path, styling only the used range, and checking the rendered result and the file's style count.
Cell styling & layoutopenpyxl

Formatting a report column — bold amounts, a currency format, a fill on the header row — is easy in openpyxl for a few hundred cells and noticeably slow for a few hundred thousand. Two tempting shortcuts cause trouble. The first is styling the column itself through ws.column_dimensions, which looks like Excel's "select column, click Bold" but does not do what that does: cells that already hold data keep their own formatting. The second is creating a fresh Font or PatternFill object for every cell inside the loop, which works but wastes most of the run time. This page shows what column and row styles really change, measures the cost of the common ways of styling many cells, and settles on an approach that is both correct and fast: style the cells in the used range, with shared style objects or a named style, and use column and row styles only for the cells people will add later. It belongs to Styling Excel Cells with openpyxl.

Prerequisites

Bash
pip install openpyxl pillow

LibreOffice (soffice) and pdftoppm render workbooks in Step 1 to show what a column style does to existing cells. Timings in Step 2 come from the machine that built this page; the ratios between methods matter more than the absolute numbers.

Step 1 — What a column style does, and does not, do

Write a small sheet, give column B a bold dark-blue font through its column dimension, and give column C the same font cell by cell; then render it:

Python
import subprocess
from openpyxl import Workbook
from openpyxl.styles import Font
from PIL import Image

BLUE = "1F3864"
wb = Workbook()
ws = wb.active
ws.append(["Item", "Via column style", "Via cell style"])
for i in range(1, 21):
    ws.append([f"Item {i}", i * 1000.5, i * 1000.5])
ws.column_dimensions["B"].font = Font(name="Calibri", bold=True, color=BLUE, size=14)
for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    cell.font = Font(name="Calibri", bold=True, color=BLUE, size=14)
for col in "BC":
    ws.column_dimensions[col].width = 18
wb.save("column_style.xlsx")

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "column_style.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "100", "-f", "1", "-l", "1", "column_style.pdf", "colstyle"], check=True)
img = Image.open("colstyle-1.png").convert("RGB")
w = img.width
def blue_pixels(x0, x1):
    return sum(1 for x in range(int(x0), int(x1)) for y in range(img.height) if img.getpixel((x, y)) == (0x1F, 0x38, 0x64))
print("rendered blue px — column B:", blue_pixels(w * 0.15, w * 0.34), "| column C:", blue_pixels(w * 0.34, w * 0.5))
print("cell fonts as stored — B2:", ws["B2"].font.b, ws["B2"].font.sz, "| C2:", ws["C2"].font.b, ws["C2"].font.sz)

Both columns print in bold dark blue in LibreOffice — yet the cells themselves tell a different story: B2 still has the default 11-point, non-bold font, while C2 has the font that was set on it. A column style is stored once, on the <col> element of the sheet, and the cells in column B were written without a style of their own. LibreOffice fills that gap with the column's style. Excel's behaviour is the one openpyxl's own documentation describes: column and row styles apply to cells created afterwards — when someone types into an empty cell of column B, the value appears bold and blue — while cells already in the file keep their own formatting. The same report can therefore look different depending on the application that opens it, and code that reads cell fonts back sees neither version.

The safe rule follows from that: use column_dimensions[...] and row_dimensions[...] styles for cells people will add later, such as an input column below the data, and style the data you write on the cells themselves. Row dimensions behave the same way as columns.

Column style or cell style A style set on a column dimension is stored once on the column. Excel applies it to cells created later, such as values typed in, while LibreOffice also applies it to written cells that have no style of their own. A style set on each cell changes the cells that hold data in every application. column_dimensions['B'].font stored once on the column Excel: new cells only LibreOffice: unstyled cells too good for input columns cell.font for each cell stored per cell same result everywhere needed for report output cost grows with cell count

Step 2 — Measure the ways of styling many cells

Style the amounts in a 20,000-row sheet four ways and time each:

Python
import os, time
from openpyxl.styles import NamedStyle

def sheet():
    wb = Workbook()
    ws = wb.active
    for r in range(20_000):
        ws.append([f"SO-{r:05d}", r * 1.5, r % 7, r * 2.25])
    return wb, ws

def amounts(ws):
    for (b, c, d) in ws.iter_rows(min_col=2, max_col=4):
        yield b
        yield d

results = {}
for method in ["new objects per cell", "shared objects", "named style"]:
    wb, ws = sheet()
    start = time.perf_counter()
    if method == "new objects per cell":
        for cell in amounts(ws):
            cell.font = Font(color=BLUE)
            cell.number_format = "#,##0.00"
    elif method == "shared objects":
        font = Font(color=BLUE)
        for cell in amounts(ws):
            cell.font = font
            cell.number_format = "#,##0.00"
    else:
        wb.add_named_style(NamedStyle(name="amount", font=Font(color=BLUE), number_format="#,##0.00"))
        for cell in amounts(ws):
            cell.style = "amount"
    results[method] = time.perf_counter() - start
    wb.save("timing.xlsx")
    print(f"{method:<22} {results[method]:.3f}s  file {os.path.getsize('timing.xlsx') / 1024:.0f} KB  cell formats {len(wb._cell_styles)}")
base = results["new objects per cell"]
print({m: f"{base / t:.1f}x faster" for m, t in results.items() if m != "new objects per cell"})

All three produce the same file — the same size and the same small number of cell formats — because openpyxl de-duplicates identical styles when it saves: forty thousand Font(color=BLUE) objects still become one font entry. The difference is time. Creating a new Font object for every cell was nearly four times slower here than creating one and assigning it repeatedly, and a named style, which sets font and number format in a single assignment, was about eight times faster than the baseline. On a report with a dozen styled columns and hundreds of thousands of rows that is the difference between seconds and minutes. The exact ratios vary from machine to machine and run to run, so treat them as an order of magnitude; the ranking does not change.

Relative time to style 40,000 cells Creating a new Font object for each cell is the slowest. Reusing one shared Font object is several times faster, and assigning a named style that carries both font and number format is fastest. All three produce the same file. New objects per cell baseline Shared objects ≈4× faster Named style ≈8× faster

Step 3 — The pattern for a real report

Combine both tools: style the written data cell by cell with shared objects or a named style, limited to the used range, and set column and row styles for the cells people will add:

Python
from openpyxl.styles import PatternFill, Alignment

wb, ws = sheet()
ws.insert_rows(1)
for col, name in enumerate(["Order", "Amount", "Weekday", "Total"], start=1):
    ws.cell(1, col, name)

header = NamedStyle(name="header", font=Font(bold=True, color="FFFFFF"), fill=PatternFill("solid", fgColor=BLUE),
                    alignment=Alignment(horizontal="center"))
amount = NamedStyle(name="amount", font=Font(color=BLUE), number_format="#,##0.00")
wb.add_named_style(header)
wb.add_named_style(amount)
start = time.perf_counter()
for cell in ws[1]:
    cell.style = "header"
for col in ("B", "D"):
    for (cell,) in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=ord(col) - 64, max_col=ord(col) - 64):
        cell.style = "amount"
    ws.column_dimensions[col].number_format = "#,##0.00"      # for rows added later in Excel
ws.freeze_panes = "A2"
wb.save("report.xlsx")
print(f"styled {ws.max_row - 1:,} rows in {time.perf_counter() - start:.2f}s | cell formats: {len(wb._cell_styles)}")

The header row and two amount columns are styled through named styles in a fraction of a second, and the amount columns also carry a column-level number format so a value typed below the data in Excel is formatted the same way. Iterating with iter_rows over just the needed columns and rows avoids touching cells that do not need styling — and avoids creating cells: accessing ws["B30000"] on a 20,000-row sheet creates an empty cell, which then counts towards max_row. When even this is too slow — millions of cells — writing the report with XlsxWriter's column formats, applied as the data is written, is the faster route, covered in Write a Million Rows to Excel with xlsxwriter constant_memory.

Styling a large report efficiently Register named styles once, style the header row by name, loop over only the used range of the columns that need styling with iter_rows, add column-level formats for rows people will add later, and freeze the header. Register styles NamedStyle, once Header row cell.style = 'header' Used range only iter_rows per column Column formats for rows added later

Common pitfalls

  • Expecting column styles to restyle data. Excel applies them to new cells only; results differ by application.
  • A new Font per cell. Create it once and reuse it.
  • Setting font and number format separately for many cells. A named style does both in one assignment.
  • Styling whole columns cell by cell to row 1,048,576. Style the used range; it creates cells otherwise.
  • Style explosions from slightly different objects. Keep a small set of shared styles.

Conclusion

Column and row styles in openpyxl are meant for the cells people add later — Excel does not apply them to the data you write, and LibreOffice does — so style written cells directly. Do it efficiently: create style objects once and reuse them, or register a NamedStyle and assign it by name, and limit loops to the used range with iter_rows. openpyxl de-duplicates identical styles in the saved file either way, so the gain is in run time, which on large reports is several-fold.

Frequently asked questions

How do I make a whole column bold in openpyxl? Set the font on each cell in the used range of the column, reusing one Font object; column_dimensions styles only affect cells added later.

Why doesn't column_dimensions font change my existing cells? In Excel a column style applies to cells created after the file is opened, not to cells already in it; LibreOffice also applies it to unstyled existing cells, so style data cells directly for consistent results.

What is the fastest way to style many cells with openpyxl? Register a NamedStyle that combines font, fill and number format, and assign it with cell.style; reusing shared style objects is the next fastest.

Does creating many Font objects make the file bigger? No. openpyxl merges identical styles when saving, so the file is the same; it only makes the script slower.

More on cell styling & layout

More on cell styling & layout →