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

Recalculate Excel Formulas Without Excel in Python

Get formula results when openpyxl returns None: recalculate with the formulas library or LibreOffice headless, or restructure so the values are computed in Python.

ws["B10"].value returns '=SUM(B2:B9)' when you wanted 4823.50, and loading with data_only=True returns None. Neither is a bug: openpyxl reads what is in the file, and the file only contains a cached result if Excel has saved it. A workbook your script just wrote has no cache at all. This guide covers the three ways to get real numbers without Excel — a pure-Python calculation engine, a headless LibreOffice pass, and the restructuring that makes the question go away. It extends Working with Excel Formulas in Python.

Why a formula cell can hold two different things A cell stores the formula string and, only after Excel saves the file, a cached result; openpyxl returns whichever one you ask for, and the cache is absent in a file Excel never opened. One cell, two stored values the formula string always present: "=SUM(B2:B9)" the cached result only after Excel saves the file load_workbook(path) gives you the formula load_workbook(path, data_only=True) gives None when no cache exists Nothing in openpyxl computes — it reads what the file already holds

Prerequisites

Bash
pip install openpyxl pandas formulas

The LibreOffice route needs the application itself, available as soffice on the path:

Bash
sudo apt-get install -y libreoffice-calc     # Debian/Ubuntu

Confirm the diagnosis first

Python
from openpyxl import load_workbook

formulas = load_workbook("report.xlsx")["Summary"]["B10"].value
values = load_workbook("report.xlsx", data_only=True)["Summary"]["B10"].value
print(repr(formulas), repr(values))

If the first prints a formula and the second prints None, the file has no cached results — it was written by a library and never opened in Excel. If the second prints a number, you already have what you need; the cache is there and no recalculation is required. The distinction is explored further in Read formula results with openpyxl data_only.

Option 1: calculate in pure Python

The formulas package parses a workbook, builds a dependency graph and evaluates it — no Excel, no LibreOffice, no external process:

Python
"""Recalculate a workbook and read a result, in pure Python."""
import formulas

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

for key, value in solution.items():
    if key.upper().endswith("'[REPORT.XLSX]SUMMARY'!B10"):
        print(key, "=", value)

It can also write the recalculated workbook back out, cached values included:

Python
model.calculate()
model.write(dirpath="./calculated")     # writes report.xlsx with results

The keys are fully qualified — book, sheet and cell — which is verbose but unambiguous in a workbook with cross-sheet references. Coverage is the caveat: formulas implements a large subset of Excel's function library, and a workbook that uses an unsupported function raises rather than silently guessing. Test it against your real file before building a pipeline on it.

Option 2: let LibreOffice recalculate

LibreOffice recalculates on load and can write the result back, which gives much broader function coverage at the cost of a heavier dependency:

Python
"""Recalculate by round-tripping through headless LibreOffice."""
import subprocess
from pathlib import Path

def recalculate(path: Path, out_dir: Path) -> Path:
    out_dir.mkdir(parents=True, exist_ok=True)
    subprocess.run(
        ["soffice", "--headless", "--norestore",
         "--convert-to", "xlsx", "--outdir", str(out_dir), str(path)],
        check=True, timeout=180,
        stdout=subprocess.DEVNULL, stderr=subprocess.PIPE,
    )
    return out_dir / path.name

result = recalculate(Path("report.xlsx"), Path("calculated"))

Then read the cached values normally:

Python
from openpyxl import load_workbook

wb = load_workbook(result, data_only=True)
print(wb["Summary"]["B10"].value)      # a number, at last

Two operational notes. LibreOffice keeps a user profile directory and will refuse to run two instances against the same one, so pass -env:UserInstallation=file:///tmp/lo-$$ when running conversions in parallel. And always set a timeout: a malformed file can hang the process indefinitely, which is exactly the failure a nightly job cannot recover from. The same tool converts to PDF, as covered in Convert an Excel file to PDF with Python.

Three ways to end up with numbers A pure-Python engine covers a subset of functions quickly, LibreOffice covers almost everything but costs a process, and computing in pandas avoids recalculation entirely. Coverage, cost and complexity formulas package pure Python, pip only a subset of functions slow on big models for: simple workbooks LibreOffice headless near-complete coverage a process per file profile and timeout care for: inherited models compute in pandas nothing to recalculate milliseconds, testable logic moves out of the sheet for: anything you own

Option 3: do not put the calculation in the sheet

The fastest recalculation is the one that never happens. If your code generates the workbook, compute the numbers in Python and write values — optionally alongside the formula, so a reader can still see how a figure was derived:

Python
import pandas as pd
from openpyxl import load_workbook

df = pd.read_excel("raw.xlsx", engine="calamine")
total = float(df["revenue"].sum())

wb = load_workbook("report.xlsx")
ws = wb["Summary"]
ws["B10"] = total                       # the number, computed and testable
ws["C10"] = "=SUM(B2:B9)"               # the formula, for transparency
wb.save("report.xlsx")

This is the right default for generated reports. The calculation lives in code that can be unit-tested and reviewed, the delivered file opens with correct numbers everywhere, and nothing depends on the recipient's software recalculating anything.

Verify the recalculation

Whichever route you take, check a known figure rather than assuming:

Python
from openpyxl import load_workbook

wb = load_workbook("calculated/report.xlsx", data_only=True)
ws = wb["Summary"]

expected = round(sum(ws.cell(row=r, column=2).value or 0 for r in range(2, 10)), 2)
actual = round(ws["B10"].value or 0, 2)
assert abs(expected - actual) < 0.01, f"total mismatch: {actual} vs {expected}"

An assertion like this belongs in the job, not just in a test: a recalculation that silently produced zeros — because a function was unsupported, or the process timed out — otherwise ships a plausible-looking report full of blanks.

Cache results so the recipient sees numbers immediately

There is a middle path worth knowing: write the formula and its cached result, so the file shows correct values the instant it opens and still recalculates if the reader edits an input. openpyxl does not expose the cache directly, but xlsxwriter does:

Python
import xlsxwriter

with xlsxwriter.Workbook("report.xlsx") as book:
    ws = book.add_worksheet("Summary")
    values = [1200.0, 980.5, 1450.0, 610.25]
    for row, value in enumerate(values, start=1):
        ws.write_number(row, 1, value)

    # formula string plus the result Excel would compute
    ws.write_formula(len(values) + 1, 1, "=SUM(B2:B5)", None, sum(values))

The fifth argument to write_formula is the cached value. Excel shows it immediately and replaces it the moment anything recalculates, so the two can never disagree for long. Getting it wrong is worse than omitting it, though — a wrong cached value is displayed as fact until someone edits the sheet, so compute it with the same code that produced the data.

What the reader sees, with and without a cached result A formula with no cached value shows as blank or zero until the reader recalculates, while a formula written with its computed result displays correctly on open. formula only formula + cached value openpyxl reads None PDF export prints a blank cell openpyxl reads the number PDF export prints the value The PDF case is where a missing cache is noticed by the recipient, not by you

The PDF row is the one that catches teams out. A report exported to PDF without cached results prints blank cells wherever a formula sits, because no application ever recalculated the file — see Convert an Excel file to PDF with Python.

Common pitfalls and gotchas

  • Expecting data_only=True to compute. It reads a cache; it never calculates.
  • Losing formulas on save. Opening with data_only=True and saving writes the cached values instead of the formulas, permanently.
  • Unsupported functions. formulas raises on what it does not implement — catch it and fall back rather than shipping a partial recalculation.
  • Parallel LibreOffice runs. They collide on the shared user profile; give each an isolated -env:UserInstallation.
  • Volatile functions. NOW(), TODAY() and RAND() change on every recalculation, so an output diff will never be stable.

Performance and scale notes

formulas builds a dependency graph of the whole workbook, so its cost grows with the number of formula cells rather than the number of data rows — a sheet with ten thousand formula cells is genuinely slow, while one with ten formulas over a hundred thousand rows is fast. LibreOffice costs a process start of a second or two plus its own recalculation, which makes it a batch tool: convert many files in one run rather than starting the application per request. Computing in pandas is orders of magnitude faster than either and is the only option that scales linearly with data rather than with formulas. For workbooks large enough that this matters, the write-side techniques in Write large DataFrames to Excel with write-only mode pair well with computing values up front.

Conclusion

openpyxl does not calculate, and a file written by Python has no cached results to read. Pick the route that matches the workbook: formulas when it is simple and you want to stay in Python, headless LibreOffice when you have inherited a model that uses the full function library, and computed values when you own the report — which is faster, testable, and removes the dependency altogether. Whichever you choose, assert a known total before delivery so a silent recalculation failure cannot reach a reader.

Frequently asked questions

Why does openpyxl return None for my formula cell? With data_only=True openpyxl returns the cached result Excel stored when it last saved the file. A workbook written by a Python library has never been opened by Excel, so there is no cache and the value is None.

Does the formulas library support every Excel function? No. It covers a large, useful subset — arithmetic, logic, lookup, text and common maths and date functions — but not everything, and not modern dynamic arrays. Test it against your actual workbook before depending on it.

Is LibreOffice a reasonable dependency on a server? Yes, and it is a common one. Headless LibreOffice recalculates on load and can convert to .xlsx or PDF, which makes it the most compatible option short of Excel itself.

What is the fastest approach? Not recalculating at all. Compute the numbers in pandas and write values, optionally alongside formulas for transparency. That runs in milliseconds and needs no extra process.

Can I keep the formulas and still have values? Yes — write the formula string and set the cached value, or recalculate once with LibreOffice before delivery so the file carries both.