Guide

Automating Reporting WorkflowsDeep dive

Write Multiple DataFrames to One Excel File

Q: Why does my file only contain the last DataFrame?

You opened more than one ExcelWriter on the same path. The second one opens in write mode and truncates the file. Put every to_excel() call inside a single with pd.ExcelWriter(...) block.

Q: Do I need index=False on every call?

Yes. index=False is an argument to each to_excel() call and is not carried over from a previous write. Omit it once and that sheet gets a row-number column.

Q: What is the maximum sheet name length?

Excel caps tab names at 31 characters and forbids \ / * ? : [ ]. Names over the limit or with those characters raise an error or get mangled — sanitize names you build from data.

Q: How do I add a sheet without rewriting the file?

Open the writer with mode="a" and engine="openpyxl", and set if_sheet_exists to control collisions. Use "replace" so a rerun overwrites the sheet cleanly.

Write several pandas DataFrames into one .xlsx: separate sheets via one ExcelWriter, stacking on a single sheet with startrow, and appending with mode='a'.

You have several DataFrames and want them in one workbook — one sheet each, or stacked on a single sheet, or appended to a file that already exists. The mechanism for all three is pandas.ExcelWriter, and the single rule that prevents the most common failure is: one writer per file, opened once. This page is part of Building Multi-Sheet Excel Dashboards; it covers the writer mechanics that the dashboard build depends on.

Prerequisites

You need pandas and at least one Excel engine. openpyxl reads and writes .xlsx and supports append mode, so install it:

Bash

pip install pandas openpyxl xlsxwriter

xlsxwriter is optional — it is a fast write-only engine, useful when you never need to reopen the file. For multi-sheet dashboards you reopen later, prefer openpyxl. If you are unsure which one fits your job, the openpyxl vs xlsxwriter vs pandas.ExcelWriter comparison lays out the trade-offs in full.

One sheet per DataFrame

Open a single pd.ExcelWriter as a context manager and call to_excel() once per DataFrame, each with a distinct sheet_name. The with block writes and closes the file exactly once when it exits:

Python

import pandas as pd

region = pd.DataFrame({
    "region": ["North", "South", "West"],
    "revenue": [26700, 20900, 7600],
})
month = pd.DataFrame({
    "month": ["Jan", "Feb", "Mar"],
    "revenue": [22300, 21800, 11100],
})
product = pd.DataFrame({
    "product": ["Widget", "Gadget", "Gizmo"],
    "units": [350, 209, 76],
})

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    region.to_excel(writer, sheet_name="By Region", index=False)
    month.to_excel(writer, sheet_name="By Month", index=False)
    product.to_excel(writer, sheet_name="By Product", index=False)

print("Wrote 3 sheets to report.xlsx")

The order of the to_excel() calls sets the left-to-right tab order. index=False drops the pandas row-number column — pass it on every call, because it is not remembered between writes.

Several DataFrames on the same sheet

To place tables side by side or stacked on one sheet, pass the same sheet_name with different startrow / startcol offsets. startrow and startcol are zero-based and count from the top-left. Leave a blank row between stacked tables so they read as separate blocks:

Python

import pandas as pd

summary = pd.DataFrame({
    "Metric": ["Total Revenue", "Total Units"],
    "Value": [55200, 635],
})
detail = pd.DataFrame({
    "region": ["North", "South", "West"],
    "revenue": [26700, 20900, 7600],
})

with pd.ExcelWriter("stacked.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Overview", index=False, startrow=0)
    # leave one blank row after summary (len + header + gap)
    next_row = len(summary) + 2
    detail.to_excel(writer, sheet_name="Overview",
                    index=False, startrow=next_row)

print(f"Stacked detail starting at row {next_row + 1} on the Overview sheet")

For side-by-side blocks, keep startrow fixed and vary startcol instead — for example startcol=0 for the first table and startcol=4 for the second.

Append a sheet to an existing workbook

To add a sheet to a file you already wrote — without rewriting the whole thing — open the writer with mode="a" (append) and an if_sheet_exists policy. Append mode requires engine="openpyxl"; xlsxwriter cannot append. This is the DataFrame-level counterpart to appending rows with openpyxl at the cell level. if_sheet_exists controls what happens when the sheet name is already present:

Python

import pandas as pd

# Assume report.xlsx already exists from the first example.
late = pd.DataFrame({
    "region": ["East"],
    "revenue": [4100],
})

with pd.ExcelWriter("report.xlsx", engine="openpyxl",
                    mode="a", if_sheet_exists="replace") as writer:
    late.to_excel(writer, sheet_name="By Region East", index=False)

print("Appended a sheet to the existing report.xlsx")

The if_sheet_exists values are: "error" (default — raise if the name exists), "replace" (drop and rewrite the sheet), "overlay" (write on top of existing cells, keeping the rest), and "new" (create a uniquely renamed sheet). Use "replace" for idempotent jobs that rerun the same report.

Choosing an engine

Engine	Read existing file	Append mode	Charts	When to use
`openpyxl`	Yes	Yes	Yes (`openpyxl.chart`)	Multi-sheet dashboards you reopen to add charts, links, or sheets.
`xlsxwriter`	No (write-only)	No	Yes (`workbook.add_chart`)	One-shot writes where speed matters and you never reopen the file.

For everything in the dashboard build, use openpyxl — append and load_workbook() are what make it work. See the full engine comparison if your job leans heavily on speed or on later edits.

Common pitfalls

Symptom	Cause	Fix
Only the last sheet survives	A second `pd.ExcelWriter` opened on the same path in default `mode="w"` truncated the file	Keep all writes in one `with` block, or use `mode="a"` to add to an existing file.
A stray `0, 1, 2` column appears	`index=False` missing — it is per-call, not global	Pass `index=False` on every `to_excel()` call.
`InvalidWorksheetName` or silent truncation	Sheet name over 31 characters	Keep names ≤ 31 chars; truncate long names before writing.
`ValueError` on the sheet name	Name contains `\ / * ? : [ ]`	Strip those characters; Excel forbids them in tab names.
`ValueError: Append mode is not supported with xlsxwriter`	`mode="a"` with the wrong engine	Use `engine="openpyxl"` for append.
Appended sheet raises on rerun	Default `if_sheet_exists="error"` and the name already exists	Pass `if_sheet_exists="replace"` for idempotent reruns.

Scale note

For workbooks with many large sheets, xlsxwriter writes faster and uses less memory than openpyxl, and its constant_memory mode streams rows to disk — but it cannot reopen or append, so you must write every sheet in one pass. If a job grows beyond comfortable memory, write each DataFrame to its own file and combine the files in a final openpyxl pass, or switch the whole job to a single streaming xlsxwriter write.

One sheet each, or several blocks on one sheet

Python

import pandas as pd

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False, startrow=0)
    detail.to_excel(writer, sheet_name="Summary", index=False, startrow=len(summary) + 2)
    detail.to_excel(writer, sheet_name="Detail", index=False)

The len(summary) + 2 offset leaves exactly one blank row between the blocks — enough that a reader sees two tables, and enough that an autofilter applied to the lower block does not reach up into the upper one. Computing the offset from the frame rather than hardcoding it is what keeps the layout correct when the summary grows a row.

Where both a combined view and a filterable table are wanted, writing the detail twice — once as a block and once on its own sheet — costs a few kilobytes and removes the compromise entirely.

Name the sheets for the reader

Sheet names are navigation. Summary, By region, Detail tell a reader where to go; Sheet1, df2, output_final tell them the file was generated by somebody in a hurry. Because Excel caps names at 31 characters and rejects five punctuation marks, running every generated name through one sanitising function is what keeps a clear naming scheme from turning into a save-time error.

Write in one context

Opening a single ExcelWriter and writing every frame inside it produces one coherent file and one save. Writing each frame with its own to_excel call to the same path does not append — each call replaces the file, leaving only the last frame, which is the most common surprise in multi-sheet output.

Presentation comes after the data

Any write replaces what it covers, so formatting, filters, images and charts belong in a single finishing pass that runs after the last value has been written. Splitting the job that way — build the frame, write it, then decorate the finished sheet — is what stops a style disappearing the month someone adds a to_excel call in the middle. It also gives a report one obvious place to change when the house style moves, instead of a dozen scattered blocks that have to be found first.

Frequently asked questions

Why does my file only contain the last DataFrame? You opened more than one ExcelWriter on the same path. The second one opens in write mode and truncates the file. Put every to_excel() call inside a single with pd.ExcelWriter(...) block.

Do I need index=False on every call? Yes. index=False is an argument to each to_excel() call and is not carried over from a previous write. Omit it once and that sheet gets a row-number column.

What is the maximum sheet name length? Excel caps tab names at 31 characters and forbids \ / * ? : [ ]. Names over the limit or with those characters raise an error or get mangled — sanitize names you build from data.

How do I add a sheet without rewriting the file? Open the writer with mode="a" and engine="openpyxl", and set if_sheet_exists to control collisions. Use "replace" so a rerun overwrites the sheet cleanly.

Conclusion

Writing multiple DataFrames to one file comes down to one ExcelWriter: distinct sheet_name values for separate tabs, matching sheet_name with startrow/startcol offsets to stack tables on one sheet, and mode="a" with if_sheet_exists to append to an existing workbook. Open the writer once, pass index=False every time, and keep sheet names short and clean — and the three failure modes above never occur.

Up to the parent guide:

Building Multi-Sheet Excel Dashboards — assemble these sheets into a Summary-plus-detail dashboard with a chart.

Keep going:

Add a Summary Sheet to an Excel Report with Python — compute KPIs and write a front-page Summary tab in front of these sheets.
openpyxl vs xlsxwriter vs pandas.ExcelWriter — pick the right engine for append, charts, or raw write speed.
Append Data to an Existing Excel Sheet with openpyxl — add rows to a sheet at the cell level, the counterpart to mode="a" here.
Writing DataFrames to Excel with Pandas — the core to_excel() options these examples build on.