Python Excel AutomationPractical guides for developers
Start learning

Guide

Automating Reporting WorkflowsDeep dive

Write Multiple DataFrames to One Excel File

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.

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. 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

EngineRead existing fileAppend modeChartsWhen to use
openpyxlYesYesYes (openpyxl.chart)Multi-sheet dashboards you reopen to add charts, links, or sheets.
xlsxwriterNo (write-only)NoYes (workbook.add_chart)One-shot writes where speed matters and you never reopen the file.

For everything in this cluster, use openpyxl — append and load_workbook() are what make the dashboard build work.

Common pitfalls

SymptomCauseFix
Only the last sheet survivesA second pd.ExcelWriter opened on the same path in default mode="w" truncated the fileKeep all writes in one with block, or use mode="a" to add to an existing file.
A stray 0, 1, 2 column appearsindex=False missing — it is per-call, not globalPass index=False on every to_excel() call.
InvalidWorksheetName or silent truncationSheet name over 31 charactersKeep names ≤ 31 chars; truncate long names before writing.
ValueError on the sheet nameName contains \ / * ? : [ ]Strip those characters; Excel forbids them in tab names.
ValueError: Append mode is not supported with xlsxwritermode="a" with the wrong engineUse engine="openpyxl" for append.
Appended sheet raises on rerunDefault if_sheet_exists="error" and the name already existsPass 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 them in a final openpyxl pass, or switch the whole job to a single streaming xlsxwriter write.

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.

Where to go next