Count Rows in an Excel File Without Loading It

A script that receives Excel files of unknown size often needs the row count first: to choose between reading the file whole and reading it in chunks, to show progress, to reject a file that is suspiciously small, or to check that an export arrived complete. Loading the sheet into pandas just to call len() wastes seconds or minutes on a big workbook. Every xlsx sheet declares its own size at the top of its XML, which openpyxl can read almost instantly — but that declaration is sometimes missing or misleading, and it is worth knowing when to trust it and what to do instead. This page is part of Working with Large Excel Files in Python.

Prerequisites

pip install openpyxl python-calamine

Three example files: big.xlsx has 200,000 data rows plus a header on a Data sheet and a small Notes sheet; export.xlsx is the same data with its declared size broken, as some export tools write it; formatted.xlsx has 10 data rows followed by 489 rows that are highlighted but empty.

Step 1 — Read the declared size

The first thing in each sheet's XML is a <dimension ref="A1:H200001"/> tag giving the used range. In read-only mode, openpyxl reads it without touching the rows:

import time
from openpyxl import load_workbook

start = time.perf_counter()
wb = load_workbook("big.xlsx", read_only=True)
ws = wb["Data"]
print(ws.calculate_dimension(), ws.max_row, ws.max_column)
print(f"{time.perf_counter() - start:.3f} s")
wb.close()

200,001 rows — 200,000 data rows plus the header — and eight columns, in a few milliseconds. max_row includes the header, so subtract one for data rows. Closing the workbook matters in read-only mode, because openpyxl keeps the file open while the workbook object exists. Without read_only=True, openpyxl would parse every cell before answering. For a CSV export the equivalent quick count is the number of lines, with the caveat that a quoted field containing a line break spans two lines and inflates the total.

Step 2 — Recognise a size you cannot trust

The declared size is written by whatever program produced the file, and not all of them get it right. Some write A1 regardless of the data; some omit the tag. openpyxl in read-only mode takes the tag at its word:

wb = load_workbook("export.xlsx", read_only=True)
ws = wb["Data"]
print("declared:", ws.calculate_dimension(), "max_row:", ws.max_row)

ws.reset_dimensions()
start = time.perf_counter()
print("scanned:", ws.calculate_dimension(force=True), "max_row:", ws.max_row, f"{time.perf_counter() - start:.1f} s")
wb.close()

The export claims a single row. reset_dimensions() discards the declared size and calculate_dimension(force=True) works it out by reading every row — correct, but now the full scan takes seconds rather than milliseconds. A sheet reporting A1:A1, or a max_row of 1 for a file several megabytes in size, is the signal to fall back to counting.

The opposite problem is a size that is too large. Rows that carry formatting but no values count toward the used range, so a fill applied to a block of empty rows inflates max_row:

wb = load_workbook("formatted.xlsx", read_only=True)
ws = wb["Data"]
with_values = sum(1 for row in ws.iter_rows(values_only=True) if any(v is not None for v in row))
print("max_row:", ws.max_row, "rows with values:", with_values)
wb.close()

Five hundred declared rows, eleven with data. For a quick "is this file empty?" check the declared size is still fine; for anything that relies on the count being exact, count rows with values.

Step 3 — Count real rows quickly with calamine

python-calamine is a Rust spreadsheet reader. It ignores the declared size, skips rows without values, and reads fast enough that a full count is usually acceptable:

from python_calamine import CalamineWorkbook

for path in ["big.xlsx", "export.xlsx", "formatted.xlsx"]:
    start = time.perf_counter()
    sheet = CalamineWorkbook.from_path(path).get_sheet_by_name("Data")
    print(f"{path:15} {sheet.height:>7} rows  {sheet.width} columns  {time.perf_counter() - start:.2f} s")

All three are counted correctly — 200,001 for both large files, whatever their dimension tag says, and 11 for the formatted one — in well under a second for the large ones. height includes the header row. calamine also reads .xls, .xlsb and .ods, so the same code counts older formats too; Speed Up pandas Excel Reads with the calamine Engine covers using it through pandas.

Step 4 — Stream the sheet XML with the standard library

When you cannot add a dependency, count rows by streaming the sheet's XML out of the zip file. An xlsx is a zip archive; each sheet is an XML file whose <row> elements hold the cells. Find the right file through the workbook's relationships — sheet names do not map to file names directly — then count rows containing a value:

import posixpath
import zipfile
import xml.etree.ElementTree as ET

M = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
R = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}"

def sheet_files(z):
    workbook = ET.fromstring(z.read("xl/workbook.xml"))
    rels = {r.get("Id"): r.get("Target") for r in ET.fromstring(z.read("xl/_rels/workbook.xml.rels"))}
    files = {}
    for s in workbook.find(f"{M}sheets"):
        target = rels[s.get(f"{R}id")]
        files[s.get("name")] = target.lstrip("/") if target.startswith("/") else posixpath.join("xl", target)
    return files

def count_rows(path, sheet):
    with zipfile.ZipFile(path) as z, z.open(sheet_files(z)[sheet]) as xml:
        n = 0
        for _, el in ET.iterparse(xml):
            if el.tag == f"{M}row":
                if el.find(f"{M}c/{M}v") is not None or el.find(f"{M}c/{M}is") is not None:
                    n += 1
                el.clear()
        return n

for path in ["big.xlsx", "export.xlsx", "formatted.xlsx"]:
    start = time.perf_counter()
    print(f"{path:15} {count_rows(path, 'Data'):>7} rows  {time.perf_counter() - start:.2f} s")

The same counts as calamine, in constant memory: el.clear() discards each row once counted, so a million-row sheet uses no more memory than a small one. A row counts only if one of its cells has a value (<v>) or inline text (<is>), which excludes the formatted blanks. It is slower than calamine but needs nothing beyond Python itself.

Step 5 — Count every sheet in a folder of files

A common use is an inventory: how many rows does each sheet in each incoming file have? Use the declared size first and fall back to calamine only when it looks wrong:

from pathlib import Path

def sheet_counts(path):
    counts = {}
    wb = load_workbook(path, read_only=True)
    for ws in wb.worksheets:
        declared = ws.max_row
        if not declared or ws.calculate_dimension() == "A1:A1":
            declared = CalamineWorkbook.from_path(path).get_sheet_by_name(ws.title).height
        counts[ws.title] = declared
    wb.close()
    return counts

for path in sorted(Path(".").glob("*.xlsx")):
    print(f"{path.name:15}", sheet_counts(path))

The broken export is caught and recounted; the good files cost milliseconds each. The formatted file still reports 500 — the declared size is not wrong there, it includes formatted rows — so use calamine's count throughout if you need rows with values rather than the used range.

Common pitfalls

• Loading the data to count it. len(pd.read_excel(...)) parses every cell; read the declared size first.
• Trusting the declared size blindly. A1:A1 or a max_row of 1 on a large file means it was not recorded.
• Forgetting the header. Every count here includes it; subtract one for data rows.
• Formatted empty rows. They inflate the used range; count rows with values when it matters.
• Leaving read-only workbooks open. Call wb.close() or the file stays locked on Windows.

Performance notes

The declared size costs the same for any file size. calamine and the XML stream grow with the number of rows but stay several times faster than pandas or a full openpyxl scan; see Speed Up openpyxl with Read-Only Mode for the openpyxl side.

Conclusion

Read max_row from a read-only openpyxl workbook for an instant answer, and close it afterwards. Treat A1:A1 or a max_row of 1 on a large file as a missing size, and remember that formatted empty rows inflate the count. When the declared size cannot be trusted, count rows with values using python-calamine's height, or stream the sheet XML with the standard library — both far faster than loading the sheet into pandas.

Frequently asked questions

What is the fastest way to get the row count of an xlsx file? Open it with openpyxl in read-only mode and read ws.max_row. That comes from the dimension the file declares at the top of the sheet, so it takes milliseconds even for a million rows — as long as the program that wrote the file recorded it correctly.

Why does openpyxl say max_row is 1 for a sheet with thousands of rows? Some programs write a dimension of "A1" or none at all. In read-only mode openpyxl trusts it. Call ws.reset_dimensions() and then calculate_dimension(force=True), which scans the sheet, or count the rows another way.

Why is max_row much larger than the number of rows with data? Rows that have formatting but no values — a fill or border applied to a whole block — still count toward the sheet's dimension. Count rows that contain at least one value instead, for example with python-calamine's height.

Does counting rows require reading the whole file? Only when the declared dimension cannot be trusted. Then the rows have to be scanned, but streaming the sheet XML or using calamine is several times faster than loading the data into pandas.

Related

• Up one level: Working with Large Excel Files in Python — reading and writing big workbooks.
• Read a Large Excel File in Chunks with pandas — what to do once you know it is big.
• Fix openpyxl max_row Counting Empty Rows — the same inflation when editing.
• Reduce Memory When Reading Excel with pandas — loading big sheets lean.