Python Doc & Data Automation

Fix Pivot Table MultiIndex Columns in Excel

Writing a pivot table with two measures or two column dimensions to Excel fails outright:

NotImplementedError: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.

Or, with index=True, it succeeds and produces a sheet readers cannot use: two stacked header rows with merged cells, the index name region sitting alone on a third row, and a blank row between the headers and the data. Excel's filter and sort buttons land on the wrong row, and anyone who loads the file back with pd.read_excel gets Unnamed: 1 columns.

Root Cause

pivot_table with several values, several columns levels, or several aggfunc entries returns a DataFrame whose columns are a MultiIndex — each column label is a tuple such as ("revenue", "Online"). Excel has no concept of hierarchical column labels, so pandas emulates them with one header row per level, merges repeated labels across cells, and writes the index names on an extra row so they are not lost. Without the index, pandas has no layout that keeps both the tuple structure and the row alignment, so it refuses. Neither output is what an Excel reader wants: a single header row whose cells are unique, readable strings. The fix belongs in pandas, before writing — turn each tuple into one string and move the index into ordinary columns.

Minimal Diagnostic

Confirm the column type and see exactly which tuples you are dealing with, including empty levels that margins and aggfunc lists create.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

sales = pd.DataFrame({
    "region": ["EMEA", "EMEA", "APAC", "APAC", "LATAM"],
    "channel": ["Online", "Retail", "Online", "Retail", "Online"],
    "units": [120, 40, 75, 60, 30],
    "revenue": [9600.0, 3900.0, 6100.0, 5200.0, 2250.0],
})

pivot = pd.pivot_table(sales, index="region", columns="channel",
                       values=["units", "revenue"], aggfunc="sum",
                       fill_value=0, margins=True, margins_name="Total")

print(type(pivot.columns).__name__, pivot.columns.nlevels, "levels")
print(pivot.columns.names)
for col in pivot.columns[:6]:
    print(col)

try:
    pivot.to_excel(Path("out/pivot-broken.xlsx"), index=False)
except NotImplementedError as exc:
    print(f"to_excel failed: {exc}")

MultiIndex 2 levels
[None, 'channel']
('revenue', 'Online')
('revenue', 'Retail')
('revenue', 'Total')
('units', 'Online')
('units', 'Retail')
('units', 'Total')
to_excel failed: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.

The first level is the measure (revenue, units), the second is the channel. The None name on level 0 is why a blank cell appears above the index name when writing with the index.

MultiIndex headers versus flattened headers in Excel The left panel shows the sheet pandas writes with index True, with a merged revenue and units header row, a channel row below it, a row holding only the index name region, and data starting on row four. The right panel shows the flattened version with one header row reading region, revenue Online, revenue Retail, revenue Total, units Online and so on, and data starting on row two where filters and sorting work. Written as MultiIndex row 1 | | revenue ....... | row 2 |channel| Online | Retail | row 3 |region | | | row 4 |APAC | 6100 | 5200 | filters land on row 1 or 2 Flattened first row 1 |region|revenue Online|... row 2 |APAC | 6100|... row 3 |EMEA | 9600|... row 4 |LATAM | 2250|... one header row, filters work

Fix: Flatten Column Tuples and Reset the Index

Join each tuple into a single label, clear the column level names, and turn the index into a column. Every changed line carries a comment.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

DEST = Path("out/pivot-flat.xlsx")

def flatten_pivot(table: pd.DataFrame, sep: str = " ", order: str = "measure-first") -> pd.DataFrame:
    """Return a copy with single-level string columns and the index as ordinary columns."""
    out = table.copy()
    if isinstance(out.columns, pd.MultiIndex):
        labels = []
        for col in out.columns.to_flat_index():                  # changed: tuples, one per column
            parts = [str(p) for p in col if p not in ("", None)]  # changed: drop empty levels
            if order == "dimension-first":
                parts = parts[1:] + parts[:1]
            labels.append(sep.join(parts))
        out.columns = labels                                     # changed: plain strings
    out.columns.name = None                                      # changed: no stray header cell
    out = out.reset_index()                                      # changed: index becomes a column
    duplicated = out.columns[out.columns.duplicated()].tolist()
    if duplicated:
        raise ValueError(f"flattening produced duplicate headers: {duplicated}")
    return out

if __name__ == "__main__":
    flat = flatten_pivot(pivot)                                  # the pivot from the diagnostic
    DEST.parent.mkdir(parents=True, exist_ok=True)
    try:
        flat.to_excel(DEST, sheet_name="Summary", index=False)   # changed: index=False now works
    except PermissionError:
        raise SystemExit(f"{DEST} is open in Excel; close it and rerun")
    print(flat.columns.tolist())

['region', 'revenue Online', 'revenue Retail', 'revenue Total', 'units Online', 'units Retail', 'units Total']

The duplicate check guards against a subtle case: two different tuples can flatten to the same string, for example when a dimension value happens to equal a measure name. Excel accepts duplicate headers, but anyone reading the file back with pandas gets revenue.1 columns and misattributes values.

Choose the label order your readers scan by. order="dimension-first" gives Online revenue, Online units, which reads better when the channel is the primary grouping; pair it with pivot.swaplevel(axis=1).sort_index(axis=1) so the columns are also physically grouped by channel.

Variant Fix 1: Multiple Aggregation Functions

aggfunc=["sum", "mean"] adds a third level: function, measure, dimension. Flattening works the same way, but names become long. Rename the functions to reader-friendly words before joining:

# pip install "pandas>=2.2"
import pandas as pd

FUNC_LABELS = {"sum": "Total", "mean": "Avg", "count": "Count"}

def flatten_with_funcs(table: pd.DataFrame, sep: str = " ") -> pd.DataFrame:
    out = table.copy()
    if isinstance(out.columns, pd.MultiIndex):
        out.columns = [
            sep.join(FUNC_LABELS.get(str(p), str(p)) for p in col if p not in ("", None))
            for col in out.columns.to_flat_index()
        ]
    out.columns.name = None
    return out.reset_index()

multi_func = pd.pivot_table(sales, index="region", columns="channel", values="revenue",
                            aggfunc=["sum", "mean"], fill_value=0)
print(flatten_with_funcs(multi_func).columns.tolist())
# ['region', 'Total revenue Online', 'Total revenue Retail', 'Avg revenue Online', 'Avg revenue Retail']

When a summary needs different functions for different measures, named aggregations with groupby().agg(total_revenue=("revenue", "sum"), avg_units=("units", "mean")) avoid the MultiIndex entirely and are usually easier to read, as shown in Building Pivot Tables and Summaries for Excel.

How column levels are created One measure and one column dimension produce a single level such as Online. Adding a second measure in values adds a measure level, producing labels such as revenue Online. Adding a list of aggregation functions adds a function level, producing Total revenue Online after renaming. Adding a second columns dimension adds another level, such as revenue Online 2026-09. Each added level makes the flattened label longer, so choose the fewest levels readers need. values='revenue', columns='channel' single level: Online, Retail — no flattening needed values=['revenue','units'] + measure level: revenue Online, units Retail aggfunc=['sum','mean'] + function level: Total revenue Online, Avg units Retail columns=['channel','month'] + second dimension: revenue Online 2026-09 — often too wide

Variant Fix 2: Keeping Grouped Headers for Presentation

Sometimes a two-row header is genuinely wanted — a board pack where Revenue spans its channel columns. Write the flattened frame for the data and draw the upper header row yourself, so the data region still starts on a single, filterable header row:

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

def write_grouped_header(table: pd.DataFrame, dest: Path) -> None:
    flat = flatten_pivot(table)                                   # from the fix
    groups = [str(c[0]) for c in table.columns.to_flat_index()]  # measure per data column
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        flat.to_excel(writer, sheet_name="Summary", index=False, startrow=1)
        book, ws = writer.book, writer.sheets["Summary"]
        banner = book.add_format({"bold": True, "align": "center", "bottom": 1})
        start = 1                                                 # column 0 is the region
        while start <= len(groups):
            label = groups[start - 1]
            end = start
            while end < len(groups) and groups[end] == label:
                end += 1
            if end > start:
                ws.merge_range(0, start, 0, end, label.title(), banner)
            else:
                ws.write(0, start, label.title(), banner)
            start = end + 1
        ws.autofilter(1, 0, len(flat) + 1, len(flat.columns) - 1)   # filter on the real header row
        ws.freeze_panes(2, 1)

The merged banner is decoration on row 1; the autofilter and freeze panes sit on row 2, so sorting and filtering behave. Anyone reading the sheet back uses header=1.

Choosing Readable Labels

Flattened labels built from raw column names read like code: revenue Online, units_sold Partner, discount_pct mean. Readers of an Excel report expect Online revenue or Revenue – Online. Keep the flattening mechanical and apply a separate, explicit rename step, so label wording can change without touching the aggregation logic:

# pip install "pandas>=2.2"
import pandas as pd

MEASURE_NAMES = {"revenue": "Revenue", "units": "Units", "discount_pct": "Discount %"}
SEPARATOR = " – "

def readable_labels(table: pd.DataFrame, dimension_first: bool = True) -> pd.DataFrame:
    """Flatten MultiIndex columns into 'Online – Revenue' style labels."""
    out = table.copy()
    if not isinstance(out.columns, pd.MultiIndex):
        return out.reset_index()
    labels = []
    for measure, *dims in out.columns.to_flat_index():
        name = MEASURE_NAMES.get(str(measure), str(measure).replace("_", " ").title())
        dim_text = " ".join(str(d) for d in dims if d not in ("", None))
        parts = [dim_text, name] if dimension_first else [name, dim_text]
        labels.append(SEPARATOR.join(p for p in parts if p))
    out.columns = labels
    out.columns.name = None
    return out.reset_index().rename(columns={"region": "Region"})

Keep a code-friendly version for anything that reads the file programmatically. A common pattern is two sheets written from the same frame: Summary with readable labels for people, and a hidden summary_data sheet with the plain flattened labels for downstream scripts. That separates presentation from the data contract, so renaming a header for the board never breaks a reconciliation job.

Label styles for flattened pivot columns For the column tuple revenue and Online, plain joining gives revenue Online, which is unambiguous but reads like code. The readable dimension-first style gives Online – Revenue, which suits people scanning by channel. The snake case style gives revenue_online, which is stable and safe for scripts but unfriendly on a report. Each style suits a different audience. Style Example People Scripts Plain join revenue Online acceptable stable Readable Online – Revenue best wording changes snake_case revenue_online looks technical best

Reading Such Files Back

Files produced by other teams often arrive already in the MultiIndex layout. Read both header rows and flatten on load instead of fighting Unnamed columns:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def read_multiheader(path: Path, sheet: str = "Summary") -> pd.DataFrame:
    try:
        raw = pd.read_excel(path, sheet_name=sheet, header=[0, 1], index_col=0)
    except (OSError, ValueError) as exc:
        raise RuntimeError(f"cannot read {path}: {exc}") from exc
    raw.columns = [
        " ".join(str(p) for p in col if not str(p).startswith("Unnamed")).strip()
        for col in raw.columns.to_flat_index()
    ]
    raw = raw.dropna(how="all")                      # the blank index-name row
    raw.index.name = "region"
    return raw.reset_index()

Unnamed: 0_level_0 labels are pandas' placeholders for the empty merged cells; filtering them out while joining gives the same labels as flattening before writing. The same Unnamed symptom from other causes is covered in fix pandas read_excel unnamed columns.

Verification

Read the written file back with default settings and assert it round-trips: one header row, no Unnamed columns, the expected labels, and values equal to the pivot.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def verify_flat_excel(path: Path, pivot: pd.DataFrame) -> None:
    back = pd.read_excel(path, sheet_name="Summary")
    assert not [c for c in back.columns if str(c).startswith("Unnamed")], "Unnamed columns present"
    expected = flatten_pivot(pivot)
    assert list(back.columns) == list(expected.columns), "header labels differ"
    assert len(back) == len(expected), f"{len(back)} rows, expected {len(expected)}"
    pd.testing.assert_frame_equal(back, expected, check_dtype=False)
    print(f"{path.name}: {len(back.columns)} flat columns round-trip cleanly")

if __name__ == "__main__":
    verify_flat_excel(Path("out/pivot-flat.xlsx"), pivot)

check_dtype=False allows integers written as Excel numbers to come back as int64 or float64 without failing; the values themselves must match exactly.

FAQ

Is there a pandas option to flatten automatically? No built-in option exists for to_excel. The one-line comprehension over to_flat_index() is the standard approach.

Why do I still get a blank row after flattening? The column index still has a name. Set table.columns.name = None before writing, or the name is written as an extra header cell.

Does margins=True affect flattening? It adds Total entries to each level, which flatten normally to revenue Total. Rename them afterwards if readers expect Total revenue.

Will pandas ever support MultiIndex columns with index=False? Possibly, but a single header row remains the better format for Excel users regardless, so flattening is worth doing either way.

Part of Building Pivot Tables and Summaries for Excel.