Python Excel AutomationPractical guides for developers
Start learning

Guide

Advanced Data Transformation And CleaningDeep dive

Export a Pandas Pivot Table to Excel (Formatted)

Build a pandas pivot_table, flatten its MultiIndex, export with to_excel, then add a styled header, number formats, and a grand-total row with openpyxl.

To export a pandas pivot table to a clean, formatted Excel file, build the table with pivot_table(), flatten any MultiIndex on the columns and index so it writes as a flat grid, save it with to_excel(), then reopen the file with openpyxl to apply a styled header and number formats. The flattening step is what keeps the output from exporting as messy stacked headers. This page is part of the Creating Pivot Tables from Excel Data cluster.

Prerequisites

Bash
pip install pandas openpyxl

Every block below runs in order against the sample data built first.

Build the pivot table with a grand total

Set margins=True to append a grand-total row and column. margins_name controls the label so it reads cleanly:

Python
import pandas as pd

df = pd.DataFrame({
    "Region": ["East", "East", "West", "West", "East", "West"],
    "Product": ["Widget", "Gadget", "Widget", "Gadget", "Widget", "Widget"],
    "Quarter": ["Q1", "Q1", "Q1", "Q2", "Q2", "Q2"],
    "Sales": [1200.5, 800.0, 950.25, 1100.0, 1350.75, 700.0],
})

pivot = pd.pivot_table(
    df,
    index=["Region", "Product"],
    columns="Quarter",
    values="Sales",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="Total",
)
print(pivot)

This produces a MultiIndex on the rows (Region, Product) and named columns (Q1, Q2, Total). Written as-is, the row index spans two header levels and the column index name leaks into the corner cell.

Flatten the index and columns

reset_index() turns the row MultiIndex into regular columns. If the columns are also a MultiIndex (which happens when you pass multiple values or stacked columns), flatten them with to_flat_index() and join the tuples into readable strings:

Python
flat = pivot.reset_index()

# Flatten MultiIndex columns if present
if isinstance(flat.columns, pd.MultiIndex):
    flat.columns = ["_".join(str(c) for c in col if c != "").strip("_")
                    for col in flat.columns.to_flat_index()]

# Drop the leftover "Quarter" columns-axis name
flat.columns.name = None
print(flat)

For this example the columns are single-level, so reset_index() plus clearing columns.name is enough. Keep the MultiIndex branch in your pipeline so it stays correct when the shape changes.

Write the flattened table to Excel

Python
flat.to_excel("pivot_report.xlsx", index=False, sheet_name="Summary",
              engine="openpyxl")
print("Wrote pivot_report.xlsx")

Pass index=False because the index is already a real column after reset_index() — otherwise you get a duplicate, unnamed first column.

Style the header and apply number formats

pandas writes values but not formatting. Reopen the saved file with openpyxl to bold the header, fill it, and apply a currency number format to the numeric columns:

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

wb = load_workbook("pivot_report.xlsx")
ws = wb["Summary"]

header_fill = PatternFill("solid", fgColor="1F4E78")
header_font = Font(bold=True, color="FFFFFF")

for cell in ws[1]:
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center")

# Currency format on every numeric column (skip the two text key columns)
for row in ws.iter_rows(min_row=2, min_col=3):
    for cell in row:
        cell.number_format = '#,##0.00'

# Bold the grand-total row (last row)
for cell in ws[ws.max_row]:
    cell.font = Font(bold=True)

ws.column_dimensions["A"].width = 12
ws.column_dimensions["B"].width = 12

wb.save("pivot_report.xlsx")
print(f"Formatted {ws.max_row - 1} data rows")

number_format is a cell-level string; '#,##0.00' gives thousands separators and two decimals. Adjusting min_col skips the text key columns so only the numbers get the currency format.

Common pitfalls

SymptomCauseFix
Stacked, half-empty header rows in ExcelMultiIndex columns written directlyFlatten with to_flat_index() and join the tuples
Empty extra column on the leftRow MultiIndex written as the indexreset_index(), then to_excel(index=False)
Stray Quarter label in a corner cellColumns-axis name survives the writeflat.columns.name = None
Grand total labeled AllDefault margins_namePass margins_name="Total"
Formatting gone after editing in pandasRe-reading with read_excel strips stylesApply styles last, with openpyxl, and don't round-trip
Index name lost after flattenreset_index consumes index names into columnsRead them from the new column headers, not df.index.name

The last row matters most: any time you read a formatted file back into pandas and re-write it, all openpyxl styling is discarded. Treat the openpyxl pass as the final step and never funnel a styled workbook back through pd.read_excel/to_excel.

Performance and scale note

The pivot and flatten are vectorized and trivial even for large source frames; the openpyxl styling loop is the bottleneck because it touches each cell in Python. For pivots up to a few thousand rows this is instant. If you are formatting tens of thousands of rows, prefer the xlsxwriter engine with pd.ExcelWriter(path, engine="xlsxwriter") and apply a single set_column(..., cell_format) per column instead of looping cells — column-level formats are far faster than per-cell assignment.

Frequently asked questions

Why do my pivot columns export as messy stacked headers? Because pivot_table produced a MultiIndex on the columns and to_excel writes each level as its own header row. Flatten with df.columns = df.columns.to_flat_index() and join the tuples into single strings before writing.

How do I add a grand-total row? Pass margins=True to pivot_table. Use margins_name="Total" to rename the default All label on both the total row and column.

Why is there a blank extra column on the left of my sheet? The row MultiIndex was written as the spreadsheet index. Call reset_index() to turn it into real columns, then to_excel(..., index=False).

Can I format the pivot without reopening the file? Yes — write through pd.ExcelWriter with engine="xlsxwriter" and apply formats in the same context manager. The openpyxl reopen approach is simpler when the file already exists.

Why did my formatting disappear? You likely read the styled file back with pd.read_excel and re-wrote it. pandas does not preserve cell styles, so make the openpyxl/xlsxwriter formatting your final step.

Conclusion

A clean formatted pivot export is four steps: pivot with margins=True, flatten the MultiIndex with reset_index and to_flat_index, write with index=False, then style once with openpyxl. Keeping the flatten and style steps separate from any re-read is what guarantees a tidy, formatted result.

Where to go next