Fix openpyxl max_row Counting Empty Rows

A worksheet has a header and five rows of data, but openpyxl disagrees:

>>> ws.max_row
2000

The symptom that usually brings people here is not the number itself but what follows from it: ws.append() writes the new row at 2001, two thousand rows below the table, and the next person to open the report scrolls past a blank sheet to find it. max_row is not broken. It answers a different question than the one you are asking — "what is the highest row that has a cell object?", not "where does the data end?" This page, part of Troubleshooting Common Python Excel Errors, shows what creates those cell objects and how to get the answer you actually want.

Prerequisites

pip install openpyxl pandas

The examples use ledger.xlsx: six real rows, bold formatting applied down to row 1,000, and an empty string in B2000 — a fair model of a sheet someone formatted by selecting whole columns.

Step 1 — See what max_row is counting

Load the file and compare max_row with the rows that hold values:

from openpyxl import load_workbook

wb = load_workbook("ledger.xlsx")
ws = wb.active
print(ws.max_row, ws.dimensions)
# 2000 A1:B2000

has_value = [r for r in range(1, ws.max_row + 1)
             if any(c.value not in (None, "") for c in ws[r])]
print(len(has_value), has_value[-1])
# 6 6

Three kinds of cell inflate the count:

1. Formatted empty cells. Selecting a whole column in Excel and making it bold, or giving a range borders "ready for data", stores a style on every one of those cells. openpyxl loads them as real cells with value=None.
2. Cells holding an empty string. An exported "" is a value as far as the file is concerned. So is a single space.
3. Cells your own script created. In normal (non-read-only) mode, reading ws.cell(row, col) or ws["A5000"] creates the cell if it did not exist. A loop that probes rows to find the end of the data moves the end as it goes.

The third one deserves a demonstration, because it makes code that looks read-only change the sheet:

from openpyxl import Workbook

probe = Workbook().active
probe.append(["only row"])
for r in range(1, 101):
    probe.cell(row=r, column=1).value      # just looking
print(probe.max_row)
# 100

Step 2 — Find the real last row

Walk upward from max_row and stop at the first row with a real value. Walking up is much faster than walking down when the inflation is large, because it stops as soon as it reaches the data:

def last_data_row(ws, column=None):
    """Highest row with a non-empty value (in one column, or in any column)."""
    for row in range(ws.max_row, 0, -1):
        cells = [ws.cell(row=row, column=column)] if column else ws[row]
        if any(c.value is not None and str(c.value).strip() != "" for c in cells):
            return row
    return 0

print(last_data_row(ws), last_data_row(ws, column=2))
# 6 6

Treating whitespace-only strings as empty is a deliberate choice for report data. If a single space is meaningful in your sheet, drop the strip(). Passing a column is useful when one key column is always filled — an ID or a date — because it ignores stray notes typed further right. ws.cell inside this loop still creates cells above row 6, but only in rows that are already inside the inflated range, so it does not make things worse.

Step 3 — Append where the data ends

ws.append() has no notion of "data"; it writes to max_row + 1. On the inflated sheet that is row 2001:

ws.append([6, 60])
print(ws.max_row)
# 2001

Write explicitly to the row after the real data instead:

wb = load_workbook("ledger.xlsx")
ws = wb.active

def append_after_data(ws, values, key_column=1):
    row = last_data_row(ws, column=key_column) + 1
    for col, value in enumerate(values, start=1):
        ws.cell(row=row, column=col, value=value)
    return row

print(append_after_data(ws, [6, 60]))
# 7
wb.save("ledger_appended.xlsx")

If the sheet will be appended to repeatedly, fix it once (Step 4) so append() works normally again and every other tool — Excel's Ctrl+End, pandas, a colleague's macro — agrees on where the data ends. openpyxl: Append Data to an Existing Excel Sheet covers appending in general.

Step 4 — Trim the empty rows for good

Deleting the empty rows below the data removes their cell objects, formatting included, and max_row drops back to the truth:

wb = load_workbook("ledger.xlsx")
ws = wb.active
last = last_data_row(ws)
if ws.max_row > last:
    ws.delete_rows(last + 1, ws.max_row - last)
print(ws.max_row)
# 6
wb.save("ledger_trimmed.xlsx")
print(load_workbook("ledger_trimmed.xlsx").active.max_row)
# 6

Two cautions. Deleting rows removes their formatting, so if the bold style down to row 1,000 was intentional — a template waiting for data — trimming undoes the template. And delete_rows does not move merged ranges, conditional formatting ranges or defined names; on a sheet that has any of those below the data, check them afterwards. The same used-range problem affects columns (max_column), and delete_cols trims those.

Step 5 — Read-only mode trusts the file's own dimension tag

In read-only mode openpyxl does not load cells up front, so it cannot count them. It reports the <dimension ref="…"> tag that the saving program wrote into the sheet XML — and programs are free to write a generous one:

ro = load_workbook("ledger.xlsx", read_only=True)
rs = ro.active
print(rs.max_row, rs.calculate_dimension())
# 2000 A1:B2000
ro.close()

To measure rather than trust, call reset_dimensions() and iterate. Rows then come back as the file actually stores them, and you can stop counting at the last one with data:

ro = load_workbook("ledger.xlsx", read_only=True)
rs = ro.active
rs.reset_dimensions()
last = 0
for n, row in enumerate(rs.iter_rows(values_only=True), start=1):
    if any(v is not None and str(v).strip() != "" for v in row):
        last = n
print(last)
# 6
ro.close()

This pattern also protects you from the opposite problem: some writers omit the dimension tag or record it too small, and without reset_dimensions() read-only iteration can stop early.

What pandas does with the same file

pandas drops trailing rows that are entirely empty, so the same workbook reads cleanly:

import pandas as pd

print(pd.read_excel("ledger.xlsx").shape)
# (5, 2)

That is why the problem often goes unnoticed until someone writes back with openpyxl. It also means a DataFrame's length is a reasonable cross-check: if len(df) + 1 (for the header) is far below ws.max_row, the sheet has an inflated used range worth trimming.

Common pitfalls

• Looping for r in range(1, ws.max_row + 1) to find blanks. It is correct but slow on inflated sheets, and in normal mode it creates cells for every coordinate it reads. Walk up from the bottom instead.
• Using max_row as a row count. It is a row number. A table starting at row 5 with 10 rows has max_row 14.
• Checking only None. Empty strings and spaces are values; decide explicitly whether they count.
• Trimming a template. Formatted empty rows in a template are there on purpose. Write after the data instead of deleting them.

Performance notes

Walking upward costs one row check per empty row below the data, which is trivial for thousands and noticeable only for sheets formatted to the bottom of the grid (1,048,576 rows). For those, check a single key column rather than whole rows, or use read-only mode with reset_dimensions(), which streams the XML and never builds the empty rows as objects. Speed Up openpyxl with read_only Mode covers streaming in detail.

Conclusion

max_row counts cell objects, and formatting, empty strings and your own reads all create them. Find the real end by walking upward to the first row with a value, append explicitly to the row after it, and trim the empty rows with delete_rows when the sheet is yours to clean. In read-only mode, remember that max_row is whatever the file claims; reset_dimensions() and iteration tell you what is really there.

Frequently asked questions

Why is max_row larger than the number of rows with data? max_row is the highest row number that has a cell object, not the highest row with a value. Formatting, an empty string, or merely reading a cell in normal mode all create cell objects and push max_row down.

Why does ws.append() write thousands of rows below my data? append() always writes to the row after max_row. If formatting or stray cells inflated max_row, the new row lands below them. Find the real last row and write with ws.cell(row=last + 1, ...) or trim the empty rows first.

Does pandas read those empty rows? No. read_excel drops trailing rows that are completely empty, so a DataFrame usually looks right even when openpyxl reports a huge max_row. The problem appears when you write back with openpyxl.

Why is max_row wrong in read-only mode but right in normal mode? In read-only mode openpyxl trusts the dimension tag stored in the file, which the program that saved it may have written generously. Call ws.reset_dimensions() and iterate, or open the file normally, to measure what is really there.

Related

• Up one level: Troubleshooting Common Python Excel Errors — other surprises in reading and writing.
• Find the Last Row with Data in openpyxl — the general technique across columns and tables.
• Fix "Unnamed: 0" Columns When Reading Excel with pandas — the same used-range effect, sideways.
• Iterate over Rows and Columns with openpyxl — loops that do not create cells.