Working with Excel Formulas in Python

A spreadsheet that only carries numbers is a report; a spreadsheet that carries formulas is a model someone can prod. Python can produce either. openpyxl writes formulas as plain strings and reads them back the same way — what it never does is calculate them, and that single fact explains most of the surprises in this area. This guide, part of Getting Started with Python Excel Automation, covers the full round trip: writing a formula, generating references that survive a growing dataset, reading results back, naming ranges, and freezing formulas into values before a file leaves your hands.

Write your first formula

A formula is just a string that starts with =. Assign it like any other value and save:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"

ws.append(["SKU", "Quantity", "Unit_Price", "Line_Total"])
ws.append(["A-100", 4, 19.99, "=B2*C2"])
ws.append(["B-200", 2, 49.50, "=B3*C3"])
ws.append(["C-300", 7, 12.25, "=B4*C4"])

ws["C6"] = "Order total"
ws["D6"] = "=SUM(D2:D4)"

wb.save("orders.xlsx")
print(ws["D2"].value)      # '=B2*C2' — the text, not 79.96

The print is the important line. Python holds '=B2*C2', not 79.96, because nothing has calculated anything. Open orders.xlsx in Excel or LibreOffice and the numbers appear immediately; the file on disk is complete and correct, it simply carries instructions rather than results.

That is also why a workbook full of formulas can look empty to a downstream script. If another job reads this file with pandas read_excel, the Line_Total column arrives as NaN until someone has opened and saved the workbook in a spreadsheet application.

Generate references that follow your data

Hardcoded row numbers survive exactly one dataset. Build them from the row you are writing, and let get_column_letter translate column indexes so a re-ordered layout does not silently point at the wrong column:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

rows = [
    ("A-100", 4, 19.99),
    ("B-200", 2, 49.50),
    ("C-300", 7, 12.25),
    ("D-400", 5, 8.75),
]

wb = Workbook()
ws = wb.active
ws.append(["SKU", "Quantity", "Unit_Price", "Line_Total", "Share"])

qty_col = get_column_letter(2)      # 'B'
price_col = get_column_letter(3)    # 'C'
total_col = get_column_letter(4)    # 'D'

for sku, qty, price in rows:
    ws.append([sku, qty, price])
    r = ws.max_row                                  # the row just written
    ws[f"{total_col}{r}"] = f"={qty_col}{r}*{price_col}{r}"

last = ws.max_row
total_row = last + 2
ws[f"C{total_row}"] = "Order total"
ws[f"{total_col}{total_row}"] = f"=SUM({total_col}2:{total_col}{last})"

# Each line's share of the order — the divisor must not move, so anchor it
for r in range(2, last + 1):
    ws[f"E{r}"] = f"={total_col}{r}/$D${total_row}"
    ws[f"E{r}"].number_format = "0.0%"

wb.save("orders_generated.xlsx")

Two habits are doing the work here. ws.max_row gives the row that was just appended, so the formula and its data can never drift apart. And the $D$14-style anchor on the divisor means every Share formula points at the one total cell — without the dollar signs, a reader who sorts or inserts a row gets a column of #DIV/0!.

Mixed anchors are legal and useful: $D14 pins the column while letting the row move, and D$14 does the opposite. When you generate a grid of formulas — a cross-tab where every cell divides by its column header — mixed anchors are what let one generated pattern fill the whole block.

Read a formula back: text or cached result

load_workbook has two personalities, and picking the wrong one is the most common formula bug in Python code:

from openpyxl import load_workbook

# Personality 1 — the default: formulas come back as text
wb_text = load_workbook("orders.xlsx")
print(wb_text["Orders"]["D2"].value)      # '=B2*C2'

# Personality 2 — cached results written by the last calculating application
wb_vals = load_workbook("orders.xlsx", data_only=True)
print(wb_vals["Orders"]["D2"].value)      # None if Excel has never saved this file

data_only=True does not calculate. It reads a value Excel stored next to the formula the last time Excel saved the workbook. For a file Python just generated, that slot is empty and you get None — a fact worth asserting loudly rather than discovering in a report:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx", data_only=True)
ws = wb["Orders"]

missing = [c.coordinate for c in ws["D"] if c.row > 1 and c.value is None]
if missing:
    raise SystemExit(
        f"No cached values in {missing[:5]} — open and save the file in Excel, "
        "or compute the totals in Python instead of writing formulas."
    )

There is also a halfway house. Setting the workbook's recalculation flag asks the reading application to recompute everything on open, which is useful when you have rewritten input cells under formulas that Excel might otherwise consider unchanged:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
wb.calculation.fullCalcOnLoad = True
wb.save("orders.xlsx")

The warning in the right-hand box deserves its own sentence: if you load with data_only=True, change one cell and call save(), every formula in the workbook is replaced by whatever cached number was sitting in it. That is occasionally exactly what you want — see freezing values below — and otherwise it is a silent data loss.

Name a range so formulas read like English

A defined name turns 'Rates'!$B$1 into TaxRate. Formulas written from Python become far easier to review, and a reader can find the input cell without hunting:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

wb = Workbook()
rates = wb.active
rates.title = "Rates"
rates["A1"] = "VAT rate"
rates["B1"] = 0.20

orders = wb.create_sheet("Orders")
orders.append(["SKU", "Net", "VAT", "Gross"])
orders.append(["A-100", 79.96])

# Workbook-wide: usable from any sheet
wb.defined_names.add(DefinedName("TaxRate", attr_text="'Rates'!$B$1"))

orders["C2"] = "=B2*TaxRate"
orders["D2"] = "=B2+C2"

wb.save("named.xlsx")
print(list(wb.defined_names))          # ['TaxRate']

Names come in two scopes. A name added to wb.defined_names is visible everywhere; a name added to ws.defined_names belongs to that sheet only, which is how you give five regional tabs their own Target cell without collisions:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

wb = Workbook()
for region in ("North", "South", "West"):
    ws = wb.create_sheet(region)
    ws["A1"] = "Target"
    ws["B1"] = 100000
    ws.defined_names.add(DefinedName("Target", attr_text=f"'{region}'!$B$1"))
    ws["B3"] = "=B2/Target"          # resolves to this sheet's own Target

del wb["Sheet"]
wb.save("scoped_names.xlsx")

Names are also the cleanest way to hand a range to a chart or a validation list. When you later add a dropdown from a named list, the formula =Regions is far more robust than ='Lookups'!$A$2:$A$12, because the name can be repointed in one place.

Freeze formulas into values before shipping

Some recipients should never see a formula: a file sent to a client, an archive that must read the same in five years, or a sheet that feeds a system without a calculation engine. Freezing means reading the cached results and writing them back as literals — which requires that the workbook has been opened and saved by Excel or LibreOffice at least once, so that cached values exist:

from openpyxl import load_workbook

src = load_workbook("orders.xlsx", data_only=True)   # cached results
dst = load_workbook("orders.xlsx")                   # formulas + all formatting

for name in src.sheetnames:
    s, d = src[name], dst[name]
    for row in s.iter_rows():
        for cell in row:
            target = d[cell.coordinate]
            if isinstance(target.value, str) and target.value.startswith("="):
                if cell.value is None:
                    raise SystemExit(f"{name}!{cell.coordinate} has no cached value")
                target.value = cell.value            # replace formula with result

dst.save("orders_frozen.xlsx")

Loading the file twice looks wasteful, but it is what keeps the styling. The data_only copy is only a source of numbers; every fill, border and number format is preserved because the file being saved is the ordinary copy. Running the same freeze with a single data_only=True load and a save() also works, and quietly strips nothing else — but it gives you no chance to check for missing cached values first.

Cross-sheet references and the quoting rule

A formula on the Summary tab that reads the Q1 Sales tab needs the sheet name in front of the reference, and that name must be wrapped in single quotes whenever it contains a space, a hyphen or anything other than letters, digits and underscores. Getting this wrong produces a #REF! that only shows up when someone opens the file, so it is worth a helper:

import re
from openpyxl import Workbook

def sheet_ref(sheet_name, ref):
    """Return a formula-safe reference like 'Q1 Sales'!$B$2 or Summary!$B$2."""
    safe = re.fullmatch(r"[A-Za-z_][A-Za-z0-9_]*", sheet_name)
    quoted = sheet_name if safe else "'{}'".format(sheet_name.replace("'", "''"))
    return f"{quoted}!{ref}"

wb = Workbook()
q1 = wb.active
q1.title = "Q1 Sales"
q1.append(["Region", "Revenue"])
for region, revenue in (("North", 26700), ("South", 20900), ("West", 7600)):
    q1.append([region, revenue])

summary = wb.create_sheet("Summary")
summary["A1"] = "Q1 revenue"
summary["B1"] = f"=SUM({sheet_ref('Q1 Sales', '$B$2:$B$4')})"
summary["A2"] = "Best region"
summary["B2"] = (
    f"=INDEX({sheet_ref('Q1 Sales', '$A$2:$A$4')},"
    f"MATCH(MAX({sheet_ref('Q1 Sales', '$B$2:$B$4')}),"
    f"{sheet_ref('Q1 Sales', '$B$2:$B$4')},0))"
)

wb.save("cross_sheet.xlsx")
print(summary["B1"].value)

Two details save time later. Sheet names that a user might rename are exactly the ones worth replacing with a defined name, because a rename updates the name's target but not a hardcoded string you generated. And a sheet name containing an apostrophe needs that apostrophe doubled inside the quotes — rare, but it happens with names like O'Brien Region, and the helper above handles it.

Writing lookup formulas from Python

Lookups are where generated formulas earn their keep: the reader can add a row to the price list and every line recalculates. VLOOKUP remains the most portable choice, while XLOOKUP is cleaner but only resolves in recent Excel versions:

from openpyxl import Workbook

wb = Workbook()

prices = wb.create_sheet("Prices")
prices.append(["SKU", "Unit_Price"])
for sku, price in (("A-100", 19.99), ("B-200", 49.50), ("C-300", 12.25)):
    prices.append([sku, price])
last_price_row = prices.max_row

orders = wb.create_sheet("Orders")
orders.append(["SKU", "Quantity", "Unit_Price", "Line_Total"])
for sku, qty in (("A-100", 4), ("C-300", 7), ("Z-999", 2)):
    orders.append([sku, qty])
    r = orders.max_row
    lookup_range = f"Prices!$A$2:$B${last_price_row}"
    # IFERROR keeps an unknown SKU from poisoning the totals below
    orders[f"C{r}"] = f'=IFERROR(VLOOKUP(A{r},{lookup_range},2,FALSE),"unpriced")'
    orders[f"D{r}"] = f'=IF(ISNUMBER(C{r}),B{r}*C{r},0)'

del wb["Sheet"]
wb.save("lookups.xlsx")

The FALSE fourth argument forces an exact match, which is almost always what a SKU lookup wants — the default of TRUE silently returns the nearest lower match on unsorted data, producing wrong prices rather than an error. Wrapping the call in IFERROR turns a missing SKU into a readable label instead of #N/A, and the ISNUMBER guard on the line total stops one unpriced row from breaking the grand total underneath.

Formula or pandas? A quick decision

The mixed approach is usually the best one for recurring reports: compute the figures in pandas so the numbers are fixed and correct, then add a handful of formulas — a grand total, a variance against target — where you want the reader to be able to play.

Common errors and fixes

Audit a delivered workbook for broken formulas

Once a file has been round-tripped through Excel, the cached values tell you whether the model still works. Excel stores error results as strings such as #REF! and #DIV/0!, so a short scan catches a broken report before a reader does:

from openpyxl import load_workbook

ERRORS = {"#REF!", "#DIV/0!", "#VALUE!", "#N/A", "#NAME?", "#NULL!", "#NUM!"}

wb = load_workbook("orders_frozen.xlsx", data_only=True)
broken = [
    f"{ws.title}!{cell.coordinate} = {cell.value}"
    for ws in wb.worksheets
    for row in ws.iter_rows()
    for cell in row
    if isinstance(cell.value, str) and cell.value in ERRORS
]

if broken:
    print("Formula errors found:")
    print("\n".join(broken[:20]))
else:
    print("No formula errors cached in this workbook.")

Run this as the last step of a report job and the failure lands in your logs rather than in someone's inbox. It pairs naturally with the wider checks in validating Excel data with Python, which cover the input side of the same problem.

Key takeaways

• openpyxl stores formulas as text and never calculates them; numbers appear when a spreadsheet application opens the file.
• data_only=True returns cached results, not fresh calculations — and returns None for a file Python just created.
• Saving a workbook that was loaded with data_only=True permanently replaces its formulas with numbers.
• Generate references from the row you are writing, and anchor anything that must not move with $.
• Defined names make generated formulas readable, and sheet-local names let repeated tabs reuse one name safely.
• When the number matters more than the model, compute it in pandas and write a literal.

Frequently asked questions

Does openpyxl calculate my formulas? No. openpyxl writes and reads the formula text; it has no calculation engine. The numbers appear when Excel, LibreOffice or another spreadsheet application opens the file and evaluates it.

Why does a formula cell read back as None with data_only=True?data_only=True returns the value Excel cached the last time it saved the file. A workbook that Python just created has never been opened by a calculating application, so there is no cached result yet.

Should I write a formula or compute the number in pandas? Write a formula when the reader is expected to change inputs and see the result update. Compute in pandas when the number must be identical for everyone, must survive a CSV export, or feeds a chart you are generating.

How do I stop my generated formula breaking when rows shift? Anchor the parts that must not move with dollar signs, for example $B$1 for a single rate cell, and build the moving parts from the row number you are on rather than hardcoding them.

Can I use named ranges written by Python inside formulas? Yes. Add a DefinedName to wb.defined_names for a workbook-wide name or to ws.defined_names for a sheet-local one, then reference the name in any formula string you write.

Why does Excel show my formula as text instead of a result? The cell was written while its number format was Text, or the string does not start with an equals sign. Set the format to General before assigning, and always begin the string with =.

Related

Up to the parent guide:

• Getting Started with Python Excel Automation — the full introduction to the libraries and their jobs.

Go deeper here:

• Write a Formula to an Excel Cell with openpyxl — the minimal write, including number-format traps.
• Read Formula Results with openpyxl data_only — why results are missing and what to do about it.
• Add SUM and AVERAGE Formulas with Python — total rows that grow with the data.
• Create a Named Range in Excel with openpyxl — workbook and sheet scope in practice.
• Convert Excel Formulas to Values with Python — freeze a model before sending it out.
• Copy a Formula Down a Column in openpyxl — fill patterns without Excel's fill handle.

Related areas:

• Using openpyxl for Excel File Manipulation — the object model these formulas live in.
• Writing DataFrames to Excel with Pandas — export first, then add formulas on top.
• Validating Excel Data with Python — dropdowns and rules that reference named ranges.