Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix NoneType Errors When Reading Cells with openpyxl

Why openpyxl hands your code None — empty cells, formulas that were never calculated, merged ranges, trailing formatted rows — and how to fix each one: find which cell is None, skip or default empty values deliberately, get formula results with data_only after a recalculation, and read merged ranges from their top-left cell.
Errors & fixesopenpyxl

A script that reads cells with openpyxl runs fine on one workbook and fails on the next with one of these:

Text
TypeError: unsupported operand type(s) for +=: 'int' and 'NoneType'
AttributeError: 'NoneType' object has no attribute 'strip'
TypeError: '<' not supported between instances of 'NoneType' and 'float'

All three mean the same thing: a cell's .value was None, and the code assumed it would be a number or a string. openpyxl returns None for four different reasons, and the right fix depends on which one it is. This page shows how to find the cell, tells the four cases apart and fixes each. It is part of Troubleshooting Common Python Excel Errors.

Prerequisites

Bash
pip install openpyxl

sales.xlsx is a small sheet of quarterly sales per rep, written by a Python script: a header row, five data rows, a Total column of formulas, a merged region cell for North, one rep missing a Q2 figure, one row with no rep name, and some shaded but empty rows below the data — the kind of sheet people hand over.

Four reasons a cell value is None Four panels. An empty cell has no value. A formula cell read with data_only returns None if the file was never calculated by a spreadsheet application. A merged range stores its value only in the top-left cell, so the others are None. Formatted but empty rows below the data extend max_row and yield None rows. Empty cell genuinely blank decide: skip or default Uncalculated formula data_only=True no cached result Merged range value in top-left only others read None Formatted empty rows max_row too far whole rows of None

Step 1 — Find the cell that is None

The traceback names the line, not the cell. Before changing anything, print which cells are None in the range the code reads; the pattern usually identifies the cause at once:

Python
from openpyxl import load_workbook

ws = load_workbook("sales.xlsx")["Sales"]
print("max_row:", ws.max_row)
for row in ws.iter_rows(min_row=2):
    empty = [c.coordinate for c in row if c.value is None]
    if empty:
        print(row[0].row, empty, type(row[0]).__name__)

The pattern is the diagnosis. Four different pictures appear here: row 3 has a None in column A (a merged cell) and D (a missing figure); row 4 has no rep in B; and rows 7 to 20 are entirely empty yet counted, because rows 8 to 20 carry a fill and that extends the sheet's used range.

Reading the pattern of None cells A decision diagram. If only formula cells are None, the file has no cached results. If the None cell is a MergedCell, read the range's top-left cell. If whole rows at the end are None, formatting extends max_row. If scattered single cells are None, they are genuinely blank. Which cells are None? only formula cells Uncalculated Step 3 MergedCell type Merged range Step 4 whole rows at the end Formatted rows Step 5 scattered cells Blank Step 2

Step 2 — Empty cells: decide what they mean

A blank Q2 figure could mean zero, "not reported yet" or "not applicable". The code should say which, in one place, rather than crashing or silently treating blanks as zero:

Python
def number(value, default=None):
    """Return value as a float, or default for blank cells; raise for text that is not a number."""
    if value is None or (isinstance(value, str) and not value.strip()):
        return default
    return float(value)

def text(value, default=""):
    return default if value is None else str(value).strip()

for row in ws.iter_rows(min_row=2, max_row=6, values_only=True):
    region, rep, q1, q2, _ = row
    print(f"{text(rep, '(no rep)'):9} Q1={number(q1)}  Q2={number(q2, default=0.0)}")

Treating missing Q2 as zero is a business decision; for an average it would be wrong, since a zero pulls the mean down. Keep the default explicit at each call site, so a reviewer can see what each blank turns into. Converting text with float() still raises for a cell like "n/a", which is what you want: a surprising value should stop the script rather than become zero.

Step 3 — Formulas that read as None

The Total column holds formulas. Loaded normally, openpyxl returns the formula text; loaded with data_only=True, it returns the cached result, the value the spreadsheet application stored when it last calculated the file. A file written by openpyxl or xlsxwriter has never been calculated, so there is no cached result, and every formula cell reads as None:

Python
print("formulas:", [c.value for c in load_workbook("sales.xlsx")["Sales"]["E"][1:4]])
print("data_only:", [c.value for c in load_workbook("sales.xlsx", data_only=True)["Sales"]["E"][1:4]])

Open the file in Excel and save it, or recalculate it headlessly with LibreOffice, and the cached values appear:

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated sales.xlsx > /dev/null 2>&1
ls recalculated
Python
calc = load_workbook("recalculated/sales.xlsx", data_only=True)["Sales"]
print("after recalculation:", [c.value for c in calc["E"][1:6]])

Row 3 now shows 980: LibreOffice treats the blank Q2 as zero in =C3+D3. Often the simplest fix is not to read formula results at all: compute the total in Python from the inputs, which you have anyway. Read Formula Results with openpyxl data_only and Recalculate Excel Formulas Without Excel in Python cover both routes.

The same formula cells, loaded two ways A table comparing how cell E2 is read. Loaded normally, it returns the formula text. Loaded with data_only after being written by Python, it returns None. Loaded with data_only after a recalculation, it returns 2550. How the file was loaded E2 reads as load_workbook(path) =C2+D2 data_only (Python file) None data_only (recalculated) 2550

Step 4 — Merged cells

A2:A3 is merged. The value lives in A2; A3 is a MergedCell placeholder whose value is None. Code that groups rows by region then puts row 3 in a None region. Fill the merged value into every cell of its range when reading:

Python
def merged_value(ws, cell):
    for rng in ws.merged_cells.ranges:
        if cell.coordinate in rng:
            return ws.cell(row=rng.min_row, column=rng.min_col).value
    return cell.value

print([merged_value(ws, ws.cell(row=r, column=1)) for r in range(2, 7)])

For a whole sheet, build a lookup of placeholder coordinates to their range's value once, rather than scanning the ranges per cell. Unmerge Cells and Fill the Values with openpyxl does it for a whole sheet.

Step 5 — Formatted empty rows at the end

max_row is the last row with anything in it, including formatting. Shaded rows 8 to 20 have no values, but loops that run to max_row see thirteen rows of None. Stop at the last row with a value in a key column, or skip rows that are entirely empty:

Python
data = [row for row in ws.iter_rows(min_row=2, values_only=True) if any(v is not None for v in row)]
print(len(data), "data rows out of", ws.max_row - 1)

Fix openpyxl max_row Counting Empty Rows goes further, including finding the true last row per column.

Read-only mode: EmptyCell

Large files are often opened with read_only=True for speed. The None values are the same, but two things change. Blank cells come back as EmptyCell objects, which have a value of None but no coordinate, so the diagnostic loop in Step 1 fails on them. And a read-only worksheet does not load merged ranges at all:

Python
ro = load_workbook("sales.xlsx", read_only=True)
rows = ro["Sales"].iter_rows(min_row=3, max_row=3)
print([type(c).__name__ for c in next(rows)])
print("merged ranges available:", hasattr(ro["Sales"], "merged_cells"))
ro.close()

In read-only mode, work with values_only=True and positions instead of cell objects, and if the sheet has merged cells that matter, read it once in normal mode to collect the ranges, or fill the merged values forward with pandas after loading. Close read-only workbooks explicitly; they keep the file open until you do.

Putting it together

A reader that handles all four cases, and reports what it defaulted, so nothing is silent:

Python
records, notes = [], []
for row in ws.iter_rows(min_row=2):
    values = [merged_value(ws, c) for c in row]
    if all(v is None for v in values):
        continue
    region, rep, q1, q2, _ = values
    if q2 is None:
        notes.append(f"row {row[0].row}: Q2 blank, treated as 0")
    records.append({"region": text(region), "rep": text(rep, "(no rep)"),
                    "total": number(q1, 0.0) + number(q2, 0.0)})
print(records)
print(notes)

Common pitfalls

  • Wrapping everything in or 0. value or 0 also turns a legitimate 0 or empty string into the default, and hides missing data; test is None explicitly.
  • data_only=True on Python-written files. There are no cached results; recalculate first or compute in Python.
  • Saving a workbook loaded with data_only=True. The formulas are replaced by their cached values, or lost where there were none. Never save over the original from a data-only load.
  • Merged ranges. Only the top-left cell has a value.
  • Looping to max_row. Formatting extends it; skip empty rows.

Conclusion

When openpyxl returns None, first print which cells are empty to see the pattern. Handle genuinely blank cells with explicit defaults in small helper functions; for formula cells read with data_only=True, recalculate the file first or compute the value in Python; read merged ranges from their top-left cell; and skip rows that are entirely empty instead of trusting max_row.

Frequently asked questions

Why does openpyxl return None for a cell that has a formula? With data_only=True openpyxl returns the cached result saved by the last application that calculated the file. Files written by Python have none, so the value is None. Open and save the file in Excel or recalculate it with LibreOffice first.

How do I treat empty cells as zero in openpyxl? Check for None explicitly, for example 0 if cell.value is None else cell.value. Avoid value or 0, which also replaces legitimate zeros and empty strings.

Why are some cells in a merged range None? A merged range stores its value only in the top-left cell; the others are MergedCell placeholders. Look up the range containing the cell and read its top-left cell.

Why does my loop read empty rows at the end of the sheet? ws.max_row includes rows that only have formatting. Skip rows whose values are all None, or stop at the last row with a value in a key column.

More on errors & fixes

More on errors & fixes →