Fixing Python openpyxl Formulas That Show Stale Values After Writing

You write an Excel file with openpyxl, open it in Excel or LibreOffice, and the formula cells show zeros, old numbers, or #VALUE! errors. The formulas themselves look correct when you click into the cell — but the displayed result is wrong. This is one of the most common frustrations when generating Excel files programmatically.

The root cause comes down to how Excel caches formula results inside the .xlsx file format, and how openpyxl interacts with that cache. Once you understand the mechanism, the fix is straightforward.

What you'll learn

• Why openpyxl formulas display stale or zero values after writing
• How to force Excel to recalculate on open using wb.calculation.calcMode
• How data_only=True affects reading formula results
• How to write actual computed values instead of formulas when you need portability
• Common gotchas when reading back files you just wrote

Prerequisites

You should have openpyxl installed (pip install openpyxl). The examples below use Python 3.8+ syntax. A basic understanding of how Excel formulas work will help, but is not required.

Why Stale Values Happen

The .xlsx format stores two things for every formula cell: the formula string itself, and the last calculated result. When Excel opens a file, it may either trust the cached result or recalculate everything fresh — depending on a setting embedded in the workbook.

When openpyxl writes a formula, it writes the formula string but leaves the cached value empty or at zero. If the workbook's calculation mode tells Excel to trust cached results, Excel shows that empty cache instead of computing the formula. This is why you see zeros or blank cells even though the formula text is perfectly correct.

LibreOffice Calc is less forgiving about this than Excel, so the same file can look fine in Excel but broken in LibreOffice, or vice versa.

The Fastest Fix: Force Recalculation on Open

The most reliable single-line fix is to tell Excel to recalculate all formulas when the file is opened. You do this by setting the workbook's calculation mode before saving.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = 10
ws["A2"] = 20
ws["A3"] = "=SUM(A1:A2)"

# Force Excel to recalculate all formulas when the file is opened
wb.calculation.calcMode = "auto"

wb.save("output.xlsx")

Setting calcMode to "auto" tells the spreadsheet application to recalculate every formula on load. The default in a fresh openpyxl workbook is already "auto", but if you loaded an existing workbook that had a different mode set, it may have been preserved.

If you want to force a full recalculation even more aggressively, you can also set wb.calculation.fullCalcOnLoad = True:

wb.calculation.calcMode = "auto"
wb.calculation.fullCalcOnLoad = True
wb.save("output.xlsx")

This flag explicitly instructs the application to ignore the cached values entirely and recalculate from scratch. It is the closest thing to a guaranteed fix when opening in Excel or LibreOffice.

Reading Formula Results Back with openpyxl

If you write a formula and then immediately read the same file back with openpyxl, you will not get the computed result. openpyxl does not have a formula engine — it cannot evaluate =SUM(A1:A2) by itself.

The data_only flag

When you open a file with data_only=True, openpyxl reads the cached values instead of the formula strings. This sounds useful, but it only works if the cache was populated — which requires the file to have been opened and saved by Excel or LibreOffice at least once.

from openpyxl import load_workbook

# Read the formula string
wb_formulas = load_workbook("output.xlsx")
print(wb_formulas.active["A3"].value)  # =SUM(A1:A2)

# Read the cached calculated value
wb_data = load_workbook("output.xlsx", data_only=True)
print(wb_data.active["A3"].value)  # None or 0 if never opened in Excel

If the file was generated purely by openpyxl and never opened in a spreadsheet application, data_only=True will return None for formula cells. That is expected behavior, not a bug.

What to do when you need the value right away

If your workflow requires reading the computed value immediately after writing — without a round-trip through Excel — you have two options: compute the value in Python before writing it, or use a library that can evaluate formulas.

Option: Write Computed Values Instead of Formulas

Sometimes you do not actually need a live formula in the file. You just need the correct number to appear. In that case, compute the value in Python and write it directly.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

data = [10, 20, 30, 40]

for i, val in enumerate(data, start=1):
    ws.cell(row=i, column=1, value=val)

# Compute the total in Python and write the result, not a formula
total = sum(data)
ws.cell(row=len(data) + 1, column=1, value=total)

wb.save("output_static.xlsx")

This approach is the most portable and predictable. The downside is that the cell contains a static number, not a formula, so the file won't recalculate if a user edits the input cells later. Choose this when your output is a report that doesn't need to be interactive.

Option: Use a Formula Evaluation Library

If you need openpyxl to read back formula results without opening Excel, the openpyxl-formula ecosystem is limited. The more practical choice is formulas, a separate Python package that can parse and evaluate Excel formula syntax.

pip install formulas

import formulas

# Build an Excel model from a file that contains formulas
xl_model = formulas.ExcelModel().loads("output.xlsx").finish()

# Execute the model to populate calculated values
xl_model.calculate()

# Optionally write results back to a new file with cached values populated
xl_model.write()

The formulas library supports a large subset of Excel functions. It is not perfect for every edge case, but it handles common functions like SUM, IF, VLOOKUP, and string operations well. Check the project's documentation for the full supported function list before committing to it in production.

Gotchas and Common Pitfalls

Formula strings must start with an equals sign

openpyxl writes whatever string you assign to a cell. If you accidentally strip the leading =, the cell will contain a text string, not a formula.

# Wrong — this writes a string literal, not a formula
ws["A3"] = "SUM(A1:A2)"

# Correct
ws["A3"] = "=SUM(A1:A2)"

Row and column references must be absolute when copying

openpyxl does not adjust relative references when you copy a formula to another cell programmatically. If you assign =A1+B1 to ten rows in a loop, every cell gets that exact string. Use f-strings or string formatting to build the right reference for each row.

for row in range(2, 12):
    ws.cell(row=row, column=3).value = f"=A{row}+B{row}"

Loading a file you just saved doesn't give you computed results

This trips up a lot of people. The sequence write → save → load_workbook with data_only=True will still return None for formula cells because openpyxl never populated the cache. The cache only gets populated when a spreadsheet application evaluates the formulas and saves the file again.

LibreOffice may still show stale values

Even with fullCalcOnLoad = True, some versions of LibreOffice have been slow to honor the setting. If you are targeting LibreOffice specifically, writing static values or triggering a recalculation via a macro at open time are more reliable paths.

Calculation settings can be lost when loading an existing workbook

If you load an existing workbook, modify it, and save it again, check whether the original file had a different calcMode. It's worth setting it explicitly rather than relying on whatever was there before.

from openpyxl import load_workbook

wb = load_workbook("existing_file.xlsx")

# Always set this explicitly after loading
wb.calculation.calcMode = "auto"
wb.calculation.fullCalcOnLoad = True

# ... make your changes ...

wb.save("existing_file_updated.xlsx")

Choosing the Right Approach

There is no single answer that fits every situation. Here is a quick guide to help you decide.

Wrapping Up

Stale formula values in openpyxl-generated files come down to one thing: the cache is empty and the application is told to trust it. Here are the concrete actions to take from here.

• Add wb.calculation.calcMode = "auto" and wb.calculation.fullCalcOnLoad = True to every workbook you generate with openpyxl that contains formulas.
• If you need computed values in Python immediately after writing, compute them in Python first and write the result as a static value.
• When reading formula results from a file that was already processed by Excel, use load_workbook(path, data_only=True) — but only after a genuine Excel save has populated the cache.
• For fully automated pipelines that need formula evaluation without Excel, install and evaluate the formulas library against your specific formula set.
• Always build the formula string dynamically in a loop using f-strings so each cell references the correct row or column.