to_excel with multiple sheets and ExcelWriter

Concept

1. One DataFrame, one file

summary.to_excel("summary.xlsx", sheet_name="Summary", index=False)

index=False stops pandas writing its row numbers (0, 1, 2…) as an extra column. Keep index=True only when the index carries meaning — for example a pivot table where Region is the index.

Calling to_excel twice on the same file name overwrites the file — the second sheet replaces the first. For several sheets, use ExcelWriter.

2. Several sheets in one workbook

import pandas as pd

df = pd.read_excel("sales.xlsx", sheet_name="Data")
by_region = df.groupby("Region", as_index=False)["Amount"].sum()
by_person = df.groupby("Salesperson", as_index=False)["Amount"].sum()

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    by_region.to_excel(writer, sheet_name="Summary", index=False)
    by_person.to_excel(writer, sheet_name="By Salesperson", index=False)
    df.to_excel(writer, sheet_name="Raw Data", index=False)

The with block opens the file once, writes every sheet, and saves when the block ends.

3. One sheet per group (the Python sheet splitter)

with pd.ExcelWriter("by_region.xlsx", engine="openpyxl") as writer:
    for region, group in df.groupby("Region"):
        group.to_excel(writer, sheet_name=str(region)[:31], index=False)

Sheet names: maximum 31 characters, and can't contain \ / ? * [ ] :. The [:31] cut protects you from long names.

One file per group instead:

for region, group in df.groupby("Region"):
    group.to_excel(f"Sales_{region}.xlsx", index=False)

Compare this with the 60-line VBA splitter in the VBA projects module.

4. Add or replace a sheet in an existing file

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

mode="a" — append to the existing workbook (other sheets are kept).
if_sheet_exists="replace" — replace the Summary sheet if it's there. Other choices: "new" (adds "Summary1"), "overlay" (writes over cells without clearing the sheet), "error" (default).

Note: replacing a sheet removes its formatting and charts. Keep "pretty" sheets separate from data sheets that Python rewrites.

5. Several tables on one sheet

with pd.ExcelWriter("dashboard.xlsx", engine="openpyxl") as writer:
    by_region.to_excel(writer, sheet_name="Report", startrow=2, index=False)
    by_person.to_excel(writer, sheet_name="Report", startrow=2, startcol=4, index=False)
    writer.sheets["Report"]["A1"] = "Monthly Sales Report"

startrow / startcol are 0-based (startrow=2 → table starts on row 3). writer.sheets[...] gives you the openpyxl sheet to write a title or anything else.

6. Dates, numbers and empty values

Dates are written as real Excel dates. Set a display format with openpyxl (next lesson), or pass date_format="DD-MM-YYYY" / datetime_format="DD-MM-YYYY" to ExcelWriter.
NaN becomes an empty cell. Use na_rep="-" in to_excel to show a dash instead.
Keep codes like PIN as text in pandas (astype(str)) so leading zeros survive.

7. Don't overwrite an open file

On Windows, writing to a file that's open in Excel fails with PermissionError. Close it first, or write to a new name with the date:

from datetime import date
out = f"Sales_Report_{date.today():%Y-%m-%d}.xlsx"

Common mistakes

Calling to_excel repeatedly on the same path without ExcelWriter (only the last sheet survives). Long or invalid sheet names. Forgetting index=False. Writing while the file is open in Excel.

What you'll learn