Save Each Sheet of a Workbook as a Separate File

A regional workbook has one tab per region, and each regional manager should receive only their own tab. Copy-and-paste into new workbooks works for three tabs and becomes a chore at thirty, and Excel's "Move or Copy" keeps every cross-sheet formula pointing back at the original file. In Python the split takes a short loop — but a correct split has to deal with formulas that reference sheets that will not be in the new file, defined names that point at deleted sheets, hidden helper tabs, and sheet names that are not valid file names. This page goes through a split that keeps formatting and produces files that open cleanly. It belongs to Working with Multiple Excel Sheets in Python.

Prerequisites

pip install openpyxl pandas

regions.xlsx has three regional sheets — North | East, South and West <pilot> — plus a hidden Settings sheet holding a VAT rate, a Lists sheet, and a defined name VatRate. Each regional sheet calculates gross amounts with =B2*(1+Settings!$B$1), a formula that reaches into another sheet. The file was last saved by a spreadsheet application, so every formula cell also carries its calculated result.

Step 1 — The naive split and why it breaks

The obvious approach is: load the workbook, remove every sheet except one, save under a new name. It produces files — but look inside one:

from openpyxl import load_workbook

wb = load_workbook("regions.xlsx")
for name in wb.sheetnames:
    if name != "South":
        wb.remove(wb[name])
wb.save("naive_south.xlsx")
check = load_workbook("naive_south.xlsx")
print(check.sheetnames, check["South"]["C2"].value, check.defined_names["VatRate"].attr_text)

The new file has only the South sheet, but C2 still says =B2*(1+Settings!$B$1) and the defined name still points at Settings!$B$1. There is no Settings sheet any more, so Excel shows #REF! in every gross amount and may report a problem with the defined name. openpyxl does not rewrite or remove references when a sheet is deleted; that is up to the script.

Step 2 — Freeze cross-sheet formulas as values

A regional manager does not need a live link to a settings sheet they will never see; they need the numbers. Load the workbook a second time with data_only=True to get the cached results, and replace every formula that mentions another sheet with its value:

import re

CROSS_SHEET = re.compile(r"!|\[\d+\]")      # Sheet!A1, 'My sheet'!A1 or an external [1] link

def freeze_cross_sheet(ws, ws_values):
    frozen = 0
    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("=") and CROSS_SHEET.search(cell.value):
                cell.value = ws_values[cell.coordinate].value
                frozen += 1
    return frozen

wb = load_workbook("regions.xlsx")
values = load_workbook("regions.xlsx", data_only=True)
print("frozen:", freeze_cross_sheet(wb["South"], values["South"]), "->", wb["South"]["C2"].value)

Formulas that stay within the sheet — a SUM of its own column, say — are left alone and keep working. Only references that would dangle are frozen. The value comes from the copy saved by the last spreadsheet application; if the workbook was written by a script and never opened, there is no cached value and the cell would become empty, so recalculate it first as described in Recalculate Excel Formulas Without Excel in Python.

The regular expression catches the usual shapes: Settings!$B$1, quoted names such as 'Price list'!A1, three-dimensional ranges like Jan:Mar!B2, and the [1] prefix that marks a link to another workbook. It cannot see references hidden inside text, such as INDIRECT("Settings!B1"), or formulas that use a defined name — =B2*(1+VatRate) contains no ! at all. If the source uses names in formulas, add a second test that looks for each name defined at workbook level and freeze those cells too; the list of names is in wb.defined_names.

Step 3 — Make file names from sheet names

Excel already bans / \ ? * [ ] : in sheet names, but it allows |, <, > and ", which Windows forbids in file names — North | East and West <pilot> are valid tabs and invalid files. Replace the characters Windows rejects, the strictest common rule set, so the same script works on every system:

from pathlib import Path

def file_name(sheet, prefix="regions"):
    safe = re.sub(r'[<>:"/\\|?*]', "-", sheet)
    safe = re.sub(r"\s+", " ", safe).strip(" .")
    return f"{prefix} - {safe}.xlsx"

for s in ["North | East", "South", "West <pilot>"]:
    print(repr(s), "->", file_name(s))

Trailing dots and spaces are stripped because Windows silently drops them, which would make two different sheet names map to the same file. Prefixing the source file name keeps the outputs recognisable when they are attached to emails or uploaded together.

Step 4 — The complete split

Put the pieces together: for each sheet to export, load the workbook fresh, freeze cross-sheet formulas, delete the other sheets, remove defined names that now point nowhere, make sure the remaining sheet is visible, and save:

def split_workbook(path, out_dir="split", skip=("Settings", "Lists")):
    out = Path(out_dir)
    out.mkdir(exist_ok=True)
    values = load_workbook(path, data_only=True)
    written = []
    for name in load_workbook(path, read_only=True).sheetnames:
        if name in skip:
            continue
        wb = load_workbook(path)
        freeze_cross_sheet(wb[name], values[name])
        for other in wb.sheetnames:
            if other != name:
                wb.remove(wb[other])
        for dn in list(wb.defined_names):
            if "!" in wb.defined_names[dn].attr_text and name not in wb.defined_names[dn].attr_text:
                del wb.defined_names[dn]
        ws = wb[name]
        ws.sheet_state = "visible"
        wb.active = 0
        target = out / file_name(name)
        wb.save(target)
        written.append(target.name)
    return written

for f in split_workbook("regions.xlsx"):
    print(f)

Loading the source once per output is the simplest way to start each file from an untouched copy; the alternative — deleting sheets from one loaded workbook and "undoing" it — is error-prone. The skip tuple keeps helper sheets out of the output. Setting the sheet visible matters when the sheet you export was hidden: a workbook whose only sheet is hidden cannot be saved, and openpyxl raises ValueError: The only worksheet of a workbook cannot be hidden.

Step 5 — Check the outputs

Open each file and confirm it has one sheet, no formulas that reach outside it, and no defined names that point at missing sheets:

for f in sorted(Path("split").glob("*.xlsx")):
    wb = load_workbook(f)
    ws = wb.active
    leftovers = [c.coordinate for row in ws.iter_rows() for c in row
                 if isinstance(c.value, str) and c.value.startswith("=") and "!" in c.value]
    print(f.name, wb.sheetnames, "gross C2 =", ws["C2"].value, "| cross-sheet formulas:", leftovers,
          "| names:", list(wb.defined_names))

Each file holds one visible sheet with its formatting — the header fill and the #,##0.00 format on the gross column came through untouched — gross amounts as numbers, and no dangling names. A check like this is worth keeping in the script itself: it costs milliseconds and catches the day someone adds a new cross-sheet lookup to the template.

Step 6 — Other ways to split

Values only, with pandas. If formatting does not matter, pd.read_excel(path, sheet_name=None) returns every sheet as a DataFrame, and each can be written with to_excel. The output has no styles, no widths and no formulas at all — every formula becomes its cached value automatically, which sidesteps Step 2.

import pandas as pd

for name, df in pd.read_excel("regions.xlsx", sheet_name=None).items():
    if name not in ("Settings", "Lists"):
        df.to_excel(Path("split") / file_name(name, prefix="values"), index=False)
print(sorted(p.name for p in Path("split").glob("values*")))

Full fidelity, through Excel. openpyxl does not carry charts, images or form controls across a load-and-save, as listed in What openpyxl Loses When Saving an Existing Workbook. On Windows with Excel installed, sheet.Copy() through win32com creates a new workbook containing an exact copy of the sheet, charts included, and SaveAs writes it; cross-sheet formulas then point back at the source file as external links, so break them with wb.BreakLink or paste values afterwards. See Automating Excel with COM and pywin32.

Common pitfalls

• Dangling references. Deleting sheets does not update formulas or defined names; freeze or remove them.
• No cached values. data_only=True returns None for formulas in files that no application has calculated; recalculate first.
• Hidden sheets. The only sheet in a workbook must be visible.
• Illegal file names. Replace < > : " / \ | ? * and strip trailing dots and spaces.
• Charts and images. openpyxl drops them on save; use the COM route when they matter.

Conclusion

To save each sheet as its own file, load a fresh copy of the workbook per sheet, replace formulas that reach into other sheets with their cached values, delete the other sheets, remove defined names that would dangle, make the sheet visible and save it under a cleaned file name — then check each output. Use pandas when only the values matter, and Excel through COM when charts and images must come along.

Frequently asked questions

How do I split an Excel workbook into separate files per sheet with Python? For each sheet, load the workbook with openpyxl, remove every other sheet with wb.remove(), and save the result under a new name. Fix cross-sheet formulas and defined names first so the new files do not show #REF! errors.

Why do my split files show #REF! errors? Formulas such as =B2*Settings!B1 still refer to sheets that were removed. openpyxl does not rewrite them. Replace those formulas with their cached values, loaded with data_only=True, before removing the other sheets.

Why does openpyxl say the only worksheet cannot be hidden? The sheet you kept was hidden in the source workbook. A workbook needs at least one visible sheet, so set ws.sheet_state = "visible" before saving.

Does splitting with openpyxl keep charts? No. openpyxl does not preserve charts and images when it loads and saves an existing workbook. Use Excel through win32com (sheet.Copy and SaveAs) when charts must be kept.

Related

• Up one level: Working with Multiple Excel Sheets in Python — reading, combining and splitting sheets.
• Split One Excel Sheet into Multiple Files by Value — splitting rows rather than sheets.
• Merge Workbooks into One File with a Sheet per Source — the reverse operation.
• Send Each Recipient Their Own Excel Report — delivering the split files.