Rename, Reorder and Delete Excel Sheets with openpyxl

Sheet housekeeping looks like the least risky code in a reporting script and quietly is not. A rename can break every formula that referenced the old name, a deletion can invalidate a chart's series, and both fail in ways that only appear when someone opens the file in Excel — long after the job reported success.

None of that is hard to avoid, but it does need knowing. This guide covers the naming rules Excel enforces, reordering, the difference between deleting and hiding, and choosing which tab a reader lands on. It is part of Working with Multiple Excel Sheets in Python.

Prerequisites

pip install openpyxl

Step 1: A workbook to tidy

from openpyxl import Workbook

wb = Workbook()
wb.active.title = "Sheet1"
for name in ["Mar", "Jan", "Feb", "_scratch", "old_notes"]:
    wb.create_sheet(name)

wb["Jan"]["A1"] = 120
wb["Feb"]["A1"] = 131
wb["Mar"]["A1"] = 140
wb["Sheet1"]["A1"] = "=Jan!A1+Feb!A1+Mar!A1"
wb.save("messy.xlsx")

print(wb.sheetnames)
# ['Sheet1', 'Mar', 'Jan', 'Feb', '_scratch', 'old_notes']

Step 2: Rename safely

Assign to .title, but sanitise first — openpyxl will write an illegal name and Excel will then refuse to open the file:

import re

ILLEGAL = re.compile(r"[\\/?*\[\]:]")


def safe_title(name, existing=(), limit=31):
    """Return a legal, unique sheet title."""
    clean = ILLEGAL.sub("-", str(name)).strip() or "Sheet"
    if clean.lower() == "history":
        clean = "History data"
    clean = clean[:limit]

    if clean.lower() not in {e.lower() for e in existing}:
        return clean
    stem = clean[: limit - 4]
    for n in range(2, 100):
        candidate = f"{stem} ({n})"
        if candidate.lower() not in {e.lower() for e in existing}:
            return candidate
    raise ValueError(f"cannot make a unique title from {name!r}")


from openpyxl import load_workbook

wb = load_workbook("messy.xlsx")
wb["Sheet1"].title = safe_title("Summary", wb.sheetnames)
wb.save("messy.xlsx")

Truncating to 31 characters is the rule that bites in generated reports, because a name built from a customer or a period easily exceeds it — and a title that is silently cut can collide with another that was cut to the same prefix, which is why the uniqueness check runs after the truncation rather than before.

Step 3: Fix the formulas the rename broke

Excel rewrites references when it renames a tab. openpyxl does not, so a formula that said =Jan!A1+Feb!A1 still says exactly that even if Jan is now January:

def rename_sheet(wb, old, new):
    """Rename a sheet and repoint formula references that named it."""
    new = safe_title(new, [n for n in wb.sheetnames if n != old])
    wb[old].title = new

    quoted_old, quoted_new = f"'{old}'!", f"'{new}'!"
    plain_old, plain_new = f"{old}!", f"{new}!"

    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("="):
                    updated = (cell.value
                               .replace(quoted_old, quoted_new)
                               .replace(plain_old, plain_new))
                    if updated != cell.value:
                        cell.value = updated
    return new

This is a text substitution, so treat it as a best effort rather than a parser: a sheet named Q1 will also match inside a name like Q1 detail!, and a formula referencing another workbook will not be handled. For anything beyond simple names, the safer approach is to avoid renaming at all — decide the sheet names when the workbook is created, which is entirely within your control in a generated report.

Charts and defined names hold their own references and are not touched by the loop above, so a chart whose series pointed at the old name will need rebuilding.

Step 4: Reorder the tabs

move_sheet shifts a sheet by an offset, and wb._sheets holds the order. Working with the public method and a target list is the readable way to impose an order:

def order_sheets(wb, preferred):
    """Put the named sheets first, in the order given; leave the rest behind them."""
    for position, name in enumerate(preferred):
        if name not in wb.sheetnames:
            continue
        current = wb.sheetnames.index(name)
        wb.move_sheet(name, offset=position - current)
    return wb.sheetnames


wb = load_workbook("messy.xlsx")
print(order_sheets(wb, ["Summary", "Jan", "Feb", "Mar"]))
# ['Summary', 'Jan', 'Feb', 'Mar', '_scratch', 'old_notes']
wb.save("messy.xlsx")

move_sheet takes an offset, not a destination index, which is the most common source of confusion — moving a sheet to position 0 means passing a negative offset equal to its current index. Computing the offset from the current position, as above, sidesteps having to think about it.

Tab order is not just cosmetic in a multi-sheet report: readers open the first tab, and the summary belongs there. That is the ordering Add a Summary Sheet to an Excel Report in Python assumes.

Step 5: Delete, or hide

Deleting is del wb[name] or wb.remove(ws). Both are unconditional, and both will break anything that referenced the sheet:

wb = load_workbook("messy.xlsx")

for name in ["_scratch", "old_notes"]:
    if name in wb.sheetnames:
        del wb[name]

if len(wb.sheetnames) == 0:
    raise RuntimeError("a workbook must keep at least one sheet")
wb.save("messy.xlsx")

A workbook with no sheets is not a valid file, so guard the last one. When something might still point at the sheet — a formula, a chart series, a named range, a pivot cache — hide it instead:

wb["Working"].sheet_state = "hidden"        # visible in the unhide dialog
wb["Internal"].sheet_state = "veryHidden"   # only reachable from the VBA editor

hidden is the right choice for a working sheet a curious reader may legitimately want to see. veryHidden is for a sheet that supports the workbook's machinery — a lookup table behind a dropdown, say — where an accidental edit would break something. Neither is a security measure: both are trivially reversible by anyone who knows where to look.

Step 6: Choose the tab that opens

The sheet a reader lands on is a workbook-level index:

wb = load_workbook("messy.xlsx")
wb.active = wb.sheetnames.index("Summary")     # an index, not a sheet object
wb["Summary"].sheet_view.tabSelected = True
for ws in wb.worksheets:
    if ws.title != "Summary":
        ws.sheet_view.tabSelected = False
wb.save("messy.xlsx")

Assigning a worksheet object to wb.active is the mistake people make; it wants the index. Clearing tabSelected on the others matters too — a workbook saved with several sheets marked selected opens with a multi-sheet selection, and anything the reader then types goes into all of them at once.

Common pitfalls and gotchas

Performance and scale notes

These operations are metadata changes, so they cost nothing regardless of how much data the sheets hold — with one exception. The formula-repointing loop in step 3 visits every cell in the workbook, which on a large file is the slowest thing on this page; restrict it to the sheets that could plausibly reference the renamed one if that matters.

Note also that load_workbook(read_only=True) cannot be used for any of this: read-only mode gives you cell values, not a mutable workbook. Housekeeping needs a normal load, which means the whole workbook is parsed into memory first.

Conclusion

Sanitise every generated sheet name to 31 legal characters and check uniqueness after truncating, because Excel enforces both and openpyxl does not. Reorder with computed offsets so the summary is the first tab, set wb.active by index and clear the selection on the rest. Prefer hiding to deleting whenever a formula, chart or named range might still point at the sheet — a deletion is invisible to your script and obvious to whoever opens the file.

Frequently asked questions

What are the rules for a sheet name? One to 31 characters, and none of \ / ? * [ ] :. It cannot be blank, cannot be "History", and must be unique within the workbook — Excel refuses to open a file that breaks any of these.

Does renaming a sheet break formulas that reference it? Yes, if they are in the file as text. Excel rewrites references when you rename a tab in the application; openpyxl does not, so a formula reading 'Q1'!B2 keeps pointing at a sheet that no longer exists.

How do I control which tab is selected when the file opens? Set wb.active to the index of the sheet you want. It is an index, not a sheet object, which is the usual source of confusion.

Should I delete a sheet or hide it? Hide it when anything might still reference it — a formula, a chart series, a named range. Deletion breaks all three, and a hidden sheet keeps the workbook working while staying out of the way.

Related

Up to the parent guide:

• Working with Multiple Excel Sheets in Python — the rest of the multi-sheet toolkit.

Related guides:

• Copy a Sheet Between Excel Workbooks with Python — the operation that usually precedes this cleanup.
• Read All Sheets from an Excel File into DataFrames — filtering out the sheets this guide hides.
• Create a Named Range in Excel with openpyxl — one of the references a deletion breaks.
• Add a Summary Sheet to an Excel Report in Python — why the first tab matters.