Merge Workbooks into One File with a Sheet per Source

Twelve branch managers each send a workbook. Finance wants one file with twelve tabs, not one long table — each branch's sheet should look the way the branch sent it, and the totals at the bottom of each sheet should still be there. That is a different job from stacking rows with pd.concat, which is the right tool when the goal is a single dataset. Here the goal is a binder: one workbook, one tab per source, each tab named after its file. The tricky parts are sheet names (Excel is strict about them), formatting (pandas drops it), and sources that have more than one sheet. This page belongs to Working with Multiple Excel Sheets in Python.

Prerequisites

pip install pandas openpyxl

The branches/ folder holds four workbooks. Each has a styled header, a Sales sheet with a SUM total row and number formats. One file name contains square brackets, one contains a colon and runs well past 31 characters, and two branches produce the same name once cleaned — the realistic mess that breaks naive scripts. One workbook also has a second sheet called Notes.

Step 1 — Turn file names into valid, unique sheet names

Excel sheet names must be 1–31 characters, cannot contain [ ] : * ? / \, cannot start or end with an apostrophe, and must be unique ignoring case. Clean each name, shorten it, and add a counter when it collides:

import re
from pathlib import Path

def sheet_name(raw, taken):
    name = re.sub(r"[\[\]:*?/\\]", " ", raw)
    name = re.sub(r"\s+", " ", name).strip().strip("'") or "Sheet"
    base, n = name[:31].rstrip(), 1
    candidate = base
    while candidate.lower() in taken:
        n += 1
        suffix = f" ({n})"
        candidate = base[:31 - len(suffix)].rstrip() + suffix
    taken.add(candidate.lower())
    return candidate

files = sorted(Path("branches").glob("*.xlsx"))
taken = set()
names = {f: sheet_name(f.stem, taken) for f in files}
for f, n in names.items():
    print(f"{f.name:55} -> {n!r} ({len(n)})")

The colon and brackets become spaces, the long Birmingham name is cut at 31 characters and the trailing space that leaves is trimmed, and the two Leeds files — Leeds [North] and Leeds (North) — stay distinct because brackets and parentheses are treated differently; had they collided, the second would have become … (2). Lower-casing the taken set matters: Excel considers Leeds and LEEDS the same sheet. The rules themselves are covered in Fix Invalid Worksheet Name Errors When Writing Excel.

Step 2 — The quick way: values with pandas

When formatting does not matter, read each source with pandas and write each frame to its own sheet:

import pandas as pd

with pd.ExcelWriter("binder_values.xlsx", engine="openpyxl") as xw:
    for f, tab in names.items():
        pd.read_excel(f, sheet_name="Sales").to_excel(xw, sheet_name=tab, index=False)
with pd.ExcelFile("binder_values.xlsx") as xf:
    print(xf.sheet_names)

This is short and fast, but it loses everything except values: the teal header, column widths and number formats are gone, and the totals row is worse than gone — the source files were saved by openpyxl without calculated results, so the SUM cells read as NaN. Even with cached results, pandas would copy the number, not the formula. Use this route for data dumps, not for files people will read.

Step 3 — The faithful way: copy cells and styles with openpyxl

openpyxl's copy_worksheet only works inside one workbook, so copying between workbooks means copying cell by cell — value, style, then the sheet-level settings:

from copy import copy
from openpyxl import Workbook, load_workbook

def copy_sheet(src, dst):
    for row in src.iter_rows():
        for cell in row:
            if type(cell).__name__ == "MergedCell":
                continue
            new = dst.cell(cell.row, cell.column, cell.value)
            if cell.has_style:
                new.font, new.fill, new.border = copy(cell.font), copy(cell.fill), copy(cell.border)
                new.alignment, new.protection = copy(cell.alignment), copy(cell.protection)
                new.number_format = cell.number_format
    for key, dim in src.column_dimensions.items():
        dst.column_dimensions[key].width = dim.width
    for key, dim in src.row_dimensions.items():
        if dim.height:
            dst.row_dimensions[key].height = dim.height
    for rng in src.merged_cells.ranges:
        dst.merge_cells(str(rng))
    dst.freeze_panes = src.freeze_panes
    dst.sheet_properties.tabColor = src.sheet_properties.tabColor

binder = Workbook()
binder.remove(binder.active)
for f, tab in names.items():
    src = load_workbook(f)["Sales"]
    copy_sheet(src, binder.create_sheet(tab))
binder.save("binder.xlsx")
check = load_workbook("binder.xlsx")
ws = check["Manchester Central"]
print(check.sheetnames)
print([c.value for c in ws[ws.max_row]], ws["A1"].fill.fgColor.rgb, ws.column_dimensions["A"].width)

Each tab now has the teal header, the column widths and number formats — and the total row still holds =SUM(B2:B3), a live formula, because the cell's value is the formula text when the workbook is loaded without data_only. Formulas that refer to cells on the same sheet keep working after the copy. The six style attributes are copied one by one on purpose. The shortcut that works inside a single workbook — new._style = copy(cell._style) — copies a set of index numbers into the source workbook's style tables; in another workbook those numbers point at styles that do not exist, and reading the cell's fill then fails with IndexError: list index out of range (or, worse, silently picks up a different style). Within one workbook the shortcut is fine, as explained in Copy Cell Styles with openpyxl.

Step 4 — Sources with more than one sheet

Some sources carry extra sheets — notes, a lookup list, a second month. Copy every sheet and prefix its tab name with the source, still within the 31-character budget:

binder = Workbook()
binder.remove(binder.active)
taken = set()
for f in files:
    wb = load_workbook(f)
    for src in wb.worksheets:
        label = f.stem if len(wb.worksheets) == 1 else f"{f.stem} - {src.title}"
        copy_sheet(src, binder.create_sheet(sheet_name(label, taken)))
binder.save("binder_all.xlsx")
print(load_workbook("binder_all.xlsx").sheetnames)

The Leeds North workbook contributes two tabs, Leeds North - Sales and Leeds North - Notes; every other source contributes one tab named after the file alone. Formulas that point at another sheet of the source — =Notes!A1 — will now point at a sheet called Notes in the binder, which does not exist. Rewrite such references with the new tab names, or convert them to values before copying, as in Convert Excel Formulas to Values with Python.

Step 5 — Add an index sheet

A binder with dozens of tabs needs a front page. Insert a first sheet listing every tab with a link to it and the file it came from:

from openpyxl.styles import Font

binder = load_workbook("binder.xlsx")
index = binder.create_sheet("Index", 0)
index.append(["Sheet", "Source file"])
index["A1"].font = index["B1"].font = Font(bold=True)
for f, tab in names.items():
    index.append([tab, f.name])
    cell = index.cell(index.max_row, 1)
    cell.hyperlink = f"#'{tab}'!A1"
    cell.style = "Hyperlink"
index.column_dimensions["A"].width = 34
index.column_dimensions["B"].width = 52
binder.active = 0
binder.save("binder.xlsx")
print(load_workbook("binder.xlsx").sheetnames[:2])

The #'Tab'!A1 form is an internal link; the single quotes are required whenever a sheet name contains spaces or punctuation, which most cleaned file names do. Setting binder.active = 0 makes the file open on the index. For a richer contents page, see Add a Table of Contents Sheet with Hyperlinks in Excel.

Common pitfalls

• Invalid or duplicate sheet names. Clean, truncate to 31 characters and de-duplicate case-insensitively before creating the sheet.
• Expecting pandas to keep formatting. to_excel writes values; use the openpyxl copy for anything people will read.
• Copying _style across workbooks. Style indices only mean something in the workbook they came from; copy font, fill, border, alignment, protection and number format individually.
• Cross-sheet formulas. References to other sheets of the source break in the binder; rewrite them or freeze them as values.
• What the copy leaves behind. Charts, images, conditional formatting and data validation are not copied by the cell loop; copy conditional formats with dst.conditional_formatting = src.conditional_formatting only when the ranges are unchanged, and re-create charts.
• Lock files in the folder. Skip ~$ files that appear while a source is open, as in Ignore Excel Lock Files When Processing a Folder.

Performance notes

The cell-by-cell copy is linear in the number of cells: a few thousand rows per source copy in well under a second each. For very large sources, copy values with pandas and re-apply a standard style to the header and number formats afterwards, which is faster than copying every cell's style. Load sources with load_workbook(f) rather than read_only=True, because read-only worksheets do not expose column widths and merged ranges.

Conclusion

To merge workbooks into one file with a tab per source, first turn file names into valid, unique, 31-character sheet names. Copy values with pandas when formatting does not matter; otherwise copy each cell's value and style with openpyxl, along with widths, heights, merges and freeze panes, which keeps same-sheet formulas live. Prefix tab names for multi-sheet sources, fix cross-sheet references, and finish with a hyperlinked index sheet so readers can find their way.

Frequently asked questions

How do I combine multiple Excel files into one workbook with separate sheets? Loop over the files, create a sheet per file in a new workbook, and copy each source sheet into it — with pd.read_excel and to_excel(sheet_name=...) for values only, or cell by cell with openpyxl to keep formatting.

Can openpyxl copy a sheet from one workbook to another? Not directly; copy_worksheet only copies within the same workbook. Copy each cell's value and _style into a sheet of the target workbook, then copy column widths, row heights, merged ranges and freeze panes.

How do I make a file name a valid Excel sheet name? Replace the characters : * ? / \ with spaces, trim leading and trailing apostrophes, cut the result to 31 characters, and add a counter if the name (compared case-insensitively) is already taken.

Do formulas still work after copying sheets into one workbook? Formulas that refer to cells on the same sheet keep working, because their text is copied unchanged. References to other sheets of the source workbook break unless those sheets are copied too and keep the same names.

Related

• Up one level: Working with Multiple Excel Sheets in Python — reading, combining and splitting sheets.
• Copy a Sheet Between Excel Workbooks with Python — the single-sheet version in depth.
• Combine Multiple Excel Files into One with Python — stacking rows into one table instead.
• Save Each Sheet of a Workbook as a Separate File — the reverse operation.