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 guide 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. Styling handles the look of a cell; the value inside it is shaped separately by number and date formats, which this guide leaves untouched. Each block runs in order against a sample workbook built in the first step.

The four style objects applied to a single Excel header cell A sample header cell is labelled with the four openpyxl style objects that shape it: Font, PatternFill, Border, and Alignment. Revenue Font bold, white, size PatternFill solid background Border thin sides Alignment center, center

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. A PatternFill is a static, unconditional colour — when you want the colour to change based on the value in the cell, reach for conditional formatting instead:

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")

Zebra-stripe the data rows

Alternating row shading (banding) makes a wide table far easier to read across. Because a PatternFill is a plain object, you can pick one of two fills per row using the row index — enumerate the data rows and shade the even ones with a light tint:

Python
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

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

band = PatternFill("solid", fgColor="F2F5FB")  # very light blue-grey

# Rows 2..5 are data; shade every other one
for i, row in enumerate(ws.iter_rows(min_row=2, max_row=5)):
    if i % 2 == 1:
        for cell in row:
            cell.fill = band

wb.save("styled_report.xlsx")
print("Zebra striping applied")

Keep the band colour light so black text stays comfortably readable on it — a heavy fill fails the same contrast test the header's white-on-blue passes. Unlike Excel's built-in table banding, this writes a real fill onto each cell, so it survives even if the reader removes the table object.

Zebra striping: a solid-filled header over alternately shaded data rows A four-column sales table. The header row has a solid blue fill with white text. Below it, four data rows alternate between plain and a light band fill, so every second row (South and West) is tinted for readability. Region Rep Units Revenue NorthAlvarez12024,000 SouthBoateng9519,000 EastChen14028,000 WestDubois8016,000 Header: solid fill, white text Even rows (i % 2 == 1): light band fill

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. Once the table looks right, you can turn it into a chart or drop in a company logo to finish the report.

Style objects are shared, and that matters

openpyxl deduplicates styles: two cells given identical formatting share one record in the workbook's style table. Creating a new Font per cell defeats that, inflating the file and slowing every reader:

Reusing style objects versus creating one per cell Defining a handful of Font and PatternFill objects and assigning them to many cells keeps the workbook's style table small. Constructing a new object inside the loop produces thousands of near-identical records and a file that opens slowly. define once, assign many one record per distinct style small file, fast to open easy to change centrally new object per cell thousands of records large file, slow to open impossible to restyle
Python
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

HEADER_FONT = Font(bold=True, color="FFFFFF", size=11)
HEADER_FILL = PatternFill("solid", start_color="4338CA")
CENTRED = Alignment(horizontal="center", vertical="center", wrap_text=True)
THIN = Side(style="thin", color="CDD5E6")
BOX = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

wb = load_workbook("report.xlsx")
ws = wb["Orders"]

for cell in ws[1]:
    cell.font, cell.fill, cell.alignment, cell.border = HEADER_FONT, HEADER_FILL, CENTRED, BOX

for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    for cell in row:
        cell.border = BOX

wb.save("report_styled.xlsx")

Assigning the same object to many cells is safe because openpyxl style objects are immutable — the assignment stores a reference to a shared record rather than a mutable object each cell can change. That is also why cell.font.bold = True does not work: create a new Font (or use copy(cell.font)) instead of mutating the one you were given.

Alignment and wrapping do more than fonts

Most reports that look cramped are suffering from alignment rather than typography. Three settings carry most of the improvement: wrapping long headers so the column can stay narrow, centring short codes so the eye can scan them, and right-aligning numbers so the decimal points line up — which Excel does automatically for real numbers and not at all for numbers stored as text.

Python
from openpyxl.styles import Alignment

for cell in ws[1]:
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.row_dimensions[1].height = 30                 # room for the wrapped text
for row in ws.iter_rows(min_row=2, min_col=3, max_col=4):
    for cell in row:
        cell.alignment = Alignment(horizontal="right")

Setting an explicit row height on a wrapped header is what stops the text being clipped: Excel auto-fits row height when a user types, but not when openpyxl writes, so a wrapped header in a default-height row shows only its first line.

A house style in one function

Formatting spread through a script drifts. Collecting it into one function applied to every sheet keeps a multi-tab workbook coherent and gives you a single place to change when the style does:

Python
def apply_house_style(ws, money=(), percent=(), freeze="A2"):
    for cell in ws[1]:
        cell.font, cell.fill, cell.alignment = HEADER_FONT, HEADER_FILL, CENTRED
    ws.row_dimensions[1].height = 28
    ws.freeze_panes = freeze
    for letter in money:
        for row in range(2, ws.max_row + 1):
            ws[f"{letter}{row}"].number_format = "#,##0.00"
    for letter in percent:
        for row in range(2, ws.max_row + 1):
            ws[f"{letter}{row}"].number_format = "0.0%"
    return ws

Two arguments, one consistent look, and no styling code scattered through the report logic — which is what makes it realistic to restyle every report a team produces when the house colours change.

Column widths are the most-noticed formatting of all

A number shown as #### reads as a broken report, and it is entirely a width problem. Deriving widths from the content takes a few lines and removes the whole class of complaint:

Python
from openpyxl.utils import get_column_letter

def autosize(ws, min_width=9, max_width=42, padding=3):
    for cells in ws.columns:
        letter = get_column_letter(cells[0].column)
        longest = max((len(str(c.value)) for c in cells if c.value is not None), default=0)
        ws.column_dimensions[letter].width = min(max(longest + padding, min_width), max_width)

The padding covers the filter arrow that otherwise overlaps the last characters of a header, and the cap stops one long free-text column pushing everything else off the screen. Because openpyxl has no true auto-fit — Excel computes that at display time from font metrics — character counting is the practical approximation, and it is close enough that nobody notices the difference.

Row heights follow the same logic but only matter where text wraps: a wrapped header in a default-height row shows one line and hides the rest, so set the height explicitly whenever you turn wrapping on.

Merging cells, carefully

Merges are for titles and banners, not for data. A merged range holds its value in the top-left cell only; the rest become read-only, sorting and filtering behave unexpectedly, and any script reading the sheet sees blanks where a person sees text:

Where merging helps and where it hurts Merging across a title row groups a banner and reads well. Merging inside a data region leaves blank cells that break sorting, filtering and every script that reads the sheet. titles and banners a report heading across A1:F1 no data underneath it purely presentational inside the data blanks in the grid breaks sort and filter scripts read empty cells
Python
ws.merge_cells("A1:F1")
ws["A1"] = "Monthly report — March 2026"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
ws.row_dimensions[1].height = 26

Where a grouping label would tempt you to merge down a column, repeat the value on every row instead and use row grouping or an autofilter to give the reader the same visual grouping without damaging the data.

Style the sheet, not the cells

The most maintainable styling code operates on ranges and columns rather than on individual cells: a header row, a money column, a border around the used range. That keeps the workbook's style table small, makes the intent obvious to the next reader of the code, and means a change to the house style is an edit to a handful of constants rather than a search through nested loops.

Consistency beats cleverness

The styling that makes a report feel professional is unremarkable: one header treatment, one money format, sensible widths, a frozen header. Applied identically across every sheet and every report a team produces, that consistency does more than any individual flourish — readers stop noticing the presentation and start reading the numbers, which is the point. Collecting those decisions into a single finishing function, imported by every report, is what makes the consistency survive contact with a deadline.

Fail where the cause is

The most useful place for a check is as close as possible to the thing that can go wrong: the sheet name at the read, the column list before the transform, the row count before the write, the file size before delivery. Each of those turns a confusing downstream error into a message naming the actual problem. Checks placed late still catch the failure, but they describe a symptom — and a symptom three stages from its cause is what makes a simple mistake take an afternoon.

Styling is where reports become maintainable or not

The difference between a report that can be restyled in an afternoon and one that cannot is entirely structural: whether the fonts, fills and formats live in a handful of named constants applied by one function, or are constructed inline wherever a cell happened to need them. The second version works and cannot be changed, because the house style is spread across every report the team owns.

Collecting them costs nothing at the time and is the single highest-leverage refactor available in a growing reporting codebase.

Key takeaways

  • Styling in openpyxl comes down to four immutable objects — Font, PatternFill, Border/Side, and Alignment — each assigned to the cell attribute of the same name.
  • The objects are immutable: to change one attribute you build a new object, carrying over any old values you want to keep.
  • PatternFill needs an explicit fill_type ("solid"); colours are ARGB hex strings, 6 or 8 digits.
  • Build style objects once, outside your loops, then apply them across rows or ranges — including a two-fill trick for zebra striping.
  • Merge a range with ws.merge_cells() for a title bar, and set the value and styling on the top-left cell only.
  • Reach for a NamedStyle when the same combination repeats; it keeps the file smaller and shows up in Excel's own style gallery.
  • Because openpyxl writes straight to the XML, the styled result reopens exactly as saved — with none of the formatting loss a plain pandas export gives you.

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.