Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Iterate over Rows and Columns with openpyxl

Walk an Excel sheet cell by cell in Python — iter_rows and iter_cols, values_only, bounded ranges, the max_row trap, and building dicts keyed by header name.

Sometimes pandas is the wrong tool. You need the cell's number format, or its fill colour, or you are writing values into a template and must not disturb anything else. That means walking the sheet yourself, and openpyxl gives you two methods — iter_rows and iter_cols — plus a handful of options that make the difference between a loop that finishes in a second and one that takes a minute. This guide covers the mechanics, the max_row trap that catches everyone, and the patterns worth reusing. It is part of Using openpyxl for Excel File Manipulation.

Two traversal orders over the same range A three by three grid of cells. iter_rows walks it left to right then top to bottom, yielding one tuple per row: A2 B2 C2, then A3 B3 C3. iter_cols walks top to bottom then left to right, yielding one tuple per column: A2 A3 A4, then B2 B3 B4. Both accept the same min and max row and column bounds. iter_rows — one tuple per row iter_cols — one tuple per column A2 B2 C2 A3 B3 C3 A4 B4 C4 yields (A2,B2,C2) then (A3,B3,C3) A2 B2 C2 A3 B3 C3 A4 B4 C4 yields (A2,A3,A4) then (B2,B3,B4)

Prerequisites

Bash
pip install openpyxl

A sheet to walk:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["region", "branch", "units", "revenue"])
for i in range(1, 26):
    ws.append([f"Region {i % 4}", f"Branch {i}", 100 + i, 12.5 * (100 + i)])
wb.save("sales.xlsx")

Step 1 — Iterate rows

iter_rows yields one tuple of Cell objects per row:

Python
from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]

for row in ws.iter_rows(min_row=2, max_row=6):
    for cell in row:
        print(cell.coordinate, cell.value)

When you only want the data, values_only=True yields plain tuples instead — no Cell objects are constructed at all:

Python
for region, branch, units, revenue in ws.iter_rows(min_row=2, values_only=True):
    print(f"{region:<10} {branch:<10} {units:>5} {revenue:>10.2f}")

That tuple unpacking is the pattern to reach for by default. It is faster, it uses far less memory, and it reads better than indexing into a tuple of cells.

Bound the range when you know it. Passing min_col and max_col avoids walking columns you will ignore:

Python
# Only the units and revenue columns, rows 2 to 26.
for units, revenue in ws.iter_rows(min_row=2, max_row=26, min_col=3, max_col=4,
                                   values_only=True):
    print(units, revenue)

Two shorthands are worth knowing. Indexing with a range string yields the same row tuples, which reads nicely for a fixed block:

Python
for row in ws["A2:D6"]:
    print([cell.value for cell in row])

And ws.rows is an alias for the whole sheet with no options — convenient interactively, but it offers neither bounds nor values_only, so prefer iter_rows in real code.

Step 2 — Iterate columns

iter_cols is the transpose, yielding one tuple per column. It is the natural shape when you want to summarise a column or check its type:

Python
for column in ws.iter_cols(min_row=2, min_col=3, max_col=4, values_only=True):
    numbers = [v for v in column if isinstance(v, (int, float))]
    print(f"n={len(numbers)}  total={sum(numbers):,.2f}  max={max(numbers):,.2f}")

One important limitation: iter_cols does not work in read-only mode. Read-only mode streams the file row by row, and producing a column would require holding the whole sheet. If you need column-wise access on a large file, iterate rows and transpose in memory, or read with pandas instead.

Step 3 — The max_row trap

ws.max_row reports the extent of the used range, not the number of populated rows. A cell that was formatted and then cleared, a stray space in row 40,000, or rows that were deleted without clearing their formatting all extend it — so a sheet with 25 data rows can report max_row of 1,048,576 and your loop runs for a very long time over nothing.

Why max_row is not the number of rows with data A sheet where rows two to twenty-six hold data, followed by a large expanse of empty rows, and then a single formatted but empty cell at row forty thousand. Excel's used range therefore extends to row forty thousand, and max_row reports that. A loop bounded by max_row walks nearly forty thousand empty rows. Breaking when the key column is blank stops at row twenty-seven instead. rows 2–26 actual data rows 27–39,999 entirely empty row 40,000 formatted once, now blank break on a blank key column stops at row 27 · 25 iterations max_row says 40,000 39,974 wasted iterations the used range is a formatting fact, not a data fact

Stop on the data instead:

Python
from openpyxl import load_workbook

def iter_data_rows(ws, key_col=1, min_row=2, stop_after_blanks=1):
    """Yield row tuples until the key column has been blank N times running."""
    blanks = 0
    for row in ws.iter_rows(min_row=min_row, values_only=True):
        if row[key_col - 1] in (None, ""):
            blanks += 1
            if blanks >= stop_after_blanks:
                return
            continue
        blanks = 0
        yield row

wb = load_workbook("sales.xlsx", read_only=True)
ws = wb["Sales"]
rows = list(iter_data_rows(ws))
print(len(rows))       # 25, not 40,000
wb.close()

The stop_after_blanks parameter matters for sheets with a deliberate blank separator row between blocks — set it to 2 or 3 and a single gap does not end the read early.

Step 4 — Key rows by their header

Why positional row unpacking breaks and header keys do not Two readers against a sheet that gains an owner column in position two. Positional unpacking assigns branch to what is now the owner value and units to the branch name, so every downstream calculation silently uses the wrong field. A dict keyed by header name looks each field up by its column heading, so inserting a column changes nothing for the reader. an "owner" column is inserted in position 2 region owner (new) branch units positional unpacking region, branch, units = row branch now holds the owner units now holds the branch name no error — just wrong numbers dict keyed by header record["branch"], record["units"] still resolve correctly the new column is simply ignored and a renamed one raises KeyError

Positional unpacking breaks the day somebody inserts a column. Build a dict per row instead, keyed by the header text:

Python
from openpyxl import load_workbook

def read_records(path, sheet_name=None, header_row=1):
    """Yield each data row as a dict keyed by its column header."""
    wb = load_workbook(path, read_only=True, data_only=True)
    ws = wb[sheet_name] if sheet_name else wb.active
    try:
        rows = ws.iter_rows(min_row=header_row, values_only=True)
        headers = [
            str(h).strip() if h is not None else f"column_{i}"
            for i, h in enumerate(next(rows))
        ]
        for values in rows:
            if all(v is None for v in values):
                continue
            yield dict(zip(headers, values))
    finally:
        wb.close()

for record in read_records("sales.xlsx"):
    if record["units"] > 120:
        print(record["branch"], record["revenue"])

data_only=True returns the cached result of a formula rather than the formula text, which is what a reading pass almost always wants — the distinction is covered in reading formula results with openpyxl data_only.

Step 5 — Write while you iterate

Iterating over Cell objects lets you write back in the same pass, which is the core of any template-filling or formatting job:

Python
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("sales.xlsx")          # NOT read_only — we are writing
ws = wb["Sales"]

flag = PatternFill("solid", fgColor="FDEFD8")
bold = Font(bold=True)

for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=3, max_col=4):
    units, revenue = row
    if units.value and units.value > 120:
        units.fill = flag
        revenue.fill = flag
        revenue.font = bold

wb.save("sales_flagged.xlsx")

Note the constraint: writing needs a normal (not read-only) workbook, and modifying cells while iterating the same range is safe only because you are changing values and styles, not the sheet's shape. Inserting or deleting rows mid-iteration invalidates the iterator — do that in a separate pass, as described in inserting and deleting rows and columns with openpyxl.

Common pitfalls and fixes

SymptomCauseFix
Loop runs for minutes over an empty sheetmax_row reflects the used rangeBreak on a blank key column.
AttributeError: 'tuple' object has no attribute 'value'values_only=True yields values, not cellsDrop .value, or drop values_only.
iter_cols raises in read-only modeColumn access needs the whole sheetIterate rows and transpose.
Formulas come back as =SUM(...) stringsWorkbook opened without data_onlyLoad with data_only=True.
Values are None with data_only=TrueNo cached result — never opened in ExcelCompute in Python, or open and save once.
Loop is very slow on a big fileCell objects built for every cellread_only=True plus values_only=True.
Row unpacking breaks after a column is addedPositional accessKey rows by header name.
Blank rows appear in the outputSheet has interior gapsSkip rows where every value is None.

Performance and scale notes

Two flags dominate iteration cost, and they compose:

Python
import time
from openpyxl import load_workbook

for label, kwargs, values_only in [
    ("normal, cells",      {},                    False),
    ("normal, values",     {},                    True),
    ("read-only, values",  {"read_only": True},   True),
]:
    start = time.perf_counter()
    wb = load_workbook("sales.xlsx", **kwargs)
    total = sum(
        r[2] for r in wb["Sales"].iter_rows(min_row=2, values_only=values_only)
        if values_only and isinstance(r[2], (int, float))
    ) if values_only else 0
    wb.close()
    print(f"{label:<20} {time.perf_counter() - start:6.3f}s")

read_only=True streams the sheet instead of building the whole workbook in memory, and values_only=True skips constructing a Cell per cell. On a workbook of a few hundred thousand rows the pair is the difference between a job that fits in a container's memory limit and one that does not — the fuller treatment is in speeding up openpyxl with read-only mode.

Three further habits. Bound the columns, not just the rows — a sheet with sixty columns where you need four wastes most of its parse on the rest. Close read-only workbooks explicitly with wb.close(); they hold an open file handle that is not released by garbage collection alone, and a loop over hundreds of files will exhaust the descriptor limit. And do not iterate at all when pandas will do: for a plain read-and-aggregate, pd.read_excel followed by a vectorised operation beats any Python-level loop by a wide margin. Reach for iter_rows when you need what pandas cannot see — the styling, the formulas, the coordinates — or when you are writing into an existing sheet.

Conclusion

iter_rows and iter_cols are the two ways through a sheet, and the options matter more than the choice between them. Bound the range on both axes, pass values_only=True whenever you only need data, and open with read_only=True for anything large. Never trust max_row as a row count — it reports the used range, which formatting alone can extend by tens of thousands of rows — so break on a blank key column instead. And key your rows by header name rather than position, so the day a column is inserted upstream your loop keeps working.

Frequently asked questions

What does values_only=True actually change? It yields plain tuples of cell values instead of Cell objects. That skips constructing one object per cell, which is markedly faster and lighter — use it whenever you only need the data and not the styling or coordinates.

Why does max_row report more rows than my data has?max_row is the extent of the used range, not the count of populated rows. Formatting, a stray space, or a deleted-but-not-cleared row extends it. Break on a blank key column rather than trusting the number.

Should I use ws.rows or iter_rows?iter_rows, because it accepts bounds and values_only. ws.rows is a convenience alias for the whole sheet with no options, so it materialises Cell objects for every cell whether you need them or not.

How do I iterate a specific range like B2 to D50? Pass min_row, max_row, min_col and max_col to iter_rows, or index the sheet with a range string such as ws["B2:D50"]. Both yield tuples of cells row by row.

Why is my loop so slow on a large sheet? You are almost certainly building Cell objects you do not need. Open the workbook with read_only=True and iterate with values_only=True; together they turn a whole-workbook parse into a streaming read.