Write a MultiIndex DataFrame to Excel with pandas

A pivot table, a groupby over two keys, a crosstab with margins — the most useful summaries in pandas come out with a MultiIndex on the rows, the columns or both. Writing one to Excel mostly just works, but the result has three surprises: merged label cells, an extra near-empty row between the header and the data, and an error the moment you pass index=False. This guide, part of Writing DataFrames to Excel with Pandas, explains what pandas writes, how to choose between a presentation layout and a filterable one, and how to read the sheet back without losing the hierarchy.

Prerequisites

pip install pandas openpyxl

A pivot with two levels on each axis makes a good test case:

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "North", "South", "South"],
    "quarter": ["Q1", "Q2", "Q1", "Q2"],
    "product": ["A", "B", "A", "B"],
    "units": [1, 2, 3, 4],
    "revenue": [10.0, 20.0, 30.0, 40.0],
})
pivot = sales.pivot_table(index=["region", "quarter"], columns="product",
                          values=["revenue", "units"], aggfunc="sum")
print(pivot)

Step 1 — The default write: merged and readable

With no options, to_excel writes every level, merges repeated outer labels on both axes, and centres them — a layout that reads like a report:

from openpyxl import load_workbook

pivot.to_excel("pivot_default.xlsx", sheet_name="Pivot")

ws = load_workbook("pivot_default.xlsx")["Pivot"]
for row in ws.iter_rows(max_row=4, values_only=True):
    print(row)
print(sorted(str(r) for r in ws.merged_cells.ranges))
# ['A4:A5', 'A6:A7', 'C1:D1', 'E1:F1']

The printout shows the structure from the diagram: two header rows for the two column levels, a third row holding the index names region and quarter, then data. Row three exists because the index levels have names and pandas has nowhere else to put them. For a report someone reads, that is fine. For a sheet someone works with, it is a problem: Excel's AutoFilter and tables expect exactly one header row directly above the data, and merged cells break sorting.

Step 2 — A filterable layout: repeat instead of merge

merge_cells=False repeats outer labels instead of merging them. Every data row then carries its full key, so sorting and filtering keep rows intact:

pivot.to_excel("pivot_repeated.xlsx", sheet_name="Pivot", merge_cells=False)

ws = load_workbook("pivot_repeated.xlsx")["Pivot"]
for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)

The top column labels now read revenue, revenue, units, units across and the region is written on every row. The index-name row is still there, though — repeating labels does not remove it. To get a true single-row header you have to flatten.

Step 3 — Flatten for filters, tables and analysis

Flattening means two things: join the column levels into single strings, and turn the index levels into ordinary columns with reset_index(). The join rule is yours to choose; revenue_A is compact, Revenue (A) reads better in a report.

flat = pivot.copy()
flat.columns = [f"{measure}_{product}" for measure, product in flat.columns]
flat = flat.reset_index()
print(flat.columns.tolist())
# ['region', 'quarter', 'revenue_A', 'revenue_B', 'units_A', 'units_B']

flat.to_excel("pivot_flat.xlsx", sheet_name="Pivot", index=False)

The result has one header row, no merged cells and no index-name row, so it can become an Excel table with filter buttons straight away — see Create an Excel Table with Python. Flattening also avoids the error in the next step.

Step 4 — The index=False error, and what to do about it

Dropping the index is the usual way to tidy a plain export, so people try it here too:

try:
    pivot.to_excel("pivot_noindex.xlsx", index=False)
except NotImplementedError as exc:
    print(exc)
# Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.

pandas needs the index columns to anchor the multi-row column header, so it refuses. The fix depends on what you wanted from index=False. If you wanted the row labels as normal columns, call reset_index() first and keep index=False. If you wanted no row labels at all, flatten the columns as in Step 3. A MultiIndex on the rows only, with ordinary columns, writes fine with index=False — the error is specific to hierarchical columns.

Step 5 — Read the hierarchy back

A sheet written with the default layout round-trips exactly, as long as you tell read_excel the shape: how many header rows, and how many leading columns form the index.

back = pd.read_excel("pivot_default.xlsx", sheet_name="Pivot", header=[0, 1], index_col=[0, 1])
print(back.columns.tolist()[:2])     # [('revenue', 'A'), ('revenue', 'B')]
print(back.index.names)              # ['region', 'quarter']
print(back.equals(pivot))            # True

pandas recognises the index-name row, fills merged labels down and across, and rebuilds both MultiIndexes with their names. The flattened file, by contrast, reads back as an ordinary frame; restore the hierarchy with set_index(["region", "quarter"]) and by splitting the joined names — which is why a separator that cannot appear in the labels, such as _ or |, is worth choosing deliberately.

Step 6 — Present a flattened pivot so it still reads as a hierarchy

Flattening trades the visual hierarchy for usability, but you can win most of it back with light formatting: readable column names, a frozen header, number formats per measure, and a blank-looking repeat of the outer key. The last trick keeps the region on every row for filtering while showing it only on the first row of each group, using a conditional format that fades repeated values rather than deleting them:

from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font

readable = pivot.copy()
readable.columns = [f"{measure.title()} ({product})" for measure, product in readable.columns]
readable = readable.reset_index().fillna(0)

with pd.ExcelWriter("pivot_report.xlsx", engine="openpyxl") as writer:
    readable.to_excel(writer, sheet_name="Pivot", index=False)
    ws = writer.sheets["Pivot"]
    ws.freeze_panes = "C2"
    for cell in ws[1]:
        cell.font = Font(bold=True)
    for col in ("C", "D"):
        for cell in ws[col][1:]:
            cell.number_format = "#,##0.00"
    last = ws.max_row
    ws.conditional_formatting.add(
        f"A3:A{last}",
        FormulaRule(formula=["A3=A2"], font=Font(color="FFFFFF")),
    )

print(pd.read_excel("pivot_report.xlsx").columns.tolist()[:4])
# ['region', 'quarter', 'Revenue (A)', 'Revenue (B)']

The repeated region names are still in the cells — filters, lookups and copy-paste all see them — but the rule paints them white when they equal the row above, so the sheet reads like a grouped report. If someone sorts it, the rule re-evaluates and the first row of each new group shows its label again. Freezing at C2 keeps both the header and the two key columns visible while scrolling across a wide pivot.

Common pitfalls

• Missing values in the pivot. Empty combinations appear as blank cells. If the reader needs zeros, pass fill_value=0 to pivot_table rather than filling after the fact.
• Header rows miscounted on read. Two column levels means header=[0, 1]; three means [0, 1, 2]. Get it wrong and the index-name row is read as data.
• Styling merged header cells. Formatting the merged header after writing requires styling the top-left cell of each merged range; the other cells in the range are hidden. When you need designed headers, flatten first and style one row.
• Very wide pivots. A column level with hundreds of values produces hundreds of columns that no one reads. Pivot the long dimension onto the rows instead.

Performance and scale notes

MultiIndex frames write at the same speed as flat ones, but merged cells cost openpyxl and Excel time on very large sheets, and they make every later edit slower. For summary tables — the usual home of a MultiIndex — none of this matters. For detail data, never write a MultiIndex at all: reset the index and let Excel's own pivot table build the hierarchy on demand, as described in Add a Native Excel Pivot Table with Python.

Conclusion

pandas writes MultiIndex rows and columns faithfully: multiple header rows, merged outer labels and a row of index names. Keep that default for read-only reports, use merge_cells=False when people will sort, and flatten with joined column names plus reset_index() when the sheet needs filters, tables or further work. Never combine hierarchical columns with index=False, and read the default layout back with matching header and index_col lists to recover the exact frame.

Frequently asked questions

Why does to_excel raise NotImplementedError for my MultiIndex? pandas cannot write MultiIndex columns without also writing the index, so index=False with hierarchical columns raises NotImplementedError. Either keep the index or flatten the columns into single strings first.

How do I stop pandas merging the index cells? Pass merge_cells=False. The outer labels are then repeated on every row and over every column they cover, which is what you want if people will sort or filter the sheet.

Where does the empty row under the header come from? When the index has names, pandas writes them on their own row between the column headers and the data. It keeps the index names but breaks Excel's filter and table features, which expect a single header row directly above the data.

How do I read a MultiIndex sheet back into pandas? Tell read_excel how many header rows and index columns there are, for example header=0, 1 and index_col=0, 1. pandas then rebuilds both hierarchies, including the index names.

Related

• Up one level: Writing DataFrames to Excel with Pandas — the writer options in context.
• Export a Pandas Pivot Table to Excel (Formatted) — styling the result.
• Create a Pivot Table from Excel with Pandas — building the pivot in the first place.
• Write a DataFrame to a Specific Cell in Excel with pandas — placing the table inside a report layout.