Read Formula Results with openpyxl data_only

load_workbook(path, data_only=True) promises the values behind your formulas and then, for a freshly generated file, hands back a column of None. Nothing is broken: data_only reads a cache written by whichever spreadsheet application last saved the file. This guide, part of Working with Excel Formulas in Python, explains where that cache comes from, how to detect that it is missing, and what to do in each situation.

Prerequisites

pip install openpyxl pandas

The pandas install is only needed for the recompute-in-Python option in the last section.

Reproduce the None in ten lines

Seeing it happen makes the rule stick:

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws.append(["Item", "Qty", "Price", "Total"])
ws.append(["A-100", 4, 19.99, "=B2*C2"])
wb.save("fresh.xlsx")

print(load_workbook("fresh.xlsx")["Sheet"]["D2"].value)                  # '=B2*C2'
print(load_workbook("fresh.xlsx", data_only=True)["Sheet"]["D2"].value)  # None

Both reads are correct. The first returns the instruction stored in the cell; the second returns the result slot, which no calculation engine has ever filled. Open fresh.xlsx in Excel or LibreOffice, save it, and re-run the script: the second line now prints 79.96.

Detect a missing cache instead of trusting it

The dangerous version of this problem is not None — it is a None that flows into a sum and becomes a plausible-looking zero. Check explicitly at the point of reading:

from openpyxl import load_workbook

def read_values(path, sheet, column, first_row=2):
    """Return the cached results of a formula column, or explain why they're missing."""
    formulas = load_workbook(path)[sheet]
    values = load_workbook(path, data_only=True)[sheet]

    out, uncached = [], []
    for row in range(first_row, formulas.max_row + 1):
        coord = f"{column}{row}"
        source = formulas[coord].value
        cached = values[coord].value
        if isinstance(source, str) and source.startswith("=") and cached is None:
            uncached.append(coord)
        out.append(cached)

    if uncached:
        raise ValueError(
            f"{len(uncached)} formula cell(s) have no cached result "
            f"(first: {uncached[0]}). Open and save the file in Excel, "
            "or compute these values in Python."
        )
    return out

print(read_values("orders.xlsx", "Orders", "D"))

Loading the file twice is the key move: one handle knows whether a cell is a formula, the other knows what it evaluated to. Comparing them separates "this cell is genuinely empty" from "this cell should hold a number and does not".

Option 1: let a spreadsheet application fill the cache

If the workbook is produced by Excel users and consumed by your script, the cache is already there and data_only=True is all you need. Read it, and treat a missing cache as a hard failure rather than a zero:

from openpyxl import load_workbook

wb = load_workbook("month_end.xlsx", data_only=True, read_only=True)
ws = wb["Summary"]

kpis = {ws.cell(row=r, column=1).value: ws.cell(row=r, column=2).value
        for r in range(2, 8)}
wb.close()

for name, value in kpis.items():
    if value is None:
        raise SystemExit(f"KPI {name!r} has no cached value — was the file saved from Excel?")
print(kpis)

read_only=True pairs well with data_only=True for this job: you are harvesting numbers, not editing, so streaming the sheet keeps memory flat on a large workbook. Remember to close() a read-only workbook — it holds an open file handle until you do.

Option 2: recompute in Python and stop depending on the cache

When your own code both writes and reads the workbook, the cache is a detour. Compute the figure with pandas, write the number, and optionally keep the formula next to it for the reader:

import pandas as pd
from openpyxl import load_workbook

df = pd.read_excel("orders.xlsx", sheet_name="Orders", usecols=["SKU", "Quantity", "Unit_Price"])
df["Line_Total"] = df["Quantity"] * df["Unit_Price"]

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
for i, total in enumerate(df["Line_Total"], start=2):
    ws[f"D{i}"] = float(total)          # a literal, readable by anything
ws[f"D{len(df) + 3}"] = f"=SUM(D2:D{len(df) + 1})"   # one formula, for the reader
wb.save("orders_values.xlsx")

print(df["Line_Total"].sum())

This is the pattern to reach for whenever a downstream system — a database load, a CSV export, another Python job — needs the numbers. Values survive every hop; formulas only survive into applications that can evaluate them.

Option 3: evaluate the formulas without Excel

Third-party libraries can parse and evaluate an existing workbook's formula graph. They are useful when you receive a modelling workbook you cannot open in Excel on a server, and they are not a drop-in replacement for a spreadsheet application — coverage of Excel's function set and of volatile or array formulas varies:

pip install formulas

import formulas

model = formulas.ExcelModel().loads("month_end.xlsx").finish()
solution = model.calculate()

# Keys look like "'[MONTH_END.XLSX]SUMMARY'!B2"
for key, value in solution.items():
    if key.endswith("!B2"):
        print(key, value)

Treat this as an escape hatch. If a calculation matters enough to automate, owning it in pandas is nearly always more predictable than re-evaluating someone else's spreadsheet.

Option 4: force a recalculation on next open

You cannot make openpyxl calculate, but you can ask the next application that opens the file to recalculate everything rather than trusting its own cache:

from openpyxl import load_workbook

wb = load_workbook("month_end.xlsx")
wb.calculation.fullCalcOnLoad = True
wb.save("month_end.xlsx")

This matters after your script has rewritten input cells: without the flag, some applications trust cached results whose inputs have changed underneath them, and a reader sees last month's totals over this month's data.

Which option fits your situation

The row that catches most teams out is the second one. It is tempting to keep writing formulas because the file looks richer, then to bolt on a data_only read at the end of the pipeline — which works on a developer machine where Excel has opened the file, and returns None on the server where nothing ever does.

Common pitfalls and gotchas

Performance and scale notes

data_only=True does not make loading slower — it reads a different attribute of the same XML — but combining it with read_only=True is what keeps a 200 MB workbook from filling memory. Loading the file twice (once per mode) doubles parse time, so on very large files do it once each and keep the two sheet handles rather than reloading per lookup. If you only need a handful of cells, read_only plus direct ws.cell(row=..., column=...) access avoids materialising the entire sheet.

Conclusion

data_only=True is a cache reader, not a calculator. On files that come from Excel users the cache is present and the mode does exactly what you want; on files your own code generated it is empty by definition. Detect the difference explicitly, never let None become a silent zero, and when your pipeline owns the numbers, compute them in pandas and write literals so no cache is involved at all.

Frequently asked questions

Why is data_only=True returning None? The workbook has no cached value for that cell. Caches are written by Excel or LibreOffice when they save a file; a workbook generated by Python has never been through a calculation engine.

Does openpyxl have a way to calculate formulas itself? No. If you need fresh numbers without a spreadsheet application, recompute them in Python with pandas, or use a formula-evaluation library such as formulas or pycel on the workbook.

Can I open one workbook in both modes at once? Yes, and it is the standard pattern — load the file twice, once with data_only=True for the numbers and once normally for the formulas and formatting.

Does saving a data_only workbook lose my formulas? Yes. Every formula is replaced by the cached value that was loaded, so only save from that mode when freezing values is exactly what you intend.

Do cached values go stale? They can. The cache reflects the inputs as of the last save by a calculating application, so editing source cells from Python leaves the old results in place until the file is recalculated.

Related

Up to the parent guide:

• Working with Excel Formulas in Python — writing, generating and freezing formulas.

Related guides:

• Write a Formula to an Excel Cell with openpyxl — the write side of the same round trip.
• Convert Excel Formulas to Values with Python — turn cached results into permanent literals.
• Read a Cell Value from Excel with openpyxl — the basics of reading cells and ranges.
• Speed Up openpyxl with read_only Mode — harvesting values from large workbooks.