openpyxl can't calculate formulas — here's the recipe

The problem: openpyxl reads formulas, it never evaluates them

openpyxl is a reader/writer for the xlsx file format. It stores the formula text you assign, and that's it. When you load a workbook, a formula cell gives you back the formula string:

import openpyxl
wb = openpyxl.load_workbook("model.xlsx")   # data_only defaults to False
print(wb["Sheet1"]["B1"].value)             # '=SUM(A1:A3)'

The openpyxl docs are explicit about the scope: it "supports limited parsing of formulas embedded in cells" — a Tokenizer to split a formula into tokens, and a Translator to shift a formula from one cell to another. Tokenizing and translating are not calculating. There is no evaluator, and the maintainers have said for years there won't be one.

The data_only=True trap

The usual next stop is data_only=True:

wb = openpyxl.load_workbook("model.xlsx", data_only=True)
print(wb["Sheet1"]["B1"].value)   # None

Per the docs, data_only "controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet." The catch is in the last clause: it returns the cached value that Excel wrote. If the file was created by openpyxl and never opened in Excel, there is no cache, so you get None. Even when a cache exists, it's a snapshot — change an input cell and the cached results are immediately stale. data_only=True is a way to read Excel's old homework, not a way to do the math.

The landscape

A handful of pure-Python evaluators exist. Honestly:

• formulas — a real evaluator that builds a dependency graph, but function coverage is partial and large workbooks get slow; you'll hit unsupported functions on nontrivial models.
• pycel — compiles a workbook to a Python graph; capable, but targets specific cells you name up front and coverage/precision vary by function.
• xlcalculator — descends from the same lineage; usable for simple sheets, with sparse function support and light maintenance.

They can work for small, well-scoped sheets. The failure mode is always the same: a function you use isn't implemented, or the numbers drift.

The recipe

formualizer is an open-source spreadsheet engine written in Rust with Python bindings. Author with openpyxl if you like; calculate with formualizer.

pip install formualizer openpyxl

Author with openpyxl, calculate with formualizer:

import openpyxl
import formualizer as fz

# 1. openpyxl writes the formulas (as text)
wb = openpyxl.Workbook()
ws = wb.active; ws.title = "Sheet1"
ws["A1"] = 1000.0; ws["A2"] = 2000.0; ws["A3"] = 1500.0
ws["A5"] = "west"
ws["D1"] = "east"; ws["E1"] = 0.10
ws["D2"] = "west"; ws["E2"] = 0.20
ws["B1"] = "=SUM(A1:A3)"
ws["B2"] = '=IF(B1>3000, "big", "small")'
ws["B3"] = "=VLOOKUP(A5, D1:E2, 2, FALSE)"
ws["B4"] = "=B1*B3"
wb.save("model.xlsx")

# 2. formualizer loads and evaluates (row, col are 1-based)
fwb = fz.load_workbook("model.xlsx")
print(fwb.evaluate_cell("Sheet1", 1, 2))   # 4500.0   (B1)
print(fwb.evaluate_cell("Sheet1", 2, 2))   # big      (B2)
print(fwb.evaluate_cell("Sheet1", 3, 2))   # 0.2      (B3)
print(fwb.evaluate_cell("Sheet1", 4, 2))   # 900.0    (B4)

evaluate_cell returns native Python values — float, str, bool, None, or nested lists for spilled arrays.

Or skip openpyxl entirely and build the workbook in formualizer:

import formualizer as fz

wb = fz.Workbook()
s = wb.sheet("Sheet1")
s.set_value(1, 1, 1000.0)
s.set_value(2, 1, 2000.0)
s.set_value(3, 1, 1500.0)
s.set_formula(1, 2, "=SUM(A1:A3)")
print(wb.evaluate_cell("Sheet1", 1, 2))   # 4500.0

Need the cached values written back into the xlsx (so a downstream tool — including openpyxl's own data_only=True — can read real numbers)?

import formualizer as fz

summary = fz.recalculate_file("model.xlsx", output="model.recalc.xlsx")
print(summary["status"], summary["evaluated"], summary["errors"])
# success 4 0

import openpyxl
wb = openpyxl.load_workbook("model.recalc.xlsx", data_only=True)
print(wb["Sheet1"]["B1"].value)   # 4500  (no longer None)

What you get

• 400+ Excel-compatible functions — SUM, IF, VLOOKUP, XLOOKUP, SUMIFS, PMT, and the rest of the common surface.
• A Rust numeric core — the arithmetic runs in native code, not interpreted Python; wheels ship for CPython 3.10–3.13 on Linux, macOS, and Windows.
• Deterministic evaluation — you can inject a fixed clock, timezone, and RNG seed for reproducible results.
• No Excel or LibreOffice install — it's a self-contained library, so it runs in CI, containers, and serverless with no headless-office automation.
• Prebuilt wheels — pip install formualizer, no Rust toolchain.

When not to use it

Be honest with your own requirements:

• You only need Excel's last saved answer. If the file always comes from Excel and inputs never change, data_only=True is fine — you don't need an evaluator at all.
• You depend on a function that isn't implemented yet. 400+ functions is broad but not all ~500 of Excel's. Check the function reference for anything exotic before committing.
• You need pixel-perfect round-trip of every xlsx feature — charts, pivot tables, VBA macros. formualizer calculates formulas; it is not a full Excel document-fidelity layer.
• Errors are values, not exceptions. A #DIV/0! comes back as {'type': 'Error', 'kind': 'Div'}, not a raised exception — check the return value if you need to branch on errors.