Write Excel Formulas from a pandas DataFrame

Most reports are computed in pandas and written as values. Sometimes the recipient needs to see — and change — the calculation: a margin column that updates when they adjust a price, a total that follows edits, a commission rate held in one cell. pandas can write those formulas directly: any string that begins with = is written as a formula by both Excel engines. The work is in producing the right string for each row — the right column letters, the right row numbers once the header, index and startrow are taken into account — and in knowing what the file contains until Excel recalculates it. This page belongs to Writing DataFrames to Excel with pandas.

Prerequisites

pip install pandas openpyxl xlsxwriter

LibreOffice (soffice) is used in Step 6 to recalculate the file and check the formulas; it is optional.

Step 1 — A formula is just a string that starts with "="

import pandas as pd

orders = pd.DataFrame({
    "Product": ["Drill", "Saw", "Hose", "Rake"],
    "Qty": [3, 5, 12, 4],
    "Unit price": [89.0, 45.5, 14.0, 22.0],
})
orders["Total"] = [f"=B{r}*C{r}" for r in range(2, len(orders) + 2)]
orders.to_excel("orders.xlsx", index=False)
print(orders)

Row 1 of the sheet holds the header, so the first data row is row 2 and each formula multiplies the Qty and Unit price cells of its own row. Open the file in Excel and the Total column shows 267, 227.5, 168 and 88. In pandas the column is just text; the conversion happens in the writer.

Step 2 — Look up column letters by name

Hard-coding B and C breaks the moment someone adds a column. Derive the letters from the DataFrame's own column positions:

from openpyxl.utils import get_column_letter

def col_letter(df, name, index=False):
    return get_column_letter(df.columns.get_loc(name) + 1 + (df.index.nlevels if index else 0))

def per_row(df, template, startrow=0, index=False):
    first = startrow + 2                       # header row + 1, 1-based
    letters = {c: col_letter(df, c, index) for c in df.columns}
    return [template.format(r=r, **{k.replace(" ", "_"): v for k, v in letters.items()})
            for r in range(first, first + len(df))]

orders = orders.drop(columns="Total")
orders.insert(1, "SKU", ["D-01", "S-02", "H-03", "R-04"])
orders["Total"] = per_row(orders, "={Qty}{r}*{Unit_price}{r}")
print(orders["Total"].tolist())

After inserting a SKU column, Qty moved to C and Unit price to D, and the generated formulas follow: =C2*D2, =C3*D3, and so on. The template names columns rather than letters (spaces become underscores), so the formula reads like the calculation it performs.

Step 3 — Account for the index and startrow

Two writer options shift every cell: index=True (the default) puts the index in column A, pushing all data one column right, and startrow moves the header down. Formulas generated for one layout are wrong in the other, which is why per_row takes both:

with pd.ExcelWriter("orders_offset.xlsx", engine="openpyxl") as xw:
    title_rows = 2
    df = orders.drop(columns="Total").set_index("Product")
    df["Total"] = per_row(df, "={Qty}{r}*{Unit_price}{r}", startrow=title_rows, index=True)
    df.to_excel(xw, sheet_name="Orders", startrow=title_rows)
    xw.sheets["Orders"]["A1"] = "Orders, week 40"
print(df["Total"].tolist())

With the index in column A and the header on row 3, the first formula is =C4*D4. Getting the arithmetic in one helper — header row plus startrow, column position plus index levels — means it is right everywhere. When the index is a MultiIndex, df.index.nlevels columns are inserted, which the helper already allows for.

Step 4 — Add a totals row with SUM

A totals row is easiest to write after the data, with the worksheet object the writer exposes, because it does not belong in the DataFrame's columns:

orders["Total"] = per_row(orders, "={Qty}{r}*{Unit_price}{r}")
with pd.ExcelWriter("orders_total.xlsx", engine="xlsxwriter") as xw:
    orders.to_excel(xw, sheet_name="Orders", index=False)
    ws, book = xw.sheets["Orders"], xw.book
    last = len(orders) + 1                    # last data row, 1-based
    bold = book.add_format({"bold": True, "top": 1})
    ws.write(last, 0, "Total", bold)          # xlsxwriter rows are 0-based
    for name in ("Qty", "Total"):
        letter = col_letter(orders, name)
        ws.write_formula(last, orders.columns.get_loc(name), f"=SUM({letter}2:{letter}{last})", bold)
print("SUM range ends at row", last)

Mind the two numbering systems: formula text uses Excel's 1-based row numbers (SUM(C2:C5)), while xlsxwriter's write methods use 0-based row and column indexes, so the totals go to index last, which is the sheet's row last + 1. The same totals can be written as a structured table with a total row, as in Add a Total Row to an Excel Table.

Step 5 — Why the results read back as 0 or NaN

A workbook written by Python contains the formulas but no calculated results — nothing has run them yet. Excel calculates on opening, so a person never notices. Another script reading the file does:

for engine in ("xlsxwriter", "openpyxl"):
    orders.to_excel(f"check_{engine}.xlsx", index=False, engine=engine)
    print(engine, pd.read_excel(f"check_{engine}.xlsx")["Total"].tolist())

The xlsxwriter file reads back as [0, 0, 0, 0], because xlsxwriter stores a cached result of 0 for every formula; the openpyxl file reads back as NaN, because openpyxl stores no cached result at all. Neither is the real answer. If a downstream step must read numbers, either compute the values in pandas and write those, or cache the result alongside the formula with xlsxwriter's write_formula(row, col, formula, fmt, value) — see Store Formula Results with xlsxwriter.

Step 6 — Check the formulas by recalculating

To test that every generated formula calculates what you expect, let LibreOffice open and resave the file, which stores real cached results, and compare them with the same calculation in pandas:

import subprocess

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc",
                "check_openpyxl.xlsx"], check=True, capture_output=True)
recalc = pd.read_excel("recalc/check_openpyxl.xlsx")
expected = orders["Qty"] * orders["Unit price"]
print(recalc["Total"].tolist())
assert (recalc["Total"] - expected).abs().max() < 1e-9

The recalculated totals match the pandas calculation exactly. This check belongs in the test suite of any report that ships formulas; a wrong column letter otherwise surfaces as a plausible-looking but wrong number in someone's inbox. More approaches are in Recalculate Excel Formulas Without Excel in Python.

Step 7 — Text that starts with "=" by accident

Because the conversion is automatic, a comment like "=== see note ===" or a value pasted from another system becomes a broken formula and triggers Excel's repair dialog. With the xlsxwriter engine, switch the conversion off for the whole file and write intended formulas explicitly:

notes = pd.DataFrame({"Note": ["=== check stock ===", "ok"]})
with pd.ExcelWriter("notes.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as xw:
    notes.to_excel(xw, index=False)
from openpyxl import load_workbook
print(load_workbook("notes.xlsx").active["A2"].data_type)

The cell's data type is s — stored as text. With this option on, add the formulas you do want through write_formula. The openpyxl engine has no such switch, so change accidental values before writing — for example by prefixing them with a space or a quote character — as described in Prevent Formula Injection in Excel Exports.

Common pitfalls

• Off-by-one rows. The first data row is startrow + 2; forgetting the header row shifts every formula up one.
• Forgetting the index column. With index=True, every data column moves one letter right.
• Hard-coded letters. Derive them from column positions so inserted columns do not break the sheet.
• Function names. Write English function names with commas as separators (=SUMIFS(…)), whatever the reader's Excel language; newer functions may need the _xlfn. prefix, covered in Fix #NAME? Errors for New Excel Functions Written by Python.
• Reading your own output. Formula cells read as 0 or NaN until recalculated.

Performance notes

Formula strings cost the same to write as any other text, so a 100,000-row sheet with a formula column writes about as fast as one with a text column. The cost moves to the reader: Excel recalculates every formula on opening, and a few hundred thousand simple row formulas add a noticeable pause. Whole-column ranges such as SUM(E:E) are cheap in modern Excel, but volatile functions (OFFSET, INDIRECT, TODAY) recalculate on every edit — avoid generating them per row. When nobody will edit the inputs, write values instead and keep formulas for the cells people are meant to change.

Conclusion

Strings that start with = become formulas when pandas writes them. Generate them per row from column names rather than letters, include the header, startrow and index offsets in one helper, and write totals with the writer's worksheet object. Remember that the file holds no calculated results until Excel or LibreOffice opens it — cache values or compute them in pandas when other scripts read the output — and recalculate in a test to prove every formula is right.

Frequently asked questions

Can pandas write formulas to Excel? Yes. Any string value that starts with "=" is written as a formula by both the xlsxwriter and openpyxl engines, for example df"Total" = f"=B{r}*C{r}" for r in range(2, len(df) + 2).

Why does pandas read my formula column as 0 or NaN? Python writers do not calculate formulas. xlsxwriter stores a cached result of 0 and openpyxl stores none, so read_excel returns 0 or NaN until the file is opened and saved by Excel or recalculated by LibreOffice.

How do I stop pandas turning text that starts with "=" into a formula? With the xlsxwriter engine, pass engine_kwargs={"options": {"strings_to_formulas": False}} to ExcelWriter. With openpyxl there is no option, so change the text before writing.

How do I get the right row numbers when using startrow or the index? The first data row is startrow + 2 in Excel's 1-based numbering, because of the header row. With index=True the data columns shift right by the number of index levels, so add df.index.nlevels to each column position.

Related

• Up one level: Writing DataFrames to Excel with pandas — writer options and engines.
• Write a Formula to an Excel Cell with openpyxl — formulas cell by cell.
• Copy a Formula Down a Column in openpyxl — translating one formula to many rows.
• How pandas Data Types Are Written to Excel Cells — what happens to every other kind of value.