Python Excel AutomationPractical guides for developers
Start learning

Guide

Formatting And Charting Excel Reports With PythonDeep dive

Styling Excel Cells with openpyxl

Style Excel cells from Python with openpyxl: Font, PatternFill, Border, Alignment, header rows in a loop, merged titles, and reusable NamedStyles — all runnable.

A report that no one styles looks like a database dump. With openpyxl you set fonts, fills, borders, and alignment directly on cells, and because you are editing the Office Open XML file itself, those styles persist exactly as written. This page is part of Formatting and Charting Excel Reports with Python, and it walks through every styling object you reach for in a real reporting script. Each block runs in order against a sample workbook built in the first step.

Install and create a sample workbook

Bash
pip install openpyxl

Build a tiny sales sheet with a header row and a few data rows so the styling steps below have real cells to target:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"

ws.append(["Region", "Rep", "Units", "Revenue"])
for row in [
    ["North", "Alvarez", 120, 24000],
    ["South", "Boateng", 95, 19000],
    ["East",  "Chen",    140, 28000],
    ["West",  "Dubois",  80,  16000],
]:
    ws.append(row)

wb.save("styled_report.xlsx")
print("Sample workbook created")

Font: bold, color, and size

Font controls typeface, weight, size, and color. Color is an 8-digit ARGB hex string (or a 6-digit RGB string, which openpyxl pads to full opacity). Assign a fresh Font to a cell's .font attribute — fonts are immutable, so you replace them rather than mutate them:

Python
from openpyxl import load_workbook
from openpyxl.styles import Font

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

ws["A1"].font = Font(name="Calibri", size=12, bold=True, color="FFFFFF")
ws["D2"].font = Font(bold=True, italic=True, color="2E7D32")  # green revenue

wb.save("styled_report.xlsx")
print("Fonts applied")

PatternFill: cell backgrounds

PatternFill paints a cell's background. You must pass a fill_type (use "solid"); without it the fill renders invisibly. fgColor is the visible color for a solid fill:

Python
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

header_fill = PatternFill(fill_type="solid", fgColor="4472C4")  # blue bar
for cell in ws[1]:
    cell.fill = header_fill

wb.save("styled_report.xlsx")
print("Header fill applied")

Border and Side: ruled cells

A Border is built from four Side objects — left, right, top, bottom. Each Side takes a style (such as "thin", "medium", or "double") and a color. Define one Side and reuse it on all four edges for a clean box:

Python
from openpyxl import load_workbook
from openpyxl.styles import Border, Side

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

thin = Side(style="thin", color="999999")
box = Border(left=thin, right=thin, top=thin, bottom=thin)

for row in ws["A1:D5"]:
    for cell in row:
        cell.border = box

wb.save("styled_report.xlsx")
print("Borders applied")

Alignment: wrapping and centering

Alignment controls horizontal and vertical placement plus text wrapping. Set wrap_text=True to let long values flow onto multiple lines instead of spilling over. Center the header row both ways:

Python
from openpyxl import load_workbook
from openpyxl.styles import Alignment

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

center = Alignment(horizontal="center", vertical="center", wrap_text=True)
for cell in ws[1]:
    cell.alignment = center

# Right-align the numeric columns for a tidy ledger look
right = Alignment(horizontal="right")
for row in ws["C2:D5"]:
    for cell in row:
        cell.alignment = right

wb.save("styled_report.xlsx")
print("Alignment applied")

Style a header row in one loop

In a real generator the header is styled in a single pass that combines font, fill, and alignment. Build the style objects once, outside the loop, then apply them to every cell in row 1:

Python
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

head_font = Font(bold=True, color="FFFFFF", size=12)
head_fill = PatternFill("solid", fgColor="4472C4")
head_align = Alignment(horizontal="center", vertical="center")

for cell in ws[1]:
    cell.font = head_font
    cell.fill = head_fill
    cell.alignment = head_align

wb.save("styled_report.xlsx")
print("Header row styled in a loop")

Merge cells for a title bar

ws.merge_cells() joins a rectangular range into one logical cell. Set the value and styling on the top-left cell of the range — the others are emptied. Insert a row first so the title sits above the table:

Python
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

ws.insert_rows(1)                      # push the table down one row
ws.merge_cells("A1:D1")               # title spans all four columns
title = ws["A1"]
title.value = "Q3 Regional Sales"
title.font = Font(bold=True, size=14, color="1F2937")
title.alignment = Alignment(horizontal="center", vertical="center")

wb.save("styled_report.xlsx")
print("Title bar merged")

Reuse a NamedStyle

When the same combination of font, fill, border, and alignment recurs, define a NamedStyle once, register it on the workbook, then apply it by name. Excel also exposes named styles to the user, and reusing one keeps the file smaller than repeating inline styles:

Python
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle, Font, PatternFill, Alignment

wb = load_workbook("styled_report.xlsx")
ws = wb["Sales"]

header_style = NamedStyle(name="report_header")
header_style.font = Font(bold=True, color="FFFFFF")
header_style.fill = PatternFill("solid", fgColor="305496")
header_style.alignment = Alignment(horizontal="center")

if "report_header" not in wb.named_styles:
    wb.add_named_style(header_style)

for cell in ws[2]:                     # the column-header row (row 1 is the title)
    cell.style = "report_header"

wb.save("styled_report.xlsx")
print("NamedStyle applied")

Why these styles survive

openpyxl edits the workbook XML directly, so every style you set is written into the file and reopens exactly as saved. That is the key difference from pandas.to_excel(), which writes plain unstyled cells (beyond a couple of header options) and discards any formatting you might expect. If you generate the data with pandas and need styling, write the values first, then reopen the file with openpyxl and apply styles — or skip pandas and build the sheet with openpyxl from the start.

For sizing and navigation, two companion pages go deeper: Set Column Width and Row Height in openpyxl covers ws.column_dimensions[...].width and row heights, and Freeze the Header Row in Excel with openpyxl covers ws.freeze_panes so the header stays visible while scrolling.

Frequently asked questions

Why does my PatternFill show up as blank? You almost certainly omitted fill_type. A PatternFill with no type renders nothing. Use PatternFill("solid", fgColor="4472C4") or PatternFill(fill_type="solid", fgColor="4472C4").

Can I modify just one attribute of an existing Font? No — Font, Fill, Border, and Alignment are immutable. Build a new object with the attributes you want and assign it. To carry over existing values, read them off the old object first: Font(name=cell.font.name, bold=True).

Why did my value disappear after merging cells? Only the top-left cell of a merged range keeps its value; the rest are cleared. Set the value and styling on that top-left cell, and merge before writing if order matters.

Do styles applied with openpyxl survive a round-trip through pandas? No. pandas.to_excel() writes plain cells and ignores prior formatting. Apply openpyxl styles as the last step, after any pandas write, so nothing overwrites them.

What color format does openpyxl expect? An ARGB hex string. Pass 8 digits ("FF4472C4") for explicit opacity, or 6 digits ("4472C4") and openpyxl treats it as fully opaque.

Conclusion

Styling in openpyxl comes down to four immutable objects — Font, PatternFill, Border/Side, and Alignment — assigned to a cell's matching attribute. Build them once outside your loops, apply them across rows or ranges, and reach for NamedStyle when a combination repeats. Because openpyxl writes straight to the XML, the result is exactly what reopens in Excel, with none of the formatting loss you get from a plain pandas export.

Where to go next