Fix Formula Cells Reading as NaN (or 0) in pandas

A report job writes a workbook with a Total column of formulas. A second job reads that workbook with pd.read_excel and gets NaN in every Total cell — or, depending on which library wrote it, a column of zeros. Open the file in Excel and the totals are all there. Nothing is wrong with pandas: it never calculates formulas, it reads the result that the last spreadsheet application stored next to each formula. A workbook written by Python has never been opened by a spreadsheet application, so that stored result is missing or a placeholder. This page shows how to confirm the diagnosis and the three ways to fix it. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl xlsxwriter

LibreOffice (soffice) is needed only for the recalculation fix in Step 3.

Step 1 — Reproduce the symptom

Write the same small workbook with both engines and read it back:

import pandas as pd

orders = pd.DataFrame({"Qty": [3, 5, 12], "Price": [89.0, 45.5, 14.0]})
orders["Total"] = [f"=A{r}*B{r}" for r in range(2, len(orders) + 2)]
for engine in ("openpyxl", "xlsxwriter"):
    orders.to_excel(f"orders_{engine}.xlsx", index=False, engine=engine)
    print(engine, pd.read_excel(f"orders_{engine}.xlsx")["Total"].tolist())

The openpyxl-written file reads as [nan, nan, nan] and the XlsxWriter file as [0, 0, 0]. The second is the more dangerous of the two: zeros look like data, and a downstream sum quietly reports 0 revenue.

Step 2 — Confirm the diagnosis

Before changing anything, check that the cells really are formulas without stored results. openpyxl can load a workbook twice — once for formulas, once for cached values — and compare:

from openpyxl import load_workbook

def formulas_without_results(path):
    f_ws = load_workbook(path).active
    v_ws = load_workbook(path, data_only=True).active
    missing, zero = [], []
    for row_f, row_v in zip(f_ws.iter_rows(), v_ws.iter_rows()):
        for cf, cv in zip(row_f, row_v):
            if isinstance(cf.value, str) and cf.value.startswith("="):
                if cv.value is None:
                    missing.append(cf.coordinate)
                elif cv.value == 0:
                    zero.append(cf.coordinate)
    return missing, zero

for engine in ("openpyxl", "xlsxwriter"):
    missing, zero = formulas_without_results(f"orders_{engine}.xlsx")
    print(f"{engine:10} no result: {missing}  cached 0: {zero}")

If the formula cells have no result (or a 0 that cannot be right), this page applies. If they have plausible cached values and pandas still shows NaN, the problem is elsewhere — a placeholder string, a merged cell, or the wrong header row — and Find and Report Missing Values in an Excel File is the better starting point.

Step 3 — Fix 1: let LibreOffice calculate and store the results

Opening and saving the file in a spreadsheet engine stores real results next to every formula. LibreOffice does it headlessly:

import subprocess

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalculated",
                "orders_openpyxl.xlsx"], check=True, capture_output=True)
print(pd.read_excel("recalculated/orders_openpyxl.xlsx")["Total"].tolist())

The recalculated copy reads [267.0, 227.5, 168.0], and the formulas are still there for anyone who opens the file. This is the fix to use when the workbook must keep live formulas and be read by other programs. One caveat from testing: LibreOffice does not recalculate XlsxWriter files on load, because they already carry (placeholder) results, so it keeps the zeros. Round-trip an XlsxWriter file through openpyxl first (load_workbook(p).save(p) drops the cached zeros), then convert. For more control — editing and recalculating in one session — see Automate LibreOffice Calc from Python with UNO.

Step 4 — Fix 2: calculate in pandas instead

If the reader is your own code, the simplest fix is not to depend on the formula at all. Read the inputs and repeat the calculation:

df = pd.read_excel("orders_openpyxl.xlsx")
df["Total"] = df["Qty"] * df["Price"]
print(df)

For simple row formulas this is one line and always correct. For complex models — lookups across sheets, financial functions — translating every formula is work, and the pages in Excel Formula Equivalents in pandas show the pandas version of the common ones. Better still, avoid the round trip: have the job that writes the workbook also hand the DataFrame (or a Parquet copy) to the job that reads it.

Step 5 — Fix 3: write cached results when creating the file

When you control the writer, store the result alongside the formula. XlsxWriter's write_formula takes the value as its last argument:

import xlsxwriter

book = xlsxwriter.Workbook("orders_cached.xlsx")
sheet = book.add_worksheet()
sheet.write_row(0, 0, ["Qty", "Price", "Total"])
for i, (qty, price) in enumerate(zip(orders["Qty"], orders["Price"]), start=1):
    sheet.write_row(i, 0, [qty, price])
    sheet.write_formula(i, 2, f"=A{i + 1}*B{i + 1}", None, qty * price)
book.close()
print(pd.read_excel("orders_cached.xlsx")["Total"].tolist())

The file now reads [267.0, 227.5, 168.0] without any recalculation, and Excel shows the same numbers — it recalculates on open anyway. The risk is that the value you compute in Python and the formula disagree; keep both derived from the same expression, and test them against each other with a LibreOffice recalculation in CI. The technique is covered in Store Formula Results with xlsxwriter. openpyxl has no equivalent: it cannot write a cached value next to a formula.

Step 6 — Workbooks saved by Excel with stale values

A related case: the file was saved by Excel, but with calculation set to manual, so the stored results are old. pandas faithfully reads the stale numbers. Check the workbook's calculation settings:

wb = load_workbook("recalculated/orders_openpyxl.xlsx")
print("calc mode:", wb.calculation.calcMode, "| full calc on load:", wb.calculation.fullCalcOnLoad)

The LibreOffice-saved copy reports None for calcMode — the attribute is simply absent, which means automatic — and True for fullCalcOnLoad, LibreOffice's request that the next application recalculate on opening. If calcMode says "manual", the cached values may predate the last edit; recalculate with LibreOffice as in Step 3 before trusting them. Setting wb.calculation.fullCalcOnLoad = True with openpyxl makes Excel recalculate when a person opens the file, which helps people but does nothing for pandas.

Which fix to use

The three fixes answer different situations, and the deciding question is who reads the file next:

Computing in pandas is the most robust, because there is no second copy of the logic to drift. Recalculation is the most faithful, because the spreadsheet engine evaluates exactly the formulas a person will see. Cached results are the fastest — no extra process — but only as trustworthy as the test that checks the cached numbers against a real recalculation. Many pipelines combine them: compute in pandas, write formulas with cached values for the people who open the file, and run a LibreOffice recalculation in the test suite to prove the two agree.

Performance notes

A LibreOffice conversion costs a second or two for process startup plus the calculation itself, so convert files in batches — soffice --convert-to xlsx --outdir out *.xlsx handles many files in one process — rather than one call per file in a tight loop. Computing in pandas is effectively free next to reading the workbook. Writing cached values with XlsxWriter adds nothing measurable to the write time.

Common pitfalls

• Treating zeros as data. XlsxWriter's cached 0 looks valid; check formula cells before summing.
• Recalculating XlsxWriter files directly. LibreOffice keeps their cached zeros; re-save through openpyxl first.
• Reading with data_only=True and saving. That replaces formulas with the (missing) cached values permanently.
• Expecting pandas to evaluate formulas. No pandas engine calculates; they all read stored results.
• Engines that read differently. engine="calamine" reads the same cached values as openpyxl, so switching engines does not help; only a file with stored results does.
• Copying formulas between workbooks. A formula copied into a new openpyxl workbook loses its cached result even if the source had one; recalculate the copy before another program reads it.

Conclusion

pandas reads the result stored next to each formula, and workbooks written by Python have none (openpyxl) or a placeholder 0 (XlsxWriter), which is why formula columns read as NaN or zeros. Confirm with a two-pass openpyxl check, then either let LibreOffice calculate and store the results, compute the values in pandas, or write cached results with write_formula when the file is created — and watch for Excel files saved in manual calculation mode.

Frequently asked questions

Why does pandas read_excel return NaN for formula cells? pandas reads the cached result stored with each formula. Files written by openpyxl contain formulas without cached results, so the cells read as NaN until the workbook is opened and saved by Excel or LibreOffice.

Why are my formula cells read as 0? The file was written by XlsxWriter, which stores 0 as the cached result of every formula unless you pass a value to write_formula. Excel shows the right number because it recalculates on opening; pandas reads the stored 0.

How do I make pandas read calculated formula values? Recalculate the workbook in a spreadsheet engine, for example soffice --headless --convert-to xlsx, which stores real results; or compute the values in pandas; or write cached values with XlsxWriter's write_formula when creating the file.

Can pandas evaluate Excel formulas? No. None of the read_excel engines calculate formulas. They only return the values that a spreadsheet application stored in the file.

Related

• Up one level: Troubleshooting Common Python Excel Errors — errors and surprises, with fixes.
• Read Formula Results with openpyxl data_only — the same cached values through openpyxl.
• Write Excel Formulas from a pandas DataFrame — where these formula columns come from.
• Fix Formulas Showing as Text in Excel Output — the opposite problem, formulas that never become formulas.