Create a Sheet for Each Group in a DataFrame

A common report shape is "the same table, once per region" — or per customer, per sales rep, per month. Readers like it because each tab holds exactly the rows they care about, and it is one groupby loop in pandas. The loop itself is short; the details around it decide whether the workbook is usable: sheet names that Excel accepts, tabs in a sensible order, a way to find a tab when there are forty of them, and formatting that is the same on every sheet. This guide is part of Working with Multiple Excel Sheets in Python.

Prerequisites

pip install pandas xlsxwriter

import numpy as np
import pandas as pd

rng = np.random.default_rng(11)
orders = pd.DataFrame({
    "region": rng.choice(["North", "South", "East", "West", "Central/Head Office"], 400),
    "rep": rng.choice(["Ana", "Ben", "Cy", "Dee"], 400),
    "order_date": pd.Timestamp("2026-01-01") + pd.to_timedelta(rng.integers(0, 90, 400), unit="D"),
    "amount": rng.integers(50, 5_000, 400),
})

One region name, Central/Head Office, contains a slash — deliberately, because real group keys do.

Step 1 — The basic loop

Open one ExcelWriter, loop over the groups and write each one to a sheet named after its key. Writing through a single writer means the file is opened and saved once, however many sheets there are:

with pd.ExcelWriter("by_region.xlsx", engine="xlsxwriter") as writer:
    for region, group in orders.groupby("region"):
        try:
            group.to_excel(writer, sheet_name=region, index=False)
        except Exception as exc:
            print(type(exc).__name__, exc)

The loop fails on the slash: InvalidWorksheetName: Invalid Excel character '[]:*?/\' in sheetname. Group keys are data, and data does not follow Excel's naming rules, so every key needs cleaning before it becomes a sheet name.

Step 2 — Make every key a valid, unique sheet name

Excel's rules are short: at most 31 characters, none of [ ] : * ? / \, no leading or trailing apostrophe, and uniqueness compared without case. A helper that cleans, truncates and de-duplicates handles all of them:

import re

INVALID = re.compile(r"[\[\]:*?/\\]")

def sheet_names(keys) -> dict:
    used, names = set(), {}
    for key in keys:
        base = INVALID.sub("-", str(key)).strip("'").strip() or "Sheet"
        name, n = base[:31], 1
        while name.casefold() in used:
            n += 1
            suffix = f" ({n})"
            name = base[:31 - len(suffix)] + suffix
        used.add(name.casefold())
        names[key] = name
    return names

print(sheet_names(["Central/Head Office", "North", "north", "x" * 40]))
# {'Central/Head Office': 'Central-Head Office', 'North': 'North',
#  'north': 'north (2)', 'xxxx…': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'}

The de-duplication step matters more than it looks. Without it, two keys that clean or truncate to the same name — North and north, or two customer names that share their first 31 characters — both write to the same sheet in one ExcelWriter, and the second group silently replaces the first. You lose a group with no error at all.

Step 3 — Order the tabs and add a contents sheet

Tabs appear in the order you create them. Sort the keys deliberately — here, largest region first, which is usually what a reader looks for — and put a contents sheet at the front that links to every tab and states its row count:

counts = orders["region"].value_counts()
order = counts.index.tolist()                  # biggest group first
names = sheet_names(order)

with pd.ExcelWriter("by_region.xlsx", engine="xlsxwriter") as writer:
    contents = writer.book.add_worksheet("Contents")
    bold = writer.book.add_format({"bold": True})
    contents.write_row(0, 0, ["Sheet", "Rows", "Total amount"], bold)
    for i, region in enumerate(order, start=1):
        group = orders[orders["region"] == region].sort_values("order_date")
        group.to_excel(writer, sheet_name=names[region], index=False)
        contents.write_url(i, 0, f"internal:'{names[region]}'!A1", string=names[region])
        contents.write_number(i, 1, len(group))
        contents.write_number(i, 2, int(group["amount"].sum()))
    contents.set_column("A:A", 24)

print(pd.ExcelFile("by_region.xlsx").sheet_names)

The quotes in internal:'Central-Head Office'!A1 are required for any sheet name containing spaces; using them always is simpler than deciding case by case. A richer contents sheet, with back-links from every tab, is built in Add a Table of Contents Sheet with Hyperlinks in Excel.

Step 4 — Format every sheet the same way

Formatting inside the loop, through the writer's worksheet objects, guarantees identical sheets. Freeze the header, set widths, apply number and date formats, and add an autofilter sized to each group:

with pd.ExcelWriter("by_region.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:
    money = writer.book.add_format({"num_format": "#,##0"})
    for region in order:
        group = orders[orders["region"] == region].sort_values("order_date")
        name = names[region]
        group.to_excel(writer, sheet_name=name, index=False)
        ws = writer.sheets[name]
        ws.freeze_panes(1, 0)
        ws.set_column("A:B", 20)
        ws.set_column("C:C", 12)
        ws.set_column("D:D", 12, money)
        ws.autofilter(0, 0, len(group), len(group.columns) - 1)

check = pd.read_excel("by_region.xlsx", sheet_name=None)
print({name: len(frame) for name, frame in check.items()})
print(sum(len(f) for f in check.values()) == len(orders))   # True

The final check is worth keeping in production code, and it costs one extra read of a file you just wrote: the rows across all sheets must add up to the rows you started with. If a group ever goes missing — a name collision the helper did not catch, a filter applied by mistake — the job fails instead of publishing an incomplete report.

Step 5 — Group by two keys

Reports often split on two dimensions at once — a sheet per region and month. groupby with a list of columns yields tuple keys; turn each tuple into a readable name before cleaning it:

orders["month"] = orders["order_date"].dt.strftime("%Y-%m")
pairs = sorted(orders.groupby(["region", "month"]).groups)
pair_names = sheet_names([f"{region} {month}" for region, month in pairs])

with pd.ExcelWriter("by_region_month.xlsx", engine="xlsxwriter") as writer:
    for (region, month), group in orders.groupby(["region", "month"]):
        group.drop(columns="month").to_excel(writer, sheet_name=pair_names[f"{region} {month}"], index=False)

tabs = pd.ExcelFile("by_region_month.xlsx").sheet_names
print(len(tabs), tabs[:3])   # 15 ['Central-Head Office 2026-01', 'Central-Head Office 2026-02', 'Central-Head Office 2026-03']

Two keys multiply quickly: five regions over three months is fifteen tabs, and a year would be sixty. Put the coarser key first in the name so the tabs sort into readable blocks — all of one region's months together — and keep names short — Central-Head Office 2026-01 is already 27 of the 31 characters Excel allows. When the count climbs past a few dozen, split on one key into sheets and leave the other as a column the reader can filter.

Common pitfalls

• Name collisions overwrite silently. Two keys that clean to the same name write into one sheet. De-duplicate names before writing and assert the total row count afterwards.
• openpyxl is more forgiving than Excel. With the openpyxl engine, a 40-character name produces only a warning and a file Excel may refuse to open, and a case-duplicate is renamed to North1 behind your back. Clean names yourself whichever engine you use.
• Too many tabs. A sheet per customer for 600 customers produces a workbook nobody can navigate. Above a few dozen groups, prefer one sheet with a filter or one file per group.
• Reserved names. Excel reserves History for its change-tracking feature and will not accept a sheet with that name, even though both Python writers do. Treat it like an invalid character and append a suffix, for example History (data).
• Empty groups. A categorical column can yield empty groups with observed=False. Skip groups with no rows, or they become empty sheets with just a header.

Performance and scale notes

Filtering the full frame once per group, as in the examples, is fine for a handful of groups; with hundreds, iterate orders.groupby("region", sort=False) once instead, which splits the frame in a single pass. Writing is dominated by the total number of cells, not the number of sheets, so splitting a frame into sheets costs about the same as writing it once. For groups that should become separate files for different recipients, the same loop writes one workbook per key — see Split One Excel Sheet into Multiple Files by Column Value.

Conclusion

One groupby loop through one ExcelWriter produces a sheet per group. Around it, clean every key into a valid sheet name, de-duplicate case-insensitively before writing so no group overwrites another, sort the tabs on purpose, add a contents sheet with links and counts, and format each sheet inside the loop. Finish by checking that the rows across all sheets add up to the source.

Frequently asked questions

What characters are not allowed in Excel sheet names? The characters : * ? / and backslash are not allowed, a name cannot be longer than 31 characters, and it cannot start or end with an apostrophe. Names are compared without regard to case, so "North" and "north" count as duplicates.

Why did one of my groups disappear from the workbook? Two group keys produced the same sheet name after cleaning or truncation, and pandas wrote the second group into the sheet it had already created. Check that the cleaned names are unique before writing, as shown in this guide.

How do I put the sheets in a particular order? Sheets appear in the order you write them. Sort the group keys the way you want before the loop — alphabetically, by size, or by a custom list — rather than relying on groupby's default sort.

Should I use one sheet per group or one file per group? One workbook with a sheet per group suits a single reader who compares groups. One file per group suits distribution, where each recipient should see only their own data.

Related

• Up one level: Working with Multiple Excel Sheets in Python — reading and writing multi-sheet workbooks.
• Write Multiple DataFrames to One Excel File — the writer patterns underneath.
• Generate One Excel Report per Region in a Loop — templated files instead of sheets.
• Rename, Reorder and Delete Excel Sheets with openpyxl — tidying tabs after the fact.