Fix "At least one sheet must be visible" in openpyxl and pandas

The script has run for a while and fails at the very end, when the workbook is saved:

IndexError: At least one sheet must be visible

or, for a workbook with a single sheet:

ValueError: The only worksheet of a workbook cannot be hidden

Excel requires every workbook to show at least one sheet, and openpyxl checks this when it writes the file. The error appears at save time, so the traceback points at save() or at the end of a with pd.ExcelWriter(...) block, not at the line that caused it. There are two very different causes: nothing was written at all, or everything written was hidden. This page shows how to tell them apart and fix each. It is part of Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl xlsxwriter

The examples use pandas with the openpyxl engine, which is where the first error most often comes from, and openpyxl directly.

Cause 1 — the ExcelWriter wrote no DataFrames

This is the common one. A report writes one sheet per region, skipping regions with no data. One day every region is empty — a holiday, a failed upstream extract, a filter that no longer matches — and the loop writes nothing:

import pandas as pd

sales = pd.DataFrame({"Region": ["North", "South"], "Sales": [0, 0]})
by_region = {r: g for r, g in sales.groupby("Region") if g["Sales"].sum() > 0}

try:
    with pd.ExcelWriter("regional.xlsx", engine="openpyxl") as writer:
        for region, frame in by_region.items():
            frame.to_excel(writer, sheet_name=region, index=False)
except IndexError as e:
    print("IndexError:", e)

pandas' openpyxl writer starts from a workbook with no sheets and adds one per to_excel call. With zero calls, the workbook has zero sheets, none of them visible, and saving fails. The error message talks about visibility, but the real problem is that the report is empty.

The failure also leaves something behind. openpyxl had already started writing the zip when it hit the check, so regional.xlsx now exists as a broken file with no workbook in it:

import os
import zipfile

print(os.path.getsize("regional.xlsx"), "bytes;", zipfile.ZipFile("regional.xlsx").namelist())
try:
    pd.read_excel("regional.xlsx")
except KeyError as e:
    print("reading it back:", type(e).__name__, e)

The zip holds a theme, styles and document properties but no workbook part, so pandas cannot even work out what kind of file it is and fails with a baffling OptionError about io.excel.zip.reader. If a later step picks up whatever is in the output folder, it sends a file nobody can open. Delete the output when saving fails, or write to a temporary name and rename on success.

With the xlsxwriter engine the same code does not fail. xlsxwriter adds a blank Sheet1 to a workbook that has none, and the empty report is written and sent:

from openpyxl import load_workbook

with pd.ExcelWriter("regional_xw.xlsx", engine="xlsxwriter") as writer:
    for region, frame in by_region.items():
        frame.to_excel(writer, sheet_name=region, index=False)
print("xlsxwriter wrote:", load_workbook("regional_xw.xlsx").sheetnames)

Neither behaviour is what you want. Decide explicitly what an empty report means: skip the file and alert someone, or write a sheet that says there was no data.

from pathlib import Path

def write_regions(path, frames):
    if not frames:
        Path(path).unlink(missing_ok=True)          # remove any broken or stale file
        print(f"no data for any region - {path} not written")
        return False
    with pd.ExcelWriter(path, engine="openpyxl") as writer:
        for name, frame in frames.items():
            frame.to_excel(writer, sheet_name=name, index=False)
    return True

write_regions("regional.xlsx", by_region)

If recipients expect a file every day, write a one-line "No sales recorded for 28 September 2026" sheet instead; either way the empty case is handled on purpose.

Cause 2 — every sheet is hidden

Reports often hide their data sheets and show a summary. If the summary is removed, renamed away or never created, only hidden sheets remain. With two or more sheets openpyxl raises the IndexError; with one, the ValueError:

from openpyxl import Workbook

wb = Workbook()
summary = wb.active
summary.title = "Summary"
data = wb.create_sheet("Data")
data.sheet_state = "hidden"
lookup = wb.create_sheet("Lookup")
lookup.sheet_state = "hidden"
wb.remove(summary)                     # e.g. a template step that rebuilds the summary later
try:
    wb.save("hidden_only.xlsx")
except IndexError as e:
    print("IndexError:", e)

single = Workbook()
single.active.sheet_state = "hidden"
try:
    single.save("single_hidden.xlsx")
except ValueError as e:
    print("ValueError:", e)

Before saving, check the sheet states and make one sheet visible — ideally the one readers should land on — and make it active:

def ensure_visible(wb, preferred=None):
    """Make the preferred sheet (or, failing that, any sheet) visible and active."""
    if preferred in wb.sheetnames:
        target = wb[preferred]
    else:
        visible = [ws for ws in wb.worksheets if ws.sheet_state == "visible"]
        target = visible[0] if visible else wb.worksheets[0]
    target.sheet_state = "visible"
    wb.active = wb.worksheets.index(target)

ensure_visible(wb, preferred="Data")
wb.save("hidden_only.xlsx")
print({ws.title: ws.sheet_state for ws in wb.worksheets}, "active:", wb.active.title)

Making a sheet visible is a fallback; the better fix is to find why the summary sheet was missing. Hide Sheets, Rows and Columns with openpyxl covers sheet states, including veryHidden.

Appending to an existing workbook

The same errors appear when a script opens an existing report to update it. With pd.ExcelWriter(path, mode="a", if_sheet_exists="replace"), pandas loads the workbook with openpyxl, replaces the named sheets and saves. If the file's visible sheet is the one being replaced and the replacement is skipped because the new DataFrame is empty, or if the template's summary sheet was hidden by an earlier step, the save fails in exactly the way described above. The fixes are the same: handle the empty case before opening the writer, and call a function like ensure_visible on writer.book before the block ends:

pd.DataFrame({"Region": ["North"], "Sales": [100]}).to_excel("existing.xlsx", sheet_name="Summary", index=False)

with pd.ExcelWriter("existing.xlsx", mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
    pd.DataFrame({"Detail": [1, 2]}).to_excel(writer, sheet_name="Data", index=False)
    writer.book["Summary"].sheet_state = "hidden"      # an earlier step hid the landing sheet
    ensure_visible(writer.book, preferred="Summary")
print({ws.title: ws.sheet_state for ws in load_workbook("existing.xlsx").worksheets})

The Summary sheet is visible again and the file opens on it. Calling the function inside the with block matters: the workbook is saved when the block exits, so that is the last chance to fix the sheet states.

Which message means what

The two messages are not interchangeable, and the traceback line alone does not tell you which situation you are in. A quick diagnostic before saving prints the facts:

def describe(wb):
    states = {ws.title: ws.sheet_state for ws in wb.worksheets}
    return f"{len(states)} sheet(s): {states or 'none'}"

empty = Workbook()
empty.remove(empty.active)
all_hidden = Workbook()
all_hidden.create_sheet("Data")
for ws in all_hidden.worksheets:
    ws.sheet_state = "hidden"
print(describe(empty))
print(describe(single))
print(describe(all_hidden))

No sheets at all means Cause 1: the report had nothing to write. One sheet, hidden, gives the ValueError. Several sheets, all hidden, gives the IndexError. Log describe(wb) just before saving in any report that hides sheets; it makes the next failure a one-line diagnosis.

Common pitfalls

• Reading the message literally. With an ExcelWriter, "must be visible" usually means "nothing was written"; check the data, not the sheet states.
• Switching to xlsxwriter to make the error go away. It writes a blank Sheet1 and the empty report goes out unnoticed.
• Hiding the active sheet. openpyxl picks the next visible sheet as active when saving; set wb.active yourself so readers land on the right sheet.
• Templates that remove sheets. When a template step deletes and recreates the visible sheet, a failure in between leaves only hidden sheets.
• veryHidden sheets. They count as hidden; a workbook of only very hidden sheets fails the same way.

Conclusion

openpyxl will not save a workbook with no visible sheet. If the error comes from a pandas ExcelWriter, the usual cause is that no DataFrame was written; handle the empty case explicitly rather than switching engines, since xlsxwriter silently writes a blank sheet. If sheets were hidden, make the intended landing sheet visible and active before saving, and find out why it was missing.

Frequently asked questions

Why does pandas ExcelWriter raise "At least one sheet must be visible"? With the openpyxl engine, the writer starts with no sheets and adds one per to_excel call. If the code wrote nothing, for example because every DataFrame was empty and skipped, saving fails with this error.

How do I unhide a sheet with openpyxl? Set ws.sheet_state = "visible" on the worksheet, then optionally make it active with wb.active = wb.worksheets.index(ws).

What does "The only worksheet of a workbook cannot be hidden" mean? The workbook has a single sheet and its state is hidden or veryHidden. Excel needs one visible sheet, so make it visible or add a visible sheet.

Why does the same code work with xlsxwriter? xlsxwriter adds a blank Sheet1 when a workbook has no sheets, so the file is written without error. The report is still empty; handle that case in your code.

Related

• Up one level: Troubleshooting Common Python Excel Errors — the other common errors.
• Hide Sheets, Rows and Columns with openpyxl — sheet states in detail.
• Fix "Worksheet index 0 is invalid, 0 worksheets found" — the reading-side counterpart.
• Create a Sheet for Each Group in a DataFrame — the loop that usually triggers this.
• Detect a Scheduled Report That Did Not Run — alerting on empty output.