Find the Last Row with Data in openpyxl

Almost every script that adds to an existing sheet starts by asking where the data ends, and almost every one first tries ws.max_row. On a sheet that someone has formatted down to row 1,000 in advance, that answer is 1,000, and the new rows land far below the table. In read-only mode it can be even further off, because the value comes from a size recorded in the file rather than from the cells. This guide, part of Using openpyxl for Excel File Manipulation, explains what max_row really measures and how to find the row you actually want.

Prerequisites

pip install openpyxl pandas

This workbook has five rows of data, a formatted but empty cell in row 20, and an empty string typed into B10 — the three things that mislead naive checks:

from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Log"
for number in range(1, 6):
    ws.append([f"row{number}", number])
ws["A20"].fill = PatternFill("solid", start_color="FFEB9C")
ws["B10"] = ""
wb.save("log.xlsx")

Step 1 — See what max_row actually reports

max_row is the highest row number of any cell openpyxl holds for the sheet. A cell is held if it has a value, or a style, or has simply been accessed in code. It is a measure of the sheet's extent, not of its data.

from openpyxl import load_workbook

wb = load_workbook("log.xlsx")
ws = wb["Log"]
print(ws.max_row, ws.dimensions)       # 20 A1:B20

ws.cell(row=100, column=1)             # merely reading a cell creates it
print(ws.max_row)                      # 100

Excel has exactly the same notion. Press Ctrl+End on this sheet and Excel jumps to B20, not B5, because its "used range" also includes formatted cells — which is why people who format a template generously are often surprised by how far down a script writes. The difference is that Excel recalculates its used range when the file is saved after cleanup, while openpyxl simply reports the cells that exist in the file and in memory at the moment you ask.

The second print is the less obvious trap. ws.cell(row, column) and ws["A100"] create an empty cell object if none exists, which extends max_row. A loop that probes rows below the data to "look for the end" moves the end further away every time it looks.

Step 2 — Find the last row with a value on the whole sheet

Walk the rows from the top with iter_rows(values_only=True), which yields tuples of plain values and never creates cells, and remember the last row that contains anything other than None or an empty string.

def last_data_row(ws) -> int:
    last = 0
    for number, values in enumerate(ws.iter_rows(values_only=True), start=1):
        if any(value not in (None, "") for value in values):
            last = number
    return last

wb = load_workbook("log.xlsx")
print(last_data_row(wb["Log"]))        # 5

Treating "" as empty matters for sheets written by other scripts, which often write empty strings instead of leaving cells blank. If whitespace-only cells should count as empty too, test str(value).strip() instead. Scanning from the top rather than the bottom looks wasteful, but iter_rows is the cheap access path, and a bottom-up scan with ws.cell() would create cells as it goes.

Step 3 — Find the last row in one column

Sheets often have notes, totals or formulas typed below or beside the table. The row you want is then the last one where a key column — an ID, a date — is filled, regardless of what else sits on the sheet. Restrict the scan to that column.

from openpyxl.utils import column_index_from_string

def last_row_in_column(ws, column: str) -> int:
    index = column_index_from_string(column)
    last = 0
    for number, (value,) in enumerate(ws.iter_rows(min_col=index, max_col=index, values_only=True), start=1):
        if value not in (None, ""):
            last = number
    return last

wb = load_workbook("log.xlsx")
print(last_row_in_column(wb["Log"], "B"))    # 5 — B10 holds "", which does not count

For a table with a header row and occasional blank values in some columns, choose the column that is mandatory for every record. The same idea of anchoring on a required field appears in Drop Rows with Missing Required Fields in pandas.

Step 4 — Append below the real data

ws.append() writes to max_row + 1, so on this sheet it would put the next record in row 21, leaving fifteen blank rows between the old data and the new. Write explicitly below the last data row instead.

def append_after_data(ws, rows: list[list], key_column: str = "A") -> int:
    next_row = last_row_in_column(ws, key_column) + 1
    for offset, values in enumerate(rows):
        for column, value in enumerate(values, start=1):
            ws.cell(row=next_row + offset, column=column, value=value)
    return next_row

wb = load_workbook("log.xlsx")
ws = wb["Log"]
start = append_after_data(ws, [["row6", 6], ["row7", 7]])
wb.save("log_appended.xlsx")
print("appended from row", start)            # 6

Writing with ws.cell(..., value=...) into rows that already have formatting keeps that formatting, which is usually what the person who pre-formatted the sheet intended. The wider topic of adding to existing workbooks, including tables that should grow, is covered in openpyxl: Append Data to an Existing Excel Sheet.

Step 5 — Handle read-only mode

In read_only=True mode, openpyxl does not load cells up front, so max_row comes from the <dimension> element saved in the file. Some applications write it accurately, some write the extent of formatting, and some do not write it at all. reset_dimensions() discards it — and then max_row is None.

wb = load_workbook("log.xlsx", read_only=True)
ws = wb["Log"]
print(ws.max_row)                          # whatever the file recorded
ws.reset_dimensions()
print(ws.max_row)                          # None — unknown until you read

print(last_data_row(ws))                   # the scan works in read-only mode too
wb.close()

The iter_rows(values_only=True) scan works identically in read-only mode and streams the sheet without holding it in memory, which makes it the right tool for large files. Remember to close() a read-only workbook, because it keeps the file open. Read-only loading in general is covered in Speed Up openpyxl with read_only Mode.

Step 6 — Let pandas do it when you only need the data

If the goal is to read the data rather than to write below it, pandas already ignores trailing empty rows and columns. The length of the frame plus the header row is the last data row, provided the table starts in row 1.

import pandas as pd

frame = pd.read_excel("log.xlsx", sheet_name="Log", header=None)
print(len(frame))                          # 5

That shortcut breaks down if there are blank rows inside the data — pandas keeps those as rows of missing values — or if the table starts lower on the sheet. For sheets with banners above the data, locate the header first, as in Find the Header Row in an Excel Sheet with a Shifting Layout.

Common pitfalls

Performance and scale notes

A full scan touches every row once. In normal mode the cells are already in memory, so the scan is fast; the load itself is the cost. In read-only mode the scan streams the XML and is the fastest option for large files — a key-column scan with min_col/max_col also avoids building tuples for the other columns. If a job appends to the same large sheet repeatedly, store the last row number alongside the file and verify it with a quick check of that row and the next, instead of scanning the whole sheet every time.

Conclusion

ws.max_row measures how far the sheet's cells extend — including formatting, stale dimensions and cells created just by looking at them — so it is the wrong answer for "where does the data end". Scan with iter_rows(values_only=True), over the whole sheet or a key column, treating None and "" as empty. Write new rows explicitly after that position instead of using append(), use the same scan in read-only mode for large files, and let pandas handle the question when you only need to read.

Frequently asked questions

Why is openpyxl max_row larger than my data? Because cells with formatting, or cells your code accessed, count as part of the sheet even when they are empty. max_row reports that extent, not the last value.

Does deleting rows fix max_row? Deleting the formatted empty rows with ws.delete_rows removes those cells, so max_row shrinks accordingly. Clearing their values without removing the formatting does not.

How do I find the last column with data? Use the same scan turned sideways: iterate with iter_cols(values_only=True), or check each row tuple and track the highest index holding a value.

Is there an equivalent of Excel's Ctrl+Up from the bottom? Not directly. Scanning the column with iter_rows and keeping the last filled row gives the same answer without creating cells.

Do formulas that return an empty string count as data? With a normal load, a formula cell holds its formula text, so it counts as filled. Load with data_only=True to see cached results instead, where a formula that returned an empty string reads as an empty value.

Related

• Up one level: Using openpyxl for Excel File Manipulation — reading and editing workbooks cell by cell.
• Iterate over Rows and Columns with openpyxl — iter_rows and iter_cols in detail.
• Insert and Delete Rows and Columns with openpyxl — trimming formatted empty rows.
• Append a DataFrame to an Existing Excel File with pandas — the pandas route to adding rows.
• Find and Replace Text in Excel with openpyxl — another whole-sheet pass that should stay within the data.