Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Working with Multiple Excel Sheets in Python

Read, transform, and write multi-sheet Excel workbooks with pandas and openpyxl — load all tabs into a dict, merge across sheets, and export to named sheets.

A single workbook usually holds several related tables — sales on one tab, returns on another, a summary on a third. Working with multiple sheets means loading the tabs you need, joining or aggregating across them, and writing results back to clearly named sheets. It is a core skill in getting started with Python Excel automation: once you can move data between tabs freely, most reporting jobs become a short script. pandas handles the tabular work — the same reading Excel files with pandas call you already know grows one argument to load every tab — and openpyxl is the engine underneath for .xlsx. This guide walks the full cycle on a sample workbook the first step creates, so every snippet runs in order.

Several sheets funnel into one combined table Three separate worksheets — Sales, Returns, and Summary — are read with pandas and aggregated into a single combined DataFrame. Sheets in one workbook Sales Returns Regions pandas read + merge Combined one DataFrame

Step 1: Install the toolchain

Bash
pip install pandas openpyxl

openpyxl is the engine pandas uses to read and write .xlsx. pandas does not ship it, so install both.

Step 2: Create a multi-sheet workbook

Write three DataFrames to three sheets with a single ExcelWriter. Each to_excel call with a different sheet_name adds a tab:

Python
import pandas as pd

sales = pd.DataFrame({
    "product_id": [1, 2, 3, 4],
    "product": ["Widget", "Gadget", "Gizmo", "Doohickey"],
    "units": [120, 80, 45, 30],
    "unit_price": [9.99, 14.50, 22.00, 5.25],
})
returns = pd.DataFrame({
    "product_id": [1, 3],
    "return_units": [10, 5],
})
regions = pd.DataFrame({
    "product_id": [1, 2, 3, 4],
    "region": ["North", "South", "West", "East"],
})

with pd.ExcelWriter("workbook.xlsx", engine="openpyxl") as writer:
    sales.to_excel(writer, sheet_name="Sales", index=False)
    returns.to_excel(writer, sheet_name="Returns", index=False)
    regions.to_excel(writer, sheet_name="Regions", index=False)

print("Created workbook.xlsx with 3 sheets")

Step 3: List the sheets without loading data

pd.ExcelFile opens the workbook once and exposes sheet_names without parsing any rows — useful for validating a file before you commit to reading it:

Python
xls = pd.ExcelFile("workbook.xlsx")
print("Sheets:", xls.sheet_names)

Step 4: Read all sheets into a dictionary

Pass sheet_name=None to get a dict mapping each sheet name to its DataFrame. Iteration order matches the workbook:

Python
all_sheets = pd.read_excel("workbook.xlsx", sheet_name=None)
print(type(all_sheets), list(all_sheets.keys()))

for name, frame in all_sheets.items():
    print(f"{name}: {frame.shape[0]} rows x {frame.shape[1]} cols")

Step 5: Read only the sheets you need

Loading every tab is wasteful when you need a few. Pass a single name for one DataFrame, or a list of names for a dict of just those sheets:

Python
# One sheet -> one DataFrame
sales_only = pd.read_excel("workbook.xlsx", sheet_name="Sales")

# A subset -> a dict of just those sheets
subset = pd.read_excel("workbook.xlsx", sheet_name=["Sales", "Returns"])
print("Loaded subset:", list(subset.keys()))

The one thing to keep straight is that the value you pass as sheet_name decides the type you get back — a bare name returns a DataFrame, while None or a list returns a dict:

How the sheet_name argument maps to the return type Passing a single sheet name to pd.read_excel returns one DataFrame; passing None returns a dict of every sheet; passing a list of names returns a dict of just that subset. The sheet_name value decides the return type sheet_name="Sales" DataFrame that one sheet's rows sheet_name=None dict[str, DataFrame] every tab, in workbook order sheet_name=["Sales", "Returns"] dict[str, DataFrame] just the named subset

When individual sheets need different parsing — custom headers, skipped metadata rows, parsed dates — apply those per-sheet options. See How to Read Excel With Pandas: Step by Step for header, skiprows, and parse_dates.

Step 6: Merge data across sheets

The dict from Step 4 lets you join tabs in memory with the same merge and join operations you would use on separate files. Here we attach return quantities and region to sales, then compute net revenue. A left join keeps every sales row; fillna handles products with no returns:

Python
sales_df = all_sheets["Sales"]
returns_df = all_sheets["Returns"]
regions_df = all_sheets["Regions"]

merged = (
    sales_df
    .merge(returns_df, on="product_id", how="left")
    .merge(regions_df, on="product_id", how="left")
)
merged["return_units"] = merged["return_units"].fillna(0)
merged["net_revenue"] = merged["unit_price"] * (merged["units"] - merged["return_units"])

print(merged[["product", "region", "units", "return_units", "net_revenue"]])

Step 7: Build a summary sheet

Aggregate the merged data — for example, net revenue per region — to drive a summary tab:

Python
summary = (
    merged.groupby("region", as_index=False)["net_revenue"]
    .sum()
    .sort_values("net_revenue", ascending=False)
)
print(summary)

Step 8: Write multiple DataFrames to named sheets

Use one ExcelWriter so all sheets land in a single file — the multi-sheet extension of writing DataFrames to Excel with pandas. mode="w" (the default) creates a fresh workbook; each to_excel writes a named tab. The same writer exposes the underlying openpyxl worksheets through writer.sheets, so you can auto-fit columns in the same pass:

Python
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    merged.to_excel(writer, sheet_name="Detail", index=False)

    # Auto-fit column widths on every sheet
    for ws in writer.sheets.values():
        for column_cells in ws.columns:
            width = max(len(str(c.value)) for c in column_cells if c.value is not None)
            ws.column_dimensions[column_cells[0].column_letter].width = width + 2

print("Wrote report.xlsx with Summary and Detail sheets")

Step 9: Append a sheet to an existing workbook

To add a tab to a workbook that already exists, open the writer with mode="a" — appending relies on openpyxl's ability to edit an existing file in place, which is why the engine must be openpyxl here. In pandas 3.0 you must set if_sheet_exists to say what happens on a name clash — "replace", "overlay", or "error" (the default):

Python
audit = pd.DataFrame({
    "step": ["load", "merge", "summarize", "export"],
    "status": ["ok", "ok", "ok", "ok"],
})

with pd.ExcelWriter("report.xlsx", engine="openpyxl",
                    mode="a", if_sheet_exists="replace") as writer:
    audit.to_excel(writer, sheet_name="Audit", index=False)

print("Appended Audit sheet; now:", pd.ExcelFile("report.xlsx").sheet_names)

Processing large workbooks sheet by sheet

sheet_name=None materializes every tab at once. For very large workbooks, read one sheet at a time and reduce each before moving on, so only one sheet's data sits in memory:

Python
target_sheets = ["Sales", "Returns"]
results = {}
for sheet in target_sheets:
    df = pd.read_excel("workbook.xlsx", sheet_name=sheet)
    # Reduce immediately — keep only what the report needs
    results[sheet] = df.head(100).copy()

print("Processed sequentially:", list(results.keys()))

Common errors and fixes

ErrorCauseFix
ValueError: Excel file format cannot be determinedWrong/missing extension or a renamed non-Excel filePass engine="openpyxl" for .xlsx; confirm the file is real .xlsx
KeyError: 'Sales'Sheet name case or whitespace mismatchCheck pd.ExcelFile(path).sheet_names; strip names before lookup
ValueError: Sheet 'X' already exists ...Appending in mode="a" without conflict handlingPass if_sheet_exists="replace" or "overlay"
Merge produced unexpected NaNJoin keys differ in name or type across sheetsRename to a common key and align dtypes before merge

Choosing a layout: many sheets or one column

Splitting data across sheets is a presentation decision, and it has a cost. A single sheet with a Region column can be filtered, pivoted and charted; twelve regional sheets have to be reassembled before any of that is possible.

One sheet with a category column versus one sheet per category A single sheet with a Region column supports filtering, pivoting and charting directly. Separate sheets per region suit distribution and per-team ownership but must be concatenated before any analysis. one sheet + Region column Region Revenue filter · pivot · chart, immediately one sheet per region North South West each team sees only its tab easy to distribute must be concatenated to analyse

The practical rule is to keep one long sheet as the source of truth and generate the split view when distribution requires it. Going the other way — reconstructing the long form from twelve tabs every month — is where the bugs live, because a renamed tab or an extra blank sheet quietly changes the result.

Python
import pandas as pd

long = pd.read_excel("orders.xlsx", sheet_name="Orders")

with pd.ExcelWriter("by_region.xlsx", engine="openpyxl") as writer:
    long.to_excel(writer, sheet_name="All", index=False)         # keep the source of truth
    for region, block in long.groupby("Region", sort=True):
        block.to_excel(writer, sheet_name=str(region)[:31], index=False)

The [:31] slice is not decoration: Excel rejects sheet names longer than 31 characters, and the error arrives at save time rather than when the name is set.

Sheet names have rules

Generated tab names are a reliable source of save-time failures, because the constraints are easy to forget and the data rarely respects them:

Python
import re

INVALID = r'[\\/*?:\[\]]'

def safe_sheet_name(name, used=None, limit=31):
    cleaned = re.sub(INVALID, "-", str(name)).strip() or "Sheet"
    cleaned = cleaned[:limit]
    if used is None:
        return cleaned
    candidate, suffix = cleaned, 2
    while candidate.lower() in {u.lower() for u in used}:
        tail = f"_{suffix}"
        candidate = cleaned[: limit - len(tail)] + tail
        suffix += 1
    used.add(candidate)
    return candidate

used = set()
print([safe_sheet_name(n, used) for n in ["North/South", "North/South", "A very long regional name here"]])

Five characters are banned outright, the limit is 31, names are compared case-insensitively, and a duplicate raises. Running every generated name through one function removes the whole category of problem, and the deduplicating suffix means two regions that clean to the same string still both appear.

Reading many sheets back safely

Reassembling a multi-tab workbook is where hidden sheets, summary tabs and blank leftovers cause trouble. Filter deliberately rather than trusting sheet_name=None:

Python
import pandas as pd
from openpyxl import load_workbook

SKIP = {"Summary", "Notes", "Lookups"}

def data_sheets(path):
    wb = load_workbook(path, read_only=True)
    names = [
        ws.title for ws in wb.worksheets
        if ws.sheet_state == "visible" and ws.title not in SKIP and ws.max_row > 1
    ]
    wb.close()
    return names

frames = []
for name in data_sheets("by_region.xlsx"):
    frame = pd.read_excel("by_region.xlsx", sheet_name=name)
    frames.append(frame.assign(Sheet=name))

combined = pd.concat(frames, ignore_index=True)
print(len(frames), "sheet(s) ->", combined.shape)

Checking sheet_state excludes tabs someone hid deliberately — usually working notes that would otherwise arrive as data. Adding the sheet name as a column preserves the context the layout was carrying, and dropping sheets with a single row skips the empty template tabs that accumulate in long-lived workbooks.

The last consideration is order. wb.worksheets returns tabs in workbook order, which is meaningful when the sheets are months; sorting them alphabetically turns Feb, Jan, Mar into an ordering nobody wants. Keep the workbook order and sort explicitly on a real date column afterwards.

Order, visibility and the tab a reader lands on

A multi-sheet workbook has a shape beyond its data: which tab opens first, what order the rest sit in, and which are hidden. All three are worth setting explicitly, because the defaults reflect the order your code happened to create things in:

Python
from openpyxl import load_workbook

PREFERRED = ["Summary", "North", "South", "West", "Lookups"]

wb = load_workbook("by_region.xlsx")

order = [name for name in PREFERRED if name in wb.sheetnames]
order += [name for name in wb.sheetnames if name not in order]      # anything unexpected, last
wb._sheets = [wb[name] for name in order]

if "Lookups" in wb.sheetnames:
    wb["Lookups"].sheet_state = "hidden"        # reference data, out of the way

wb.active = 0                                    # open on the Summary
for ws in wb.worksheets:
    ws.sheet_view.showGridLines = ws.title != "Summary"

wb.save("by_region_ordered.xlsx")
print(wb.sheetnames)

Appending unrecognised sheets rather than dropping them matters: a tab that appears because a new region was added should still be in the file, just not promoted ahead of the summary. Hiding the lookup tab keeps the workbook tidy while leaving its data available to formulas — a hidden sheet is fully readable, which is why "veryHidden" should be reserved for things a user must never restore.

Turning gridlines off on the summary is a small touch with a large effect: it is the difference between a page that reads as a report and one that reads as a spreadsheet someone forgot to finish.

Linking the tabs together

A workbook of fifteen tabs needs navigation. Two hyperlinks — one from the summary to each detail sheet, one back — take a few lines and save readers a great deal of scrolling:

Python
from openpyxl import load_workbook
from openpyxl.styles import Font

LINK = Font(color="0563C1", underline="single")

wb = load_workbook("by_region_ordered.xlsx")
summary = wb["Summary"]

row = 2
for name in [n for n in wb.sheetnames if n not in {"Summary", "Lookups"}]:
    cell = summary.cell(row=row, column=1, value=name)
    cell.hyperlink = f"#'{name}'!A1"
    cell.font = LINK

    back = wb[name].cell(row=1, column=8, value="← Summary")
    back.hyperlink = "#'Summary'!A1"
    back.font = LINK
    row += 1

wb.save("by_region_linked.xlsx")

The #'Sheet'!A1 form is an internal reference and needs the quotes whenever the name contains a space. Styling the cell as a link is manual — openpyxl sets the hyperlink but not the appearance — and skipping it produces links that work and look like ordinary text, which nobody clicks.

Splitting a workbook into separate files

Sometimes the tabs should not be tabs at all. When each region's data is confidential to that region, one file per recipient is the only safe arrangement — a hidden tab or a set filter is one click from being visible:

Python
from pathlib import Path

import pandas as pd

def split_to_files(frame, key="Region", outdir="per_region"):
    Path(outdir).mkdir(parents=True, exist_ok=True)
    written = []
    for value, block in frame.groupby(key, sort=True):
        target = Path(outdir) / f"{value}.xlsx".replace("/", "-")
        block.to_excel(target, index=False, sheet_name=str(value)[:31])
        written.append(target)
    return written

for path in split_to_files(long):
    print(path, path.stat().st_size, "bytes")

Each file contains only that group's rows, so there is nothing to reveal. It also makes delivery simpler — one attachment per recipient, well under any mail size limit — and it removes the awkward conversation that follows someone discovering another team's numbers in a workbook they were sent deliberately.

Keeping the tabs consistent

When several sheets are generated from one template of code, small divergences creep in: one tab has a frozen header and another does not, column widths differ, one is missing its number formats. A single finishing function applied to every sheet keeps them uniform:

Python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def finalise(ws, money_columns=(), freeze="A2"):
    ws.freeze_panes = freeze
    ws.sheet_view.zoomScale = 100
    for column_cells in ws.columns:
        letter = get_column_letter(column_cells[0].column)
        longest = max((len(str(c.value)) for c in column_cells if c.value is not None), default=0)
        ws.column_dimensions[letter].width = min(max(longest + 3, 9), 42)
    for letter in money_columns:
        for row in range(2, ws.max_row + 1):
            ws[f"{letter}{row}"].number_format = "#,##0.00"

wb = load_workbook("by_region_linked.xlsx")
for ws in wb.worksheets:
    if ws.title != "Lookups":
        finalise(ws, money_columns=["B"])
wb.save("by_region_final.xlsx")

The function is short, and the discipline it enforces is what makes a fifteen-tab workbook feel designed rather than assembled. It also gives you one place to change when the house style moves — the alternative being fifteen slightly different blocks of formatting code scattered through a script.

Decide the layout before you write the code

Whether the answer is one sheet, many sheets or many files is a question about the audience rather than about pandas. One sheet suits analysis, many sheets suit a team that each owns a section, and many files suit recipients who must not see each other's data. Choosing deliberately at the start saves rewriting a report that grew into the wrong shape — and it is worth writing the reason down, because the next person to touch the script will otherwise assume the layout was accidental.

Frequently asked questions

How do I load every sheet at once? Pass sheet_name=None to pd.read_excel(). You get a dict mapping each sheet name to its DataFrame, in workbook order. Pass a list of names to load just a subset as a dict.

How can I list the tabs without reading the data? Open the file with pd.ExcelFile(path) and read its sheet_names attribute. It opens the workbook once and exposes the names without parsing any rows, so you can validate a file before committing to a full read.

Why do I get ValueError: Sheet 'X' already exists when adding a tab? You opened the writer with mode="a" but didn't say what to do on a name clash. Pass if_sheet_exists="replace" or "overlay"; the default is "error", which raises.

My cross-sheet merge produced unexpected NaN — why? The join keys differ in name or dtype across sheets. Rename them to a common key and align their dtypes before calling merge, and use a left join with fillna to handle rows that have no match.

How do I process a workbook too large to hold all sheets in memory? Skip sheet_name=None. Read one sheet at a time in a loop and reduce each DataFrame before moving on, so only one sheet's data sits in memory at once.

Key takeaways

  • The pattern for multi-sheet work is always the same: read into a dict keyed by sheet name, operate in memory, and write through a single ExcelWriter.
  • Pass sheet_name=None to load every tab as a dict; pass a name or a list of names to load only what you need and keep memory low.
  • Use pd.ExcelFile(path).sheet_names to inspect a workbook's tabs before parsing any rows.
  • Join tabs with merge on a shared key, using a left join and fillna so rows without a match survive instead of turning into NaN.
  • Write every result through one writer context; append to an existing file with mode="a" plus an explicit if_sheet_exists policy, and auto-fit columns in the same pass for output that is usable without manual tweaks.