Split One Excel Sheet into Multiple Files by Column Value
Somebody sends a single export with every region in it, and four people each want only their own rows. Or the finance system produces one file a month and each cost centre needs its own copy. Splitting is a three-line groupby and a to_excel — and then the practical work begins: filenames that are legal on every platform, blank values that groupby silently drops, and formatting that has to be identical on every output. This guide covers the split properly. It extends Working with Multiple Excel Sheets in Python.
Prerequisites
pip install pandas openpyxl xlsxwriter
A combined export to split:
import pandas as pd
pd.DataFrame({
"region": ["North", "South", "West", "North", "South", None],
"branch": [f"Branch {i}" for i in range(1, 7)],
"revenue": [5150.00, 4268.50, 3511.25, 2980.10, 3140.75, 1820.00],
"invoice_date": pd.to_datetime(
["2026-08-01", "2026-08-03", "2026-08-05",
"2026-08-09", "2026-08-14", "2026-08-15"]
),
}).to_excel("all_regions.xlsx", index=False)
Step 1 — Split into one file per value
groupby gives you the key and the rows together:
from pathlib import Path
import pandas as pd
df = pd.read_excel("all_regions.xlsx")
out = Path("split")
out.mkdir(exist_ok=True)
for region, group in df.groupby("region"):
group.to_excel(out / f"{region}.xlsx", index=False)
That works on clean data and fails on real data in two ways. The row whose region is None vanished — groupby drops missing keys by default — and a region named North/South would raise, because a slash is not legal in a filename.
Step 2 — Make the filenames safe
Sanitising is more than removing slashes. Windows also forbids a set of reserved device names, disallows trailing dots and spaces, and caps path length:
import re
ILLEGAL = re.compile(r'[<>:"/\\|?*\x00-\x1f]')
RESERVED = {
"CON", "PRN", "AUX", "NUL",
*(f"COM{i}" for i in range(1, 10)),
*(f"LPT{i}" for i in range(1, 10)),
}
def safe_filename(value, fallback="unspecified", max_length=80):
"""Turn an arbitrary group key into a filename that works everywhere."""
text = fallback if value is None or (isinstance(value, float) and value != value) \
else str(value)
text = ILLEGAL.sub("_", text)
text = re.sub(r"\s+", " ", text).strip(" .") # no trailing dots or spaces
text = text[:max_length].strip() or fallback
if text.upper() in RESERVED:
text = f"{text}_"
return text
print(safe_filename("North/South")) # North_South
print(safe_filename(None)) # unspecified
print(safe_filename("CON")) # CON_
The value != value test is the idiomatic check for NaN, which is the form a missing key takes when the column is numeric.
Sanitising creates collisions, so resolve them after the fact rather than hoping:
def unique_names(values, **kwargs):
"""Map each group key to a distinct, safe filename stem."""
used, mapping = {}, {}
for value in values:
stem = safe_filename(value, **kwargs)
key = stem.lower()
if key in used:
used[key] += 1
stem = f"{stem}_{used[key]}"
else:
used[key] = 1
mapping[value] = stem
return mapping
Step 3 — Handle the missing group and guard the count
Two guards turn a fragile loop into something safe to run unattended:
from pathlib import Path
import pandas as pd
def split_to_files(df, key, out_dir="split", max_groups=200, formatter=None):
"""Write one workbook per distinct value of `key`."""
# dropna=False keeps the rows whose key is missing.
groups = list(df.groupby(key, dropna=False))
if len(groups) > max_groups:
raise ValueError(
f"{len(groups)} distinct values in {key!r} — refusing to write that "
f"many files. Raise max_groups deliberately if this is intended."
)
out = Path(out_dir)
out.mkdir(parents=True, exist_ok=True)
names = unique_names([value for value, _ in groups])
written = []
for value, group in groups:
path = out / f"{names[value]}.xlsx"
if formatter:
formatter(group, path)
else:
group.to_excel(path, index=False)
written.append((value, path, len(group)))
return written
for value, path, rows in split_to_files(pd.read_excel("all_regions.xlsx"), "region"):
print(f"{str(value):<14} {rows:>4} rows -> {path.name}")
The max_groups guard is the one that saves you. Splitting on a column you assumed had five values and actually has four thousand fills a directory and, if the next step emails each file, sends four thousand emails. Failing loudly is much better.
Step 4 — Format every output identically
If the outputs go to people, they should look like reports rather than raw dumps. Pass a formatter so every file is produced the same way:
import pandas as pd
def write_report(group, path, sheet_name="Detail"):
"""Write one group as a formatted, self-contained report."""
with pd.ExcelWriter(path, engine="xlsxwriter",
datetime_format="yyyy-mm-dd") as writer:
group.to_excel(writer, sheet_name=sheet_name, index=False)
book, sheet = writer.book, writer.sheets[sheet_name]
header = book.add_format({"bold": True, "bg_color": "#EEF2FF",
"border": 1, "align": "center"})
money = book.add_format({"num_format": "#,##0.00"})
for position, name in enumerate(group.columns):
sheet.write(0, position, str(name), header)
sheet.set_column("A:A", 16)
sheet.set_column("B:B", 18)
sheet.set_column("C:C", 14, money)
sheet.set_column("D:D", 14)
sheet.freeze_panes(1, 0)
sheet.autofilter(0, 0, len(group), len(group.columns) - 1)
split_to_files(pd.read_excel("all_regions.xlsx"), "region",
formatter=write_report)
Keeping the formatting in one function is what stops the outputs drifting apart. The wider vocabulary is in writing a formatted Excel report with xlsxwriter.
Step 5 — Split into sheets instead
When one person wants everything, organised, a single workbook is far easier to handle than a folder:
import pandas as pd
def split_to_sheets(df, key, path, max_sheets=60):
"""One sheet per distinct value, in a single workbook."""
groups = list(df.groupby(key, dropna=False))
if len(groups) > max_sheets:
raise ValueError(f"{len(groups)} groups is too many sheets to navigate")
names = unique_names([value for value, _ in groups], max_length=31)
with pd.ExcelWriter(path, engine="xlsxwriter",
datetime_format="yyyy-mm-dd") as writer:
summary = (
df.groupby(key, dropna=False)
.agg(rows=(df.columns[0], "size"), revenue=("revenue", "sum"))
.reset_index()
)
summary.to_excel(writer, sheet_name="Summary", index=False)
for value, group in groups:
group.to_excel(writer, sheet_name=names[value], index=False)
return path
split_to_sheets(pd.read_excel("all_regions.xlsx"), "region", "by_region.xlsx")
Sheet names cap at 31 characters, hence the shorter max_length. Adding a summary sheet first gives the reader somewhere to land — the pattern described in adding a summary sheet to an Excel report.
Common pitfalls and fixes
| Symptom | Cause | Fix |
|---|---|---|
| Rows silently missing from every output | groupby drops missing keys | Pass dropna=False. |
OSError: Invalid argument writing a file | Illegal character in the group value | Sanitise the filename. |
| Two groups overwrote each other | Sanitising produced the same name | Resolve collisions with a counter. |
| Thousands of files created | Split column had far more values than expected | Guard with a max_groups limit. |
InvalidWorksheetName | Sheet name over 31 characters | Cap the length when splitting to sheets. |
| Outputs look inconsistent | Formatting inline in the loop, drifted over time | One shared formatter function. |
| Totals do not match the source | Rows dropped or duplicated | Assert the row counts sum back. |
Performance and scale notes
groupby is a single pass and cheap. The cost is one workbook write per group, and each write has a fixed overhead independent of size — so a split into three hundred small files costs far more than one file three hundred times larger.
Two things follow. Guard the group count, as above, because the cost is linear in files and people rarely intend four thousand of them. And parallelise across groups when the count is genuinely high, since each write is independent:
from concurrent.futures import ProcessPoolExecutor
from pathlib import Path
import pandas as pd
def _write_one(args):
stem, records, columns = args
frame = pd.DataFrame(records, columns=columns)
path = Path("split") / f"{stem}.xlsx"
write_report(frame, path)
return stem, len(frame)
if __name__ == "__main__":
df = pd.read_excel("all_regions.xlsx")
names = unique_names(df["region"].unique())
payload = [
(names[value], group.to_records(index=False).tolist(), list(df.columns))
for value, group in df.groupby("region", dropna=False)
]
with ProcessPoolExecutor(max_workers=4) as pool:
for stem, rows in pool.map(_write_one, payload):
print(f"{stem:<20} {rows} rows")
Passing records rather than DataFrames keeps the pickling cost down, since each worker rebuilds its own frame from plain tuples.
Finally, verify. A split should conserve rows exactly, and asserting it catches both a dropped group and a duplicated one:
import pandas as pd
source = pd.read_excel("all_regions.xlsx")
written = split_to_files(source, "region")
total = sum(rows for _, _, rows in written)
assert total == len(source), f"split produced {total} rows from {len(source)}"
print("row counts reconcile")
That check belongs in the same suite as the other output assertions described in testing Excel output with pytest.
Conclusion
Splitting a sheet is groupby plus a write, wrapped in the guards that make it safe on real data. Pass dropna=False so rows with a missing key are not silently lost, sanitise the group values into filenames that are legal everywhere and resolve the collisions that sanitising creates, and refuse to run when the group count is far higher than you expected. Route every output through one formatter so all the files look alike, choose sheets over files when a single person wants the whole picture, and assert that the row counts reconcile before anything is sent.
Frequently asked questions
Should I split into separate files or separate sheets? Separate files when each group goes to a different person, because you can send exactly one workbook to each. Separate sheets when one person wants the whole picture but organised by group — a single file is far easier to open and navigate.
How do I make safe filenames from the group values?
Strip characters that are illegal on Windows, collapse whitespace, cap the length, and handle collisions after sanitising. A region called North/South and one called North-South both become the same name otherwise.
What if a value is blank or missing?
Decide explicitly rather than letting groupby drop it. Pass dropna=False to groupby and map the missing key to a name like unspecified, or route those rows to a separate exceptions file.
Can I keep the formatting on every split file? Yes, but not by copying — write each output through the same formatting function so all of them are produced identically. Copying a styled template and filling it works too when the layout is fixed.
What happens with hundreds of groups? Guard against it. A split on an unexpectedly high-cardinality column can produce thousands of files, so check the group count first and refuse if it exceeds a sensible limit.
Related
- Up to the parent: Working with Multiple Excel Sheets in Python — the sheet-level operations behind this.
- Combine Multiple Excel Files into One with Python — the inverse operation.
- Generate One Excel Report per Region in a Loop — the same fan-out driven from a template.
- Write Multiple DataFrames to One Excel File — the split-to-sheets mechanics in more depth.
- Write a Formatted Excel Report with xlsxwriter — the formatter each output shares.