Hide Sheets, Rows and Columns with openpyxl

A generated workbook usually contains more than the reader needs: a lookup sheet feeding data validation, a raw extract behind a summary, an intermediate calculation column, a technical key nobody wants to see. Leaving them visible makes the report look like a working file rather than a finished one. openpyxl hides all of them in a line each — with two traps worth knowing, one of which produces a workbook Excel refuses to open. This guide covers sheets, rows, columns and grouping. It is part of Protecting and Sharing Excel Workbooks.

Prerequisites

pip install openpyxl pandas

A workbook with something worth hiding — a summary sheet a reader wants, plus the lookup and raw sheets that support it:

import pandas as pd

summary = pd.DataFrame({"region": ["North", "South"], "revenue": [159.92, 247.50]})
lookups = pd.DataFrame({"region_code": ["N", "S", "W"],
                        "region": ["North", "South", "West"]})
raw = pd.DataFrame({"order_id": range(1, 21), "amount": range(20, 40)})

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    lookups.to_excel(writer, sheet_name="Lookups", index=False)
    raw.to_excel(writer, sheet_name="_raw", index=False)

Step 1 — Hide a sheet

sheet_state takes one of three string values:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")

wb["Lookups"].sheet_state = "hidden"
wb["_raw"].sheet_state = "veryHidden"

wb.save("report_tidy.xlsx")
print([f"{ws.title}: {ws.sheet_state}" for ws in wb.worksheets])

Choose between the two by asking whether a reader restoring the sheet would be a problem or a convenience. A lookup table somebody might reasonably want to check should be hidden. A raw extract that would only confuse should be veryHidden — not because it is secret, but because an accidental unhide makes the report look broken.

Both are equally readable from Python, which is the point to keep clear in your head. Reading a very hidden sheet takes no special handling at all:

import pandas as pd

# The "very hidden" sheet is entirely ordinary to pandas.
raw = pd.read_excel("report_tidy.xlsx", sheet_name="_raw")
print(len(raw))     # 20

Step 2 — Never hide the last visible sheet

Excel requires at least one visible worksheet. Hide them all and the file opens with a repair prompt, or refuses to open at all — and openpyxl will write it happily, because the constraint is Excel's rather than the format's.

def hide_sheets(wb, names, state="hidden"):
    """Hide the named sheets, refusing to leave the workbook with none visible."""
    if state not in {"hidden", "veryHidden"}:
        raise ValueError(f"unknown state: {state}")

    targets = [n for n in names if n in wb.sheetnames]
    still_visible = [
        ws.title for ws in wb.worksheets
        if ws.sheet_state == "visible" and ws.title not in targets
    ]
    if not still_visible:
        raise ValueError(
            "at least one sheet must remain visible; "
            f"hiding {targets} would hide them all"
        )

    for name in targets:
        wb[name].sheet_state = state
    return targets

This bites most often in a loop that hides "every sheet matching a pattern" against a workbook where the pattern happens to match everything — a naming convention change upstream is enough to trigger it.

There is a second, related rule: the active sheet should be visible. A workbook whose active index points at a hidden sheet opens on a blank view, which reads as a broken file:

from openpyxl import load_workbook

wb = load_workbook("report_tidy.xlsx")
hide_sheets(wb, ["Lookups", "_raw"], state="hidden")

# Point the workbook at a sheet the reader will actually see.
visible = [ws for ws in wb.worksheets if ws.sheet_state == "visible"]
wb.active = wb.index(visible[0])

wb.save("report_final.xlsx")

Step 3 — Hide rows and columns

Row and column visibility lives on the dimension objects, not on individual cells — so there is no loop:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Summary"]

# A single helper column.
ws.column_dimensions["D"].hidden = True

# A contiguous block of columns, in one call.
ws.column_dimensions.group("F", "J", hidden=True)

# Individual rows.
ws.row_dimensions[7].hidden = True

wb.save("report_tidy.xlsx")

Hiding many individual rows one at a time is the slow path, because each creates a dimension record. When you need to hide a computed set — say, every row whose status column is closed — collapse consecutive runs into groups:

from itertools import groupby
from operator import itemgetter
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["_raw"]

# Rows to hide: every closed order.
to_hide = [
    cell.row
    for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3)
    if cell.value == "closed"
]

# Collapse [4,5,6,9,10] into ranges 4-6 and 9-10.
for _, group in groupby(enumerate(to_hide), lambda p: p[1] - p[0]):
    rows = list(map(itemgetter(1), group))
    ws.row_dimensions.group(rows[0], rows[-1], hidden=True)

wb.save("report_tidy.xlsx")

One thing hiding does not do: change any value. A hidden row still contributes to SUM, and pandas reads it like any other row. If a total should exclude hidden rows, the formula has to say so — SUBTOTAL(109, ...) sums only visible rows, where the plain SUM does not:

ws["D20"] = "=SUBTOTAL(109,D2:D19)"    # visible rows only
ws["D21"] = "=SUM(D2:D19)"             # every row, hidden included

Step 4 — Group instead of hide, where readers might want the detail

Hiding gives the reader no signal that anything is there. Outline grouping does the same tidying but adds a plus and minus control in the margin, so the detail is one click away.

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["_raw"]

# Collapse detail rows under their summary row.
ws.row_dimensions.group(4, 9, outline_level=1, hidden=True)
ws.row_dimensions.group(12, 17, outline_level=1, hidden=True)

# Put the plus/minus control above the group rather than below it.
ws.sheet_properties.outlinePr.summaryBelow = False

wb.save("report_grouped.xlsx")

summaryBelow is worth setting deliberately. Excel's default places the control on the row after the group, which reads oddly when your summary row comes first — the usual layout for a regional breakdown. Setting it to False puts the control beside the summary, where readers expect it.

Columns group the same way, which is the neat way to fold away a block of monthly detail while leaving the annual totals visible:

# Months in D through O; the annual total sits in P.
ws.column_dimensions.group("D", "O", outline_level=1, hidden=True)

Common pitfalls and fixes

Performance and scale notes

Row dimensions are stored individually, so hiding 100,000 rows one at a time creates 100,000 records and inflates both memory and file size noticeably. Grouping consecutive runs collapses those into a handful of range records instead — the difference is easy to measure:

import time
from openpyxl import Workbook

rows = list(range(2, 50_002))

wb = Workbook(); ws = wb.active
start = time.perf_counter()
for r in rows:
    ws.row_dimensions[r].hidden = True
print(f"per row : {time.perf_counter() - start:.2f}s")

wb2 = Workbook(); ws2 = wb2.active
start = time.perf_counter()
ws2.row_dimensions.group(rows[0], rows[-1], hidden=True)
print(f"grouped : {time.perf_counter() - start:.4f}s")

Three habits follow. Group contiguous runs rather than hiding row by row. Prefer hiding columns to hiding rows where you have the choice — a sheet has at most a few dozen columns and potentially a million rows, so the column path is bounded. And ask whether the rows need to be in the file at all: filtering them out before writing produces a smaller, faster workbook than writing them and hiding them, and it removes the risk of somebody unhiding data you did not intend to ship.

import pandas as pd

# Better than writing everything and hiding the closed orders.
open_orders = df.loc[df["status"] != "closed"]
open_orders.to_excel("report.xlsx", index=False)

That last point is the one that matters most in practice. Hidden rows are still data in the file, still readable by anyone, and still counted by any total that does not use SUBTOTAL. Hide for tidiness; filter for correctness. Where the volume is large enough that either choice affects runtime, the streaming techniques in writing large DataFrames with write-only mode apply — though note that mode cannot set row visibility, so filtering upstream becomes the only option.

Conclusion

Hiding is a presentation control: sheet_state for sheets, hidden on the row and column dimensions for everything else. Guard the last visible sheet, or Excel will call the file corrupt, and point wb.active at something a reader will see. Prefer outline grouping when the detail might genuinely be wanted, since it signals that something is there. And remember what hiding does not do — the values stay in the file, readable by any library and counted by any plain SUM. When data should not be in the report, filter it out rather than hiding it.

Frequently asked questions

What is the difference between hidden and veryHidden? A hidden sheet appears in Excel's unhide dialog and any reader can restore it. A veryHidden sheet does not appear there at all and needs the VBA editor to reveal. Neither hides the data from a library reading the file.

Why does Excel say my file is corrupt after hiding sheets? You hid every sheet. Excel requires at least one visible worksheet, so check that a visible sheet remains before applying the last hide.

How do I hide a whole column? Set ws.column_dimensions["D"].hidden = True. Note that this is a column dimension property, not a per-cell one, so there is no need to loop over the cells.

Is grouping better than hiding? Usually, for detail rows. Grouping adds a plus and minus control in the margin so readers can expand the detail themselves, whereas hiding gives them no indication anything is there.

Do hidden rows still count in formulas and exports? Yes. SUM includes hidden rows unless you use SUBTOTAL, and pandas reads them like any other row. Hiding is purely a display state.

Related

• Up to the parent: Protecting and Sharing Excel Workbooks — where hiding sits among the protection layers.
• Lock Cells and Protect a Sheet with openpyxl — the step that usually follows hiding.
• Rename, Reorder and Delete Excel Sheets with openpyxl — the other sheet-level operations.
• Add a Summary Sheet to an Excel Report — the visible sheet the hidden ones support.
• Set Column Width and Row Height in openpyxl — the sibling dimension properties.