Fix Invalid Worksheet Name Errors When Writing Excel

Scripts that make one sheet per group — per region, per customer, per month — hand data to Excel as sheet names, and data does not respect Excel's rules. The first customer called "Smith / Jones Ltd" stops the job:

ValueError: Invalid character / found in sheet title

With xlsxwriter the same name raises InvalidWorksheetName, and a month label like "2026: March" fails on the colon. Other rules fail more quietly: openpyxl accepts a 40-character name with only a warning and writes a file that breaks Excel's length limit, and it renames a case-insensitive duplicate without telling you. This guide, part of Troubleshooting Common Python Excel Errors, lists the rules, shows how each library reacts to breaking them, and ends with one function that turns any label into a safe, unique sheet name.

Prerequisites

pip install pandas openpyxl xlsxwriter

import warnings
import pandas as pd
from openpyxl import Workbook
import xlsxwriter

Step 1 — See how each library reacts

The three writers you are likely to use enforce the rules differently. Run the same awkward names through openpyxl and xlsxwriter and record what happens:

names = ["Q1/Q2", "Sales: EU", "A" * 32, "'quoted", "Sales", "sales", ""]

wb = Workbook()
for name in names:
    try:
        with warnings.catch_warnings(record=True) as caught:
            warnings.simplefilter("always")
            ws = wb.create_sheet(name)
        note = f" (warning: {caught[0].message})" if caught else ""
        print(f"openpyxl  {name!r:40} -> {ws.title!r}{note}")
    except ValueError as exc:
        print(f"openpyxl  {name!r:40} -> ValueError: {exc}")

book = xlsxwriter.Workbook("probe.xlsx")
for name in names:
    try:
        print(f"xlsxwriter {name!r:39} -> {book.add_worksheet(name).name!r}")
    except Exception as exc:
        print(f"xlsxwriter {name!r:39} -> {type(exc).__name__}")
book.close()

The output is the whole story in a few lines. openpyxl raises for forbidden characters, warns but accepts the 32-character name, accepts the leading apostrophe, renames sales to sales1 because Sales exists, and turns the empty name into Sheet1-style default. xlsxwriter is strict: it raises InvalidWorksheetName for the characters, the length and the apostrophe, and DuplicateWorksheetName for the case-insensitive clash. pandas passes sheet_name straight to whichever engine it is using, so to_excel inherits that engine's behaviour.

The quiet cases are the dangerous ones. A 32-character name written by openpyxl breaks a limit Excel enforces for every sheet it creates itself, and openpyxl's own warning says as much: some applications may not be able to read the file. The silent rename is worse for automation: code that later does wb["sales"] gets the first sheet, Sales, because lookups ignore case too.

Step 2 — Clean the characters first

Replace each forbidden character with something readable, or drop it where it adds nothing. A hyphen works for slashes, which keeps "Q1/Q2" and "North / East" recognisable; square brackets can become round ones; colons, asterisks and question marks carry little meaning in a label and can simply go:

import re

REPLACE = {"[": "(", "]": ")", "/": "-", "\\": "-", ":": "", "*": "", "?": ""}

def clean_chars(label):
    text = "".join(REPLACE.get(ch, ch) for ch in str(label))
    return re.sub(r"\s+", " ", text).strip(" '")

for raw in ["Q1/Q2", "Sales: EU", "[Draft] plan", "'quoted'", "  spaced   out  "]:
    print(repr(raw), "->", repr(clean_chars(raw)))

Stripping apostrophes from both ends happens here too, because Excel uses them to quote sheet names inside formulas (='Sales EU'!A1). An apostrophe inside a name is legal and kept, so "O'Brien Ltd" stays as it is. Collapsing runs of whitespace is not required by Excel, but labels from data often carry double spaces or trailing tabs that would otherwise produce two sheets whose names look identical in the tab bar and differ only in invisible characters — the kind of problem that takes an hour to spot by eye and a second to prevent in code.

Step 3 — Truncate, then make the name unique

Truncating must happen after cleaning — cleaning can change the length — and uniqueness must be checked after truncating, because two long names that differ only in their last characters become identical at 31. Keep a set of lower-cased names already used, and add a counter suffix when a name collides, shortening the base so the suffix still fits:

def safe_sheet_name(label, used, fallback="Sheet"):
    base = clean_chars(label) or fallback
    if base.lower() == "history":
        base = "History data"
    name = base[:31]
    n = 2
    while name.lower() in used:
        suffix = f" ({n})"
        name = base[: 31 - len(suffix)] + suffix
        n += 1
    used.add(name.lower())
    return name

used = set()
labels = ["North / East", "north / east", "Customer accounts receivable ageing 2026",
          "Customer accounts receivable ageing 2027", "", "History"]
for label in labels:
    print(f"{label!r:45} -> {safe_sheet_name(label, used)!r}")

The two long "ageing" labels show why the order matters: both truncate to the same 31 characters, and only the uniqueness step tells them apart. When the counter suffix makes a name less informative than you would like, keep a mapping from the original label to the sheet name — Step 5 uses it to build an index sheet.

Step 4 — Use it when writing one sheet per group

The function slots straight into the usual pandas pattern. The used set lives for the whole writer, so names are unique across every sheet in the file:

orders = pd.DataFrame({
    "customer": ["Smith / Jones Ltd", "smith / jones ltd", "A&B: Wholesale", "Northwind"],
    "amount": [120, 80, 45, 300],
})

used, index = set(), []
with pd.ExcelWriter("by_customer.xlsx", engine="xlsxwriter") as writer:
    for customer, group in orders.groupby("customer", sort=False):
        name = safe_sheet_name(customer, used)
        group.to_excel(writer, sheet_name=name, index=False)
        index.append({"customer": customer, "sheet": name})

print(pd.ExcelFile("by_customer.xlsx").sheet_names)

Using xlsxwriter here is a deliberate choice: because it raises on every broken rule, a bug in the naming function shows up as an error instead of as a file Excel quietly repairs. With openpyxl as the engine, the same code works, but a mistake would slip through. Create a Sheet for Each Group in a DataFrame covers the grouping side in more depth.

Step 5 — Keep the original labels readable

Once names are cleaned and truncated, the sheet tab no longer tells a reader the full customer name. Put the mapping on a first sheet so nothing is lost, with links to each tab:

from openpyxl import load_workbook

wb = load_workbook("by_customer.xlsx")
toc = wb.create_sheet("Index", 0)
toc.append(["Customer", "Sheet"])
for row, item in enumerate(index, start=2):
    toc.cell(row=row, column=1, value=item["customer"])
    link = toc.cell(row=row, column=2, value=item["sheet"])
    link.hyperlink = f"#'{item['sheet']}'!A1"
    link.style = "Hyperlink"
wb.save("by_customer.xlsx")
print(load_workbook("by_customer.xlsx").sheetnames)

Note the quotes in the link target: '{sheet}'!A1 is how Excel refers to a sheet whose name contains spaces or punctuation, the same quoting that makes leading and trailing apostrophes illegal. Add a Table of Contents Sheet with Hyperlinks in Excel builds a fuller version.

Common pitfalls

• Truncating before cleaning. Replacing characters can change the length; always clean, then cut to 31.
• Checking uniqueness case-sensitively. Excel does not, so "Sales" != "sales" in Python is not a useful test. Compare lower-cased names.
• Relying on openpyxl's automatic rename. sales1 is unique but unexpected, and a later wb["sales"] returns the other sheet.
• Forgetting existing sheets. When adding sheets to a workbook that already has some, seed used with {n.lower() for n in wb.sheetnames} first.
• Using sheet names as data keys. The name is a display label that may be shortened; keep the real key in the data or the index sheet.

Performance notes

Name handling is negligible next to writing the data. The only scaling concern is the uniqueness loop, which is linear in the number of collisions for one base name — irrelevant for the dozens or hundreds of sheets a workbook can reasonably hold. If you find yourself generating thousands of sheets, the report probably wants one sheet with a filter instead; see Split One Excel Sheet into Multiple Files by Column Value for the separate-files alternative.

Conclusion

Sheet names must be non-empty, free of [ ] : * ? / \, without an apostrophe at either end, at most 31 characters and unique ignoring case. openpyxl enforces only the character rule strictly and quietly works around the others; xlsxwriter raises on all of them. Clean characters, truncate, then de-duplicate case-insensitively in one function, write with an engine that fails loudly, and keep an index sheet that maps each tab back to the full label.

Frequently asked questions

What characters are not allowed in an Excel sheet name? The seven characters : * ? / and \ are forbidden. A name also cannot be empty, cannot be longer than 31 characters, and cannot start or end with an apostrophe.

Why does openpyxl accept a 40-character sheet name? openpyxl only warns ("Title is more than 31 characters") and writes the long name anyway, producing a file that breaks a limit Excel enforces. Truncate names yourself so the file stays within the rules.

Are sheet names case-sensitive? No. Excel treats "Sales" and "sales" as the same name. xlsxwriter raises DuplicateWorksheetName, and openpyxl silently renames the second sheet to "sales1", which can break code that looks the sheet up later.

Can I call a sheet History? Avoid it. Excel reserves the name History for its change-tracking feature and will not let a user give a sheet that name, so a generated sheet called History is an accident waiting to happen. Rename it, for example to "History data".

Related

• Up one level: Troubleshooting Common Python Excel Errors — other errors raised while writing.
• Rename, Reorder and Delete Excel Sheets with openpyxl — managing sheets after they exist.
• Fix "Worksheet Does Not Exist" KeyError in openpyxl — the lookup side of sheet names.
• Generate One Excel Report per Region in a Loop — the same naming problem for file names.