List Sheet Names and Column Headers Without Loading the Data

Before a script reads a workbook it often needs to know what is in it: which sheets exist, which one holds the data, what the columns are called, roughly how many rows there are. Reading every sheet with pd.read_excel(sheet_name=None) answers all of that, but it parses every cell of every sheet to do so — seconds per file, minutes for a folder. The workbook already records most of this in small metadata parts, and pandas and openpyxl can read them without touching the data. This page, part of Reading Excel Files with pandas, shows the fast way to inspect a file and then builds a catalogue of a whole folder.

Prerequisites

pip install pandas openpyxl

sales_2025.xlsx has an Orders sheet with 200,000 rows, a hidden Lookup sheet, a Summary sheet and a chartsheet called Chart. The exports/ folder holds three store workbooks, one of which has a column renamed.

Step 1 — List the sheet names

pd.ExcelFile opens the workbook and reads its list of sheets without parsing any of them:

import time
import pandas as pd

t = time.perf_counter()
with pd.ExcelFile("sales_2025.xlsx") as xl:
    print(xl.sheet_names, f"{time.perf_counter() - t:.3f}s")

Use ExcelFile as a context manager so the file is closed afterwards; left open, Python warns about an unclosed file and Windows keeps the workbook locked. Inside the with block the same object can read any sheet with xl.parse("Orders"), reusing the open file instead of opening it again — useful when the next step is to read one or two of the sheets it just listed.

The chartsheet is missing from that list: pandas lists only worksheets, because a chartsheet has no cells to read. openpyxl in read-only mode shows everything, and says which sheets are hidden:

from openpyxl import load_workbook

wb = load_workbook("sales_2025.xlsx", read_only=True)
print("all sheets:", wb.sheetnames)
for ws in wb.worksheets:
    print(f"  {ws.title:8} {ws.sheet_state}")
wb.close()

wb.sheetnames includes chartsheets; wb.worksheets only real worksheets. sheet_state is visible, hidden or veryHidden — the last one cannot be unhidden from Excel's menu, and is a common place for lookup tables that a template does not want users to touch. Hidden sheets are still returned by pandas, so a loop over sheet_names reads them too unless you filter on the state.

Step 2 — Read only the header row

Pass nrows=0 and read_excel returns an empty DataFrame whose columns are the header row. With sheet_name=None it does that for every sheet:

t = time.perf_counter()
headers = pd.read_excel("sales_2025.xlsx", sheet_name=None, nrows=0)
for sheet, frame in headers.items():
    print(f"{sheet:8} {list(frame.columns)}")
print(f"{time.perf_counter() - t:.3f}s")

t = time.perf_counter()
full = pd.read_excel("sales_2025.xlsx", sheet_name="Orders")
print(full.shape, f"full read: {time.perf_counter() - t:.2f}s")

pandas reads .xlsx files through openpyxl in read-only mode, which parses the sheet XML as a stream and stops after the rows it needs, so the cost of nrows=0 does not depend on how big the sheet is. Here it takes milliseconds, while the full read of the 200,000-row sheet takes a few seconds — the exact numbers depend on the machine, but the gap is typically three orders of magnitude.

If the header is not in the first row — a report with a title and a blank line above the table — nrows=0 returns the title as column names. Read the first few rows with header=None, nrows=10 instead and look for the header, as described in Find the Header Row in an Excel Sheet with a Shifting Layout.

Step 3 — Get the size of each sheet

Most writers — Excel, openpyxl, xlsxwriter — store a <dimension> record at the top of each sheet with the used range, such as A1:E200001. In read-only mode openpyxl reads it without loading the rows:

wb = load_workbook("sales_2025.xlsx", read_only=True)
for ws in wb.worksheets:
    print(f"{ws.title:8} {ws.calculate_dimension():12} rows={ws.max_row:>7} cols={ws.max_column}")
wb.close()

The dimension is written by whatever program saved the file, so treat it as a hint. Some exporters leave it out; then max_row is None and calculate_dimension() raises ValueError: Worksheet is unsized. calculate_dimension(force=True) scans the sheet to find out, which costs a full pass. Formatted but empty rows at the bottom also count as used — Count Rows in an Excel File Without Loading It covers exact counts.

Step 4 — Catalogue a folder of workbooks

The same calls, looped over a folder, produce an inventory of every file, sheet and column — the quickest way to spot the one export whose columns do not match the rest before a combining script fails on it:

from pathlib import Path

rows = []
for path in sorted(Path("exports").glob("*.xlsx")):
    wb = load_workbook(path, read_only=True)
    for ws in wb.worksheets:
        header = next(ws.iter_rows(max_row=1, values_only=True), ())
        rows.append({"File": path.name, "Sheet": ws.title, "State": ws.sheet_state,
                     "Range": ws.calculate_dimension(), "Columns": ", ".join(map(str, header))})
    wb.close()

catalogue = pd.DataFrame(rows)
print(catalogue.to_string(index=False))

expected = catalogue["Columns"].mode()[0]
print("\nodd one out:", catalogue.loc[catalogue["Columns"] != expected, ["File", "Columns"]].values.tolist())
catalogue.to_excel("catalogue.xlsx", index=False)

iter_rows(max_row=1, values_only=True) yields just the first row as a tuple of values; next(..., ()) handles an empty sheet. Comparing each file's header with the most common one flags stores_east.xlsx, where Sales was renamed to Revenue. Merge Excel Files with Different Column Names shows how to map it back before combining.

Step 5 — Choose the sheet to read by its columns

Sheet names are the least reliable part of a workbook people maintain by hand: Data, data (2), Export_Final, Sheet1. The header row is usually steadier. When the job needs "the sheet with an order ID, a date and an amount", look for those columns in the headers you already have and read only that sheet:

REQUIRED = {"OrderID", "Date", "Amount"}

def find_sheet(path, required):
    headers = pd.read_excel(path, sheet_name=None, nrows=0)
    matches = [name for name, frame in headers.items() if required <= set(frame.columns)]
    if len(matches) != 1:
        raise ValueError(f"{path}: expected one sheet with {sorted(required)}, found {matches}")
    return matches[0]

sheet = find_sheet("sales_2025.xlsx", REQUIRED)
orders = pd.read_excel("sales_2025.xlsx", sheet_name=sheet, usecols=list(REQUIRED) + ["Region"])
print(sheet, orders.shape, list(orders.columns))

required <= set(columns) is a subset test: every required column must be present, extra columns are fine. Raising when zero or several sheets match is deliberate — a workbook where two sheets both look like the order list needs a person to decide, and guessing would silently read the wrong one. Passing usecols with the columns the job needs also makes the one full read cheaper, because pandas skips converting the other columns.

The same check doubles as input validation for scheduled jobs: run it first, and a file that arrives with renamed columns fails with a message naming the file and the columns it expected, rather than a KeyError twenty lines later. Validate Excel Columns Before Import with pandas takes the idea further, with types and allowed values.

Common pitfalls

• Calling read_excel(sheet_name=None) to get names. It reads every sheet in full. Use pd.ExcelFile(path).sheet_names.
• Forgetting to close files. Read-only workbooks keep the file handle open until wb.close(), and a bare pd.ExcelFile(path) stays open until it is garbage-collected; on Windows the file stays locked and a later write fails with a permission error. Use with blocks or close explicitly.
• Trusting the dimension. It can be missing, or cover formatted empty rows. Use it to decide how to read a file, not as a row count for validation.
• Chartsheets and pandas. sheet_name=2 counts worksheets only, so positions differ from the tab order in Excel when a chartsheet sits between them. Refer to sheets by name.
• Old .xls files. load_workbook does not open .xls; pd.ExcelFile does, through xlrd, and still lists the sheets cheaply. See Read .xls Files in Python.

Performance notes

Every call on this page reads a fixed, small part of the file, so the time per file is roughly constant — a few milliseconds plus the cost of opening the zip. A catalogue of a thousand workbooks takes seconds. The one exception is a sheet without a dimension record, where finding the size needs a full scan; skip calculate_dimension for those unless you need the number.

Conclusion

Ask the workbook for its metadata before reading its data. pd.ExcelFile(path).sheet_names lists the worksheets, read_excel(..., nrows=0) returns just the header row of one or all sheets, and openpyxl's read-only mode adds hidden and chartsheet information plus the used range from each sheet's dimension record. Loop those calls over a folder to catalogue every file and catch the mismatched export before it breaks the job that combines them.

Frequently asked questions

How do I get the sheet names of an Excel file in pandas? Use pd.ExcelFile("file.xlsx").sheet_names, ideally inside a with block. It reads only the workbook's list of sheets, not their data. pd.read_excel(file, sheet_name=None) also gives the names as dictionary keys, but it reads every sheet in full to do so.

How do I read only the column headers of an Excel sheet? Call pd.read_excel(file, sheet_name="Sheet1", nrows=0). It returns an empty DataFrame whose columns are the header row, and it stops reading after the first row, so it is fast even on very large sheets.

Why is my chartsheet missing from pandas' sheet_names? pandas lists only worksheets, because a chartsheet has no cells. openpyxl's wb.sheetnames lists every sheet, including chartsheets, in tab order.

How can I tell whether a sheet is hidden? Open the workbook with openpyxl and check ws.sheet_state, which is visible, hidden or veryHidden. pandas does not report visibility and reads hidden sheets like any other.

Related

• Up one level: Reading Excel Files with pandas — the read_excel options that follow the inspection step.
• Read Excel Sheets Whose Names Change with pandas — picking a sheet from the list by pattern.
• Read All Sheets from an Excel File into DataFrames — when you do want every sheet.
• Speed Up openpyxl with Read-Only Mode — the streaming reader behind these calls.