List Every Sheet with Its Size and Visibility

Before changing, migrating or cleaning up a workbook you did not build, it helps to know what is in it. How many sheets are there, including the ones nobody can see? Which ones hold data, which hold only formulas, which have charts? Is there a sheet whose used range claims a million rows because someone once formatted a whole column? Excel's own interface answers these questions one sheet at a time, and it hides very hidden sheets entirely — they do not even appear in the Unhide dialog. A short openpyxl script answers them all at once. This page builds a sheet inventory: visibility state, sheet type, the used range Excel reports compared with the range that really contains values, counts of formulas, tables, charts, images, merged ranges and validation rules, protection, and the number of bytes each sheet occupies inside the .xlsx file, for one workbook or a whole folder. It belongs to Working with Multiple Excel Sheets in Python.

Prerequisites

pip install openpyxl pandas

The example workbook has six sheets: a data sheet with a table, a summary with formulas, a merged heading, a chart and a logo, an input sheet with a dropdown and protection, an old sheet where 20,000 rows were once filled white, a hidden lookup sheet and a very hidden calculation sheet. Only four of them are visible in Excel's tab bar.

Step 1 — One row per sheet

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

def real_extent(ws):
    """Last row and column that actually contain a value (ignoring formatting-only cells)."""
    last_r = last_c = 0
    for row in ws.iter_rows():
        for c in row:
            if c.value is not None:
                last_r, last_c = max(last_r, c.row), max(last_c, c.column)
    return last_r, last_c

def inventory(path):
    wb = load_workbook(path)
    rows = []
    for position, sheet in enumerate(wb._sheets, start=1):
        info = {"File": str(path), "Position": position, "Sheet": sheet.title, "State": sheet.sheet_state,
                "Type": "worksheet" if isinstance(sheet, Worksheet) else "chart sheet"}
        if isinstance(sheet, Worksheet):
            last_r, last_c = real_extent(sheet)
            info.update({
                "Reported range": sheet.dimensions, "Reported rows": sheet.max_row,
                "Rows with data": last_r, "Columns with data": last_c,
                "Formulas": sum(1 for r in sheet.iter_rows() for c in r if c.data_type == "f"),
                "Tables": len(sheet.tables), "Charts": len(sheet._charts), "Images": len(sheet._images),
                "Merged ranges": len(sheet.merged_cells.ranges),
                "Validation rules": len(sheet.data_validations.dataValidation),
                "Protected": bool(sheet.protection.sheet)})
        rows.append(info)
    return rows

import pandas as pd
inv = pd.DataFrame(inventory("finance_model.xlsx"))
print(inv.drop(columns="File").to_string(index=False))

wb._sheets holds every sheet in tab order, worksheets and chart sheets alike, which is what an inventory needs; the public wb.worksheets leaves chart sheets out. sheet_state is the visibility — visible, hidden, or veryHidden, the state that can only be set or undone through code or the VBA editor. The two size columns are deliberately different. dimensions and max_row are what Excel will treat as the used range, and they count any cell that has been touched, including cells with formatting and no value. Rows with data counts only cells that hold a value. The gap between them is the point: the Old layout sheet reports 19,999 rows used but has data in one, which is the classic cause of a workbook that scrolls forever and opens slowly. The other columns count what makes a sheet more than a grid of values: formulas (cells whose data type is f), tables, charts, images, merged ranges and validation rules, plus whether the sheet is protected.

Step 2 — How much of the file each sheet takes

import zipfile
import xml.etree.ElementTree as ET

NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
      "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
      "p": "http://schemas.openxmlformats.org/package/2006/relationships"}

def part_sizes(path):
    """Compressed and uncompressed size of each sheet's XML inside the .xlsx package."""
    with zipfile.ZipFile(path) as z:
        rels = ET.fromstring(z.read("xl/_rels/workbook.xml.rels"))
        target = {r.get("Id"): r.get("Target").lstrip("/").removeprefix("xl/") for r in rels.findall("p:Relationship", NS)}
        book = ET.fromstring(z.read("xl/workbook.xml"))
        sizes = {}
        for s in book.findall("m:sheets/m:sheet", NS):
            info = z.getinfo("xl/" + target[s.get(f"{{{NS['r']}}}id")])
            sizes[s.get("name")] = (info.compress_size, info.file_size)
    return sizes

sizes = part_sizes("finance_model.xlsx")
inv["Stored KB"] = inv["Sheet"].map(lambda s: round(sizes[s][0] / 1024, 1))
inv["Unpacked KB"] = inv["Sheet"].map(lambda s: round(sizes[s][1] / 1024, 1))
print(inv[["Sheet", "State", "Rows with data", "Stored KB", "Unpacked KB"]].to_string(index=False))

An .xlsx file is a zip archive with one XML part per sheet. The workbook part lists the sheets and a relationships part maps each to its file; parsing both with ElementTree, rather than matching them with regular expressions, copes with the attributes appearing in any order, which they do between writers. Following the two gives every sheet's compressed size in the archive and its unpacked size, which is roughly what Excel has to parse when it opens the file. The Old layout sheet, with one value and twenty thousand formatted cells, takes 974.8 KB unpacked — almost fifteen times the 65.9 KB of the data sheet with its 1,500 values, and more than every other sheet in the workbook put together. The compressed size understates the problem because repetitive XML compresses very well, so the unpacked size is the better guide to opening time. Finding and fixing such sheets is covered in Find Out Why an Excel File Is Slow to Open.

Deleting the formatted rows on the old sheet — or the sheet itself, if it is truly archived — would cut the unpacked size of the whole workbook by more than nine tenths. That is the kind of finding that justifies running an inventory before any clean-up: the problem is concentrated in one place that nobody would have guessed from looking at the tabs.

Step 3 — A folder of workbooks into one report

from pathlib import Path

def inventory_folder(folder):
    rows = []
    for path in sorted(Path(folder).glob("*.xlsx")):
        if path.name.startswith("~$"):
            continue                                       # Excel lock files
        try:
            sizes = part_sizes(path)
            for r in inventory(path):
                r["Unpacked KB"] = round(sizes.get(r["Sheet"], (0, 0))[1] / 1024, 1)
                rows.append(r)
        except Exception as e:
            rows.append({"File": str(path), "Sheet": "", "State": f"could not open: {type(e).__name__}"})
    return pd.DataFrame(rows)

Path("models").mkdir(exist_ok=True)
Path("finance_model.xlsx").replace("models/finance_model.xlsx")
report = inventory_folder("models")
flags = report[(report["State"] != "visible") | (report["Reported rows"] > 10 * report["Rows with data"].clip(lower=1))]
with pd.ExcelWriter("workbook_inventory.xlsx") as xw:
    report.to_excel(xw, sheet_name="Sheets", index=False)
    flags.to_excel(xw, sheet_name="Look at these", index=False)
print(flags[["Sheet", "State", "Reported rows", "Rows with data"]].to_string(index=False))

Wrapped in a loop over a folder, the same functions produce an inventory of every workbook a team keeps on a share, with one row per sheet and the file name as the first column. Lock files left by Excel are skipped, and a file that cannot be opened gets a row saying so rather than stopping the run. A second sheet lists the rows worth a look: sheets that are not visible, and sheets whose reported rows are more than ten times the rows that hold data. In this example that flags the hidden lookup sheet, the very hidden calculation sheet — which nobody browsing the workbook would know existed — and the old sheet full of formatting. The very hidden sheet is the one to look at first: it holds a formula built on the visible summary — a calculation someone set up deliberately and hid from view, which other workbooks, macros or reports may read. Nobody opening the file in Excel would know it was there, so it should be understood before anything in the workbook is renamed, moved or deleted.

Common pitfalls

• Using wb.worksheets for an inventory. It omits chart sheets; wb._sheets has them all.
• Missing very hidden sheets. Check sheet_state for veryHidden; Excel's Unhide list does not show them.
• Trusting max_row. It includes formatting-only cells; count cells with values for the real size.
• Compressed sizes only. Unpacked XML size is closer to the work Excel does when opening the file.
• One bad file stopping the run. Catch errors per file and record them.

Conclusion

A sheet inventory lists every sheet in tab order with its visibility — including very hidden ones — and type, the used range Excel reports next to the range that actually holds values, counts of formulas, tables, charts, images, merged ranges and validation rules, protection, and the sheet's size inside the file. Built with openpyxl and a look inside the zip package, it runs over a whole folder and flags hidden sheets and sheets bloated by formatting.

Frequently asked questions

How do I list all sheets including hidden ones with openpyxl? Iterate over wb._sheets (or wb.worksheets plus wb.chartsheets) and read each sheet's sheet_state.

What is a very hidden sheet? A sheet with state veryHidden, which does not appear in Excel's Unhide dialog and can only be made visible through code or the VBA editor.

Why does openpyxl report many more rows than my sheet has?max_row counts every cell that has been formatted or touched, not only cells with values.

How do I find which sheet makes an Excel file large? Open the .xlsx as a zip file and compare the sizes of the xl/worksheets/sheet*.xml parts.

Related

• Up one level: Working with Multiple Excel Sheets in Python — many sheets and files.
• Hide Sheets, Rows and Columns with openpyxl — setting the states.
• Audit Every Formula in a Workbook with Python — looking inside the formulas.
• Remove Hidden Data Before Sharing an Excel File — what to do about hidden sheets.