Splitting One Excel Sheet Into a Workbook per Group

One sheet, one column that says who each row belongs to, and fifty people who should each get only their rows. Branch managers get their branch, account managers their customers, suppliers their orders. Doing it by hand means filter, copy, new workbook, paste, save, repeat. It takes an afternoon and risks sending someone another person's data. A script does it in seconds and does the same thing every time. This post covers the split, safe file names, formatting, a manifest and the checks that matter. It is a spoke of automating Excel work without clicking.

The basic split

from pathlib import Path
import pandas as pd

df = pd.read_excel("sales.xlsx", dtype={"order": str})
out_dir = Path("by_region")
out_dir.mkdir(exist_ok=True)

for region, part in df.groupby("region", sort=True):
    part.to_excel(out_dir / f"sales_{region}.xlsx", index=False, sheet_name="Sales")

groupby yields the group value and the rows for it. index=False keeps the DataFrame's row numbers out of the file. This works for the happy path. Four things make it safe for real data: file names, empty and unusual groups, formatting and checks.

File names from data

A group value goes into a file name, and data is untrustworthy. Names that cause trouble:

• Slashes and colons. North/East becomes a path with a folder; Q1: Final is invalid on Windows.

• Reserved names and characters. CON, PRN and NUL on Windows; < > : " | ? * anywhere.

• Length. Full paths over about 260 characters fail on older Windows setups.

• Trailing dots and spaces. Windows silently strips them, producing the wrong name.

• Case. North and NORTH collide on case-insensitive filesystems.

• Empty or null. A missing region gives a file called nan.

• Path traversal. A value like ../../etc/x in a group column must never reach the filesystem as is.

Sanitize in one function and make names unique:

import re

def safe_name(value, used):
    s = "unassigned" if pd.isna(value) or str(value).strip() == "" else str(value).strip()
    s = re.sub(r"[^\w\- ]+", "_", s)[:60].strip(" ._") or "group"
    base, n = s.lower(), 2
    while s.lower() in used:
        s = f"{base}_{n}"
        n += 1
    used.add(s.lower())
    return s

The regular expression keeps letters, digits, underscore, hyphen and space, and replaces the rest. Cutting at 60 characters and tracking used names in lower case avoids collisions. Keep a mapping from the original group value to the file name in your manifest, so a sanitized name can be traced back.

Empty and unusual groups

Decide what to do about rows with a missing group value. Dropping them loses data; writing them to unassigned keeps them where someone will see them. By default groupby drops null keys, so use dropna=False and handle the null explicitly:

for key, part in df.groupby("region", dropna=False):
    ...

Check for near-duplicate group values (North, north, North ) before splitting, because each variant becomes a file. Clean first (standardizing categories with a lookup table). Also decide whether recipients with no rows this period should get an empty file or none. An empty file with only headers says "nothing to report", which is information; no file is ambiguous.

Format every output the same way

Pandas writes plain files. Run each through the same formatting function so no recipient gets a lesser file:

def write_formatted(part, path):
    with pd.ExcelWriter(path, engine="xlsxwriter") as xw:
        part.to_excel(xw, sheet_name="Sales", index=False)
        wb, ws = xw.book, xw.sheets["Sales"]
        head = wb.add_format({"bold": True, "bg_color": "#DDEBF7", "border": 1})
        for col, name in enumerate(part.columns):
            ws.write(0, col, name, head)
            width = max(len(str(name)), part[name].astype(str).str.len().max() or 0)
            ws.set_column(col, col, min(width + 2, 40))
        ws.freeze_panes(1, 0)
        ws.autofilter(0, 0, len(part), len(part.columns) - 1)

Header style, column widths from content, a frozen header row and a filter are the usual set. If the original workbook has formatting you want to preserve, copy it with openpyxl by loading the file, deleting rows that are not in the group and saving under a new name. That preserves styles and formulas but is slower and fragile with merged cells; building fresh files from data is more predictable (formatting generated workbooks).

Keep types intact. Read identifiers as text so leading zeros survive, and write dates as dates with a number format, not as strings.

Write a manifest

After the split, write a small table of what was produced:

manifest = []
used = set()
for key, part in df.groupby("region", dropna=False):
    name = safe_name(key, used)
    path = out_dir / f"sales_{name}.xlsx"
    write_formatted(part, path)
    manifest.append({"group": key, "file": path.name, "rows": len(part),
                     "total": part["amount"].sum()})

man = pd.DataFrame(manifest)
man.to_csv(out_dir / "manifest.csv", index=False)
assert man["rows"].sum() == len(df), "split lost or duplicated rows"
assert abs(man["total"].sum() - df["amount"].sum()) < 0.005, "totals differ"

The two assertions are the heart of the check: the groups add up to the whole, in rows and in money. With a manifest, "how many files did we send and to whom" has an answer, and so does "did Maria get her 212 rows". Keep it with the outputs.

Send only what each person should see

The point of splitting is often access control, so treat it that way. Each file must contain only the recipient's rows and nothing else.

• Hidden sheets, columns and rows. If you split by copying the original workbook and deleting rows, hidden content and other groups' data may remain in sheets, defined names or the document properties. Build from data instead.

• Metadata. The author, the last modified by and the title come from the writer. Set them to neutral values.

• Pivot caches and external links. Copies of a workbook carry the full cache of any pivot table. A pivot built on the full data in a copied file exposes everything.

• File names. A name like sales_Acme_Corp.xlsx is itself information if sent to the wrong party.

Run a check that opens each output and asserts that every row belongs to the group, that there is one sheet, and that no sheet is hidden. If the data is sensitive, review a sample by hand. Treat metadata cleanup as part of the writer, and check the saved file, not the intention.

Deliver

Writing files is half the job. Deliver them by email, to a shared folder per recipient or through a link, and record what was sent. Recipient addresses come from a lookup table keyed on the group, not from the data in the sheet, since a column of emails in a report is a privacy problem of its own. A common design is a recipients.csv with group,email, and a check that every group has a recipient and every recipient has a group. Groups without a recipient should stop the run or go to a fallback address, not be skipped silently.

A Power Query alternative

Excel can split with a pivot table: put the group column in Filters and use Show Report Filter Pages, which creates one sheet per value in the same workbook. That is fine to review in one file, and does not make separate workbooks. Power Query can build one query per group but does not write files. For separate workbooks, a script is the practical route.

When splitting files is the wrong way to share data

The recipients need live data. Fifty static files go stale. A shared dashboard with row-level security, or a database view per role, stays current.

Groups change often. If people move between groups constantly, a split creates a pile of outdated files.

The data is sensitive. Email attachments are copies you cannot recall. Use an access-controlled location.

You need a combined view too. Split for delivery and keep the master as the source; do not edit the pieces (merging hundreds of workbooks into one table covers the reverse).

See the pandas reference for DataFrame.groupby and the XlsxWriter documentation for formats. To convert a small result to JSON, use the CSV to JSON converter.