Export a Pandas Pivot Table to Excel (Formatted)

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 guide extends the Creating Pivot Tables from Excel Data workflow, picking up where a raw pivot lands and turning it into a report you can hand to a colleague.

Prerequisites

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:

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:

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

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:

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

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. This is also the approach to reach for when you combine several formatted pivots into one workbook, where the per-cell cost multiplies across sheets.

Format the exported pivot in one pass

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter

wb = load_workbook("pivot.xlsx")
ws = wb["By month"]

header_font, header_fill = Font(bold=True, color="FFFFFF"), PatternFill("solid", start_color="4338CA")
for cell in ws[1]:
    cell.font, cell.fill = header_font, header_fill

for row in range(2, ws.max_row + 1):
    for col in range(2, ws.max_column + 1):
        ws.cell(row=row, column=col).number_format = "#,##0.00"

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 + 3, 10), 34)

ws.freeze_panes = "B2"
wb.save("pivot_formatted.xlsx")

Freezing at B2 rather than A2 is the detail specific to a pivot: the row labels are as important as the header when scrolling right across twelve months of columns, and pinning both keeps every number identifiable. Applying the number format from column two onwards leaves the label column alone, which matters when it holds text that a numeric format would mangle.

A pivot exported without this finishing step is the most common example of a report that is correct and looks unfinished — the numbers are right, and the reader's first impression is of raw output.

Numbers first, formatting last

The order that keeps a formatted export reliable is always the same: write the values with pandas, then reopen with openpyxl and apply the header style, number formats, widths and freeze. Any formatting applied before the write is discarded when the sheet is replaced, and a job that mixes the two produces a file whose appearance depends on the order two functions happened to run in.

Freeze at the corner

On a pivot, both the header row and the label column carry meaning, so freeze_panes = "B2" is nearly always the right setting — it pins both at once. Freezing only the header leaves a reader scrolling right through twelve months of columns with no idea which region each row belongs to, which is the specific readability failure that makes a wide pivot feel unusable.

Ranges come from the data

The habit that keeps a monthly report correct is deriving every range, row count and column position from what was just written rather than from a number typed once. ws.max_row after the write, a header map built from row one, a table reference rebuilt on each run — each of those removes a class of failure that is silent rather than loud. A hardcoded range does not raise when the data outgrows it; it simply stops covering the rows nobody looks at, and the report is wrong in a way that takes months to notice. Deriving costs a line and removes the whole category.

Flatten before writing

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.

Related

• Creating Pivot Tables from Excel Data — the parent guide this workflow belongs to.
• Create a Pivot Table From Excel With Pandas — the pivot fundamentals behind this export.
• Applying Number and Date Formats in Excel — deeper number-format strings for the styling step.
• Building Multi-Sheet Excel Dashboards — combine several formatted pivots into one workbook.