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

Write a Formatted Excel Report with xlsxwriter

Build a complete styled report in one pass: a title block, a header row you control, column formats and widths, a totals row, banded rows, an autofilter and frozen panes.

A report generated by a script is judged in the two seconds before anyone reads a number: whether the header stands out, whether money looks like money, whether the columns are wide enough, whether the totals are visible. xlsxwriter is unusually good at all of that, provided you build the sheet in the order it wants — because a cell that has been written cannot be restyled afterwards.

This guide builds a full styled report top to bottom, using the format-object pattern from Building Excel Reports with xlsxwriter. Everything below runs as written, generating its own sample data.

The anatomy of the finished sheet Row one holds a merged title with the reporting period. Row two is a generated-at line in small grey type. Row four is the header, frozen so it stays visible, and carrying the autofilter. Rows five onward are the data, banded and formatted by column. The last row is a bold totals line built from SUBTOTAL formulas. Regional sales — July 2026 merged A1:E1 Generated 2026-08-10 06:00 · 1,284 rows row 2 Region Orders Amount Share Updated frozen + filter South 412 274.75 55.0% 2026-07-30 North 388 150.25 30.0% 2026-07-28 East 484 75.00 15.0% 2026-07-29 Total 1,284 500.00 100.0% SUBTOTAL

Prerequisites

Bash
pip install xlsxwriter pandas

xlsxwriter has no dependencies and needs no Excel installation — it writes the .xlsx format directly, so this runs on a headless server or a CI runner.

Step 1: Define every format once

Formats are created on the workbook and reused. Building them in a dictionary at the top keeps the writing code readable and guarantees each style exists exactly once in the file:

Python
import xlsxwriter
import pandas as pd

data = pd.DataFrame({
    "region": ["South", "North", "East", "West"],
    "orders": [412, 388, 484, 301],
    "amount": [274.75, 150.25, 75.0, 190.4],
    "updated": pd.to_datetime(["2026-07-30", "2026-07-28",
                               "2026-07-29", "2026-07-31"]),
})
data["share"] = data["amount"] / data["amount"].sum()
data = data[["region", "orders", "amount", "share", "updated"]]


def make_formats(wb):
    return {
        "title":  wb.add_format({"bold": True, "font_size": 14,
                                 "font_color": "white", "bg_color": "#5B5CF0",
                                 "align": "left", "valign": "vcenter",
                                 "indent": 1}),
        "note":   wb.add_format({"italic": True, "font_size": 9,
                                 "font_color": "#5B6780", "indent": 1}),
        "header": wb.add_format({"bold": True, "font_color": "white",
                                 "bg_color": "#1F4E78", "align": "center",
                                 "valign": "vcenter", "border": 1,
                                 "text_wrap": True}),
        "text":   wb.add_format({"border": 1}),
        "int":    wb.add_format({"num_format": "#,##0", "border": 1}),
        "money":  wb.add_format({"num_format": '#,##0.00', "border": 1}),
        "pct":    wb.add_format({"num_format": "0.0%", "border": 1}),
        "date":   wb.add_format({"num_format": "yyyy-mm-dd", "border": 1}),
        "band":   wb.add_format({"bg_color": "#F0F4FF", "border": 1}),
        "total_text":  wb.add_format({"bold": True, "top": 2, "bg_color": "#D9F4F1"}),
        "total_int":   wb.add_format({"bold": True, "top": 2, "bg_color": "#D9F4F1",
                                      "num_format": "#,##0"}),
        "total_money": wb.add_format({"bold": True, "top": 2, "bg_color": "#D9F4F1",
                                      "num_format": '#,##0.00'}),
        "total_pct":   wb.add_format({"bold": True, "top": 2, "bg_color": "#D9F4F1",
                                      "num_format": "0.0%"}),
    }

The duplication in the total formats is unavoidable and worth accepting: a format object carries every attribute at once, so "bold, top border, shaded, and a currency code" is one object rather than four composable ones. This is the main structural difference from openpyxl's separate Font, Fill and Border objects.

Step 2: Write the title block and the header

The title is merged across the table's width, and the generated-at line goes directly beneath it:

Python
HEADERS = ["Region", "Orders", "Amount", "Share", "Updated"]
FIRST_DATA_ROW = 3                        # zero-based: title, note, blank, header at 3

wb = xlsxwriter.Workbook("regional-july.xlsx")
fmt = make_formats(wb)
ws = wb.add_worksheet("Summary")

ws.merge_range(0, 0, 0, len(HEADERS) - 1, "Regional sales — July 2026", fmt["title"])
ws.set_row(0, 26)
ws.write(1, 0, f"Generated {pd.Timestamp.now():%Y-%m-%d %H:%M} · "
               f"{len(data):,} rows", fmt["note"])

for col, name in enumerate(HEADERS):
    ws.write(FIRST_DATA_ROW, col, name, fmt["header"])
ws.set_row(FIRST_DATA_ROW, 30)

merge_range needs the format passed in the same call — a merged range written without one loses the alignment and fill. Keeping FIRST_DATA_ROW as a named constant rather than a literal 3 is what stops the autofilter, the freeze pane and the totals formula drifting out of alignment when you later add a line to the title block.

Step 3: Write the data with banded rows

Alternate row shading is applied at write time by choosing the format per row:

Python
COLUMN_FORMATS = ["text", "int", "money", "pct", "date"]

for i, row in enumerate(data.itertuples(index=False), start=0):
    excel_row = FIRST_DATA_ROW + 1 + i
    banded = i % 2 == 1
    for col, (value, kind) in enumerate(zip(row, COLUMN_FORMATS)):
        style = fmt["band"] if (banded and kind == "text") else fmt[kind]
        if kind == "date":
            ws.write_datetime(excel_row, col, value, fmt["date"])
        elif kind == "text":
            ws.write_string(excel_row, col, str(value), style)
        else:
            ws.write_number(excel_row, col, float(value), fmt[kind])

Using the explicit write_string, write_number and write_datetime methods instead of the generic write removes xlsxwriter's type guessing, which matters for two cases in particular: an identifier like 00123 that write would store as the number 123, and a date that arrives as a string and would be stored as text no reader can sort.

If you want full banding rather than banding on the label column only, build a second set of formats with bg_color set — every combination has to exist as its own format object, which is why an Excel table (below) is often the better answer.

Step 4: Add totals that survive filtering

A totals row written as a plain number goes stale the moment a reader filters the table. SUBTOTAL recalculates over visible rows only:

Python
last_data_row = FIRST_DATA_ROW + len(data)          # zero-based index of the last row
total_row = last_data_row + 1
first, last = FIRST_DATA_ROW + 2, last_data_row + 1  # 1-based for the formula text

ws.write(total_row, 0, "Total", fmt["total_text"])
ws.write_formula(total_row, 1, f"=SUBTOTAL(109,B{first}:B{last})",
                 fmt["total_int"], int(data["orders"].sum()))
ws.write_formula(total_row, 2, f"=SUBTOTAL(109,C{first}:C{last})",
                 fmt["total_money"], float(data["amount"].sum()))
ws.write_formula(total_row, 3, f"=SUBTOTAL(109,D{first}:D{last})",
                 fmt["total_pct"], float(data["share"].sum()))
ws.write(total_row, 4, "", fmt["total_text"])
What each kind of total does when the reader filters to one region A number computed in Python and written as a value never changes, so after filtering it contradicts the visible rows. A plain SUM recalculates but still counts the hidden rows. SUBTOTAL with function 109 counts only what is visible, so the total always matches the rows on screen. Filtered to South: visible rows total 274.75 a written value 500.00 frozen at generation time contradicts the screen =SUM(C2:C5) 500.00 recalculates, but counts the hidden rows too =SUBTOTAL(109,C2:C5) 274.75 counts visible rows only matches what is on screen

Function 109 is SUM ignoring hidden rows; 9 would include them. The fourth argument to write_formula is the cached result: xlsxwriter does not evaluate formulas, so without it any tool that reads the file without opening Excel — including openpyxl in data_only mode and a downstream pandas read — sees None where the total should be.

Step 5: Widths, filter and panes

The finishing pass is three calls, and the width calculation is the only one with any logic in it:

Python
from itertools import chain


def column_width(series, heading, pad=3, cap=42):
    longest = max(chain([len(str(heading))],
                        (len(str(v)) for v in series)))
    return min(longest + pad, cap)


for col, name in enumerate(data.columns):
    width = column_width(data[name], HEADERS[col])
    default = {"region": None, "orders": fmt["int"], "amount": fmt["money"],
               "share": fmt["pct"], "updated": fmt["date"]}[name]
    ws.set_column(col, col, width, default)

ws.autofilter(FIRST_DATA_ROW, 0, last_data_row, len(HEADERS) - 1)
ws.freeze_panes(FIRST_DATA_ROW + 1, 0)
ws.set_landscape()
ws.fit_to_pages(1, 0)                 # one page wide, any number tall
wb.close()

There is no autofit in the .xlsx format — Excel computes widths when a user double-clicks a column border, and a generated file has to state them. The cap is what stops one long free-text value making a column unusably wide; the pad accounts for the header's centre alignment and the filter arrow.

fit_to_pages(1, 0) is worth setting even for a file nobody plans to print, because "print to PDF" is how these reports are circulated to people who never open Excel. The page-setup details are covered in Exporting Excel Reports to PDF.

The order the sheet has to be built in Formats are created first, then column widths and defaults, then the title block, the header row and the data, then the totals row, and finally the sheet-level settings — autofilter, freeze panes and page setup. Because a written cell cannot be restyled, anything decided late has to be applied through set_column or written with the right format the first time. A written cell cannot be restyled — so the order is the design 1. formats created once 2. set_column width + default 3. title + header merged, then row 4 4. data + totals typed writes 5. sheet filter · panes The two decisions that cannot be deferred Column widths, because measuring the data after writing it is too late to apply the result to the cells. Header formatting, because pandas will write plain header cells if you let it — so write the frame with header=False.

Step 6: The pandas hand-off

When the data is already a DataFrame, let pandas write the body and take over for everything else:

Python
with pd.ExcelWriter("regional-july.xlsx", engine="xlsxwriter",
                    datetime_format="yyyy-mm-dd") as writer:
    data.to_excel(writer, sheet_name="Summary", index=False,
                  header=False, startrow=FIRST_DATA_ROW + 1)
    wb, ws = writer.book, writer.sheets["Summary"]
    fmt = make_formats(wb)
    # ... title, header row, widths, totals, filter and panes exactly as above

header=False plus startrow is the whole trick: pandas writes only the values, and every styled cell — title, header, totals — is written by you with a format you chose. Trying it the other way round leaves the header in pandas' default bold and no way to change it.

Common pitfalls and gotchas

SymptomCauseFix
The file is empty or missingclose() never ranUse with xlsxwriter.Workbook(path) as wb:
Header still plain after stylingpandas wrote those cells firstheader=False, write the header yourself
A merged title lost its fillFormat not passed to merge_rangePass the format in the same call
Leading zeros disappearedwrite guessed a numberUse write_string
Dates appear as serial numbersWritten without a date formatwrite_datetime with a num_format format
Totals read as None elsewhereNo cached value on the formulaPass the computed result as the 4th argument
Total wrong after filteringPlain SUM includes hidden rowsSUBTOTAL(109, ...)
File is huge for its row countA format created inside the loopCreate formats once, outside

Performance and scale notes

Format reuse is the dominant cost at scale. A workbook that creates a format per cell can be several times larger and noticeably slower to write than one reusing a dozen objects, because every distinct format becomes a style record in the file. set_column is the cheapest formatting available — one record for a whole column — so push as much as possible there and reserve per-cell formats for genuine exceptions.

Beyond about half a million rows, switch the workbook to constant_memory mode and accept the strict write order; Write a Million Rows to Excel with xlsxwriter covers what that changes. Below that, the default mode is faster because it avoids the temporary files.

Conclusion

Build the sheet in the order xlsxwriter wants and the report comes out right the first time: formats defined once, column widths and defaults set before any data is written, a title block and a header row you control, typed writes for the data, SUBTOTAL formulas with cached values for the totals, and the sheet-level settings last. When the data starts as a DataFrame, write it headerless with startrow and keep every styled cell in your own hands.

Frequently asked questions

Why does my header keep the plain pandas styling? pandas wrote those cells first, and xlsxwriter cannot restyle a written cell. Write the frame with header=False and startrow=1, then write your own header row with the format you want.

How do I autofit column widths? There is no autofit in the file format. Measure the longest string in each column and pass that length plus a small pad to set_column, with a cap so one long comment cannot push a column off the screen.

Should the total row be a formula or a computed value? A formula, written with write_formula, so it stays correct if a reader filters or edits rows. Include the computed result as the optional value argument so tools that never open Excel still see a number.

Can I apply a format to a cell after writing it? No. Decide the format at write time, or set a default for the whole column with set_column before the data is written.

Up to the parent guide:

Related guides: