Store Formula Results with xlsxwriter So Every Viewer Shows Values

A report written with xlsxwriter looks right in Excel: every total, margin and percentage shows its value. Open the same file with pandas, preview it in a mail client, or convert it with LibreOffice on a server, and the formula columns are all zeros. Nothing is broken — xlsxwriter cannot calculate, so it writes each formula with a cached result of 0 and marks the workbook for recalculation on open. Excel recalculates; almost nothing else does. This guide, part of Building Excel Reports with xlsxwriter, stores the real results alongside the formulas so every reader sees the same numbers.

Prerequisites

pip install xlsxwriter pandas openpyxl

Step 1 — See the zeros

import pandas as pd
import xlsxwriter

wb = xlsxwriter.Workbook("orders.xlsx")
ws = wb.add_worksheet("Orders")
ws.write_row("A1", ["qty", "price", "total"])
for row, (qty, price) in enumerate([(3, 2.5), (4, 10.0)], start=2):
    ws.write(f"A{row}", qty)
    ws.write(f"B{row}", price)
    ws.write_formula(f"C{row}", f"=A{row}*B{row}")
ws.write_formula("C4", "=SUM(C2:C3)")
wb.close()

print(pd.read_excel("orders.xlsx")["total"].tolist())      # [0, 0, 0]

Inside the file, each formula cell holds the formula and a value of 0, and the workbook carries a "recalculate everything on load" flag. Excel honours the flag and shows 7.5, 40 and 47.5. pandas reads the stored 0s — and so does openpyxl.load_workbook(..., data_only=True). A headless LibreOffice conversion to CSV with default settings also kept the zeros in our test. Any downstream job that reads the report file gets wrong numbers.

Step 2 — Pass the value with the formula

write_formula takes the result as its fourth argument. Excel still recalculates on open, so the formula stays authoritative, but every other reader now sees the stored number:

wb = xlsxwriter.Workbook("orders.xlsx")
ws = wb.add_worksheet("Orders")
ws.write_row("A1", ["qty", "price", "total"])
rows = [(3, 2.5), (4, 10.0)]
for r, (qty, price) in enumerate(rows, start=2):
    ws.write_row(f"A{r}", [qty, price])
    ws.write_formula(f"C{r}", f"=A{r}*B{r}", None, qty * price)
ws.write_formula("C4", "=SUM(C2:C3)", None, sum(q * p for q, p in rows))
wb.close()

print(pd.read_excel("orders.xlsx")["total"].tolist())      # [7.5, 40.0, 47.5]

The third argument is the cell format; pass None when there is none. The value must be the result Excel would compute — a wrong cached value is worse than 0, because non-recalculating readers show it confidently, and Excel then silently corrects it, so two people looking at the same file see different numbers.

Step 3 — Compute the results in pandas and write both

In a real report the data is a DataFrame. Compute each derived column in pandas, then write the formula and its value together, so the file is both live in Excel and correct everywhere else:

orders = pd.DataFrame({"qty": [3, 4, 2], "price": [2.5, 10.0, 7.25]})
orders["total"] = orders["qty"] * orders["price"]

with pd.ExcelWriter("orders_pd.xlsx", engine="xlsxwriter") as writer:
    orders[["qty", "price"]].to_excel(writer, sheet_name="Orders", index=False)
    ws = writer.sheets["Orders"]
    money = writer.book.add_format({"num_format": "#,##0.00"})
    ws.write("C1", "total")
    for i, total in enumerate(orders["total"], start=2):
        ws.write_formula(f"C{i}", f"=A{i}*B{i}", money, total)
    last = len(orders) + 1
    ws.write_formula(f"C{last + 1}", f"=SUM(C2:C{last})", money, orders["total"].sum())

print(pd.read_excel("orders_pd.xlsx")["total"].tolist())   # [7.5, 40.0, 14.5, 62.0]

The pandas expression and the Excel formula must describe the same calculation. Keep them next to each other in the code, and for anything beyond simple arithmetic add a test that recalculates the file independently and compares — Recalculate Excel Formulas Without Excel in Python shows how.

Step 4 — Text and boolean results

The value argument is not only for numbers. Formulas that return text or TRUE/FALSE store those as well, and xlsxwriter writes the right cell type for each:

wb = xlsxwriter.Workbook("status.xlsx")
ws = wb.add_worksheet()
ws.write("A1", 3)
ws.write_formula("B1", '=IF(A1>2,"big","small")', None, "big")
ws.write_formula("C1", "=A1>2", None, True)
wb.close()

from openpyxl import load_workbook
print([c.value for c in load_workbook("status.xlsx", data_only=True).active[1]])   # [3, 'big', True]

Error results can be stored too, as the strings Excel shows — "#DIV/0!" for a division by zero — which is better than a misleading 0 when the formula really does fail for some rows.

Step 5 — Stop data from turning into formulas

xlsxwriter's strings_to_formulas option is on by default: any string that starts with = is written as a formula. That is convenient for hand-written formulas and dangerous for data. A comment cell reading "=total" becomes a formula referring to a name that does not exist:

import re
import zipfile

notes = pd.DataFrame({"note": ["=total", "plain"], "x": [1, 2]})
notes.to_excel("notes.xlsx", index=False, engine="xlsxwriter")
print(re.findall(r'<c r="A2"[^>]*>.*?</c>',
                 zipfile.ZipFile("notes.xlsx").read("xl/worksheets/sheet1.xml").decode()))
# ['<c r="A2"><f>total</f><v>0</v></c>']

with pd.ExcelWriter("notes_safe.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
    notes.to_excel(writer, index=False)
print(pd.read_excel("notes_safe.xlsx")["note"].tolist())    # ['=total', 'plain']

With the option off, strings stay strings and formulas must be written explicitly with write_formula — which is where you want them anyway, since that is where the value goes. Excel shows a #NAME? error for the accidental formula; other readers see its cached 0, which hides the problem completely.

Step 6 — Check the stored values against a recalculation

A stored value is only useful if it is right. The formulas package can load the workbook, recalculate every formula in Python and return the results, which you can compare with the cached values openpyxl reads. Here one total has been written wrongly on purpose:

import formulas
from openpyxl import load_workbook

with pd.ExcelWriter("check.xlsx", engine="xlsxwriter") as writer:
    orders[["qty", "price"]].to_excel(writer, sheet_name="Orders", index=False)
    ws = writer.sheets["Orders"]
    ws.write("C1", "total")
    for i, total in enumerate(orders["total"], start=2):
        ws.write_formula(f"C{i}", f"=A{i}*B{i}", None, total)
    ws.write_formula("C5", "=SUM(C2:C4)", None, orders["total"].sum() + 1)   # a wrong cached value

solution = formulas.ExcelModel().loads("check.xlsx").finish().calculate()
recalculated = {key.split("!")[1]: value.value[0][0] for key, value in solution.items()
                if "!" in key and ":" not in key}
cached = load_workbook("check.xlsx", data_only=True)["Orders"]
bad = [(ref, cached[ref].value, float(recalculated[ref])) for ref in ("C2", "C3", "C4", "C5")
       if abs(cached[ref].value - recalculated[ref]) > 1e-9]
print(bad)                                   # [('C5', 63.0, 62.0)]

The check finds the one cell whose stored value disagrees with what the formula actually computes. It runs in well under a second for a report of this size, which makes it cheap enough to include in the job's tests. The formulas package covers the common worksheet functions but not everything Excel offers; for workbooks that use functions it cannot evaluate, recalculate with LibreOffice in a test environment instead.

Do you need formulas at all?

Storing values raises an obvious question: if Python already computed every result, why write formulas? Because the reader may change an input. A formula keeps a margin column correct when someone corrects a quantity; a static value does not. The rule of thumb is to write formulas where readers are expected to edit inputs and explore — budgets, planning sheets, calculators — and plain values in reports that are a record of what happened, such as a month-end extract. Mixed reports are common: static data, with formulas only in the summary block that people adjust. Whichever you choose, the file should show the same numbers to everyone who opens it.

Common pitfalls

• Values that drift from the formula. If the pandas logic and the formula disagree, Excel users and pandas users see different totals. Test one against the other.
• Rounding. Store the unrounded result; let the number format do the rounding for display, as Excel's own recalculation would.
• Formulas that depend on today. A cached result of =TODAY()-A2 is correct only on the day the file was written; non-recalculating readers show that day's value forever. Store it, but label such columns with the run date.
• Assuming LibreOffice will fix it. A server-side conversion is not a recalculation. If you depend on LibreOffice results, store the values or recalculate deliberately.

Conclusion

xlsxwriter stores a cached 0 for every formula and relies on Excel to recalculate, so pandas, openpyxl, previewers and headless conversions show zeros. Compute each result in Python and pass it as the fourth argument of write_formula, for text and boolean results as well as numbers, keep the pandas logic and the formula side by side, and turn off strings_to_formulas when writing data so that text beginning with = stays text.

Frequently asked questions

Why does pandas read 0 for formulas written with xlsxwriter? xlsxwriter cannot calculate formulas, so it stores 0 as each formula's cached result and flags the workbook to recalculate when opened. Excel does that; pandas, openpyxl with data_only=True and many previewers only read the cached value.

How do I store the real result of a formula with xlsxwriter? Pass the value as the fourth argument, worksheet.write_formula("C2", "=A2*B2", cell_format, 7.5). Excel still recalculates on open, but every other reader sees 7.5 instead of 0.

Does LibreOffice recalculate xlsxwriter files? Not necessarily. In a headless conversion with default settings, LibreOffice kept the cached zeros. Interactive LibreOffice may prompt or recalculate depending on its settings, so do not rely on it for correct values.

Why did a text value starting with "=" turn into a formula? xlsxwriter's strings_to_formulas option is on by default, so any string beginning with "=" is written as a formula. Turn it off with the workbook option {"strings_to_formulas": False} when writing data that may contain such text.

Related

• Up one level: Building Excel Reports with xlsxwriter — report writing with XlsxWriter.
• Read Formula Results with openpyxl data_only — the reader that shows the cached values.
• Write Dynamic Array Formulas with Python — formulas that spill.
• Write a Formatted Excel Report with xlsxwriter — formats for the formula columns.