Write a Formula to an Excel Cell with openpyxl

Writing a formula with openpyxl is a single assignment: give the cell a string that starts with =. The complications are never in the assignment — they are in what you assign, whether the destination cell's format lets the formula run, and how you confirm the file is right without opening Excel. This guide, part of Working with Excel Formulas in Python, covers all three.

Prerequisites

Install openpyxl into your environment:

pip install openpyxl

Nothing else is required — no Excel installation, no COM bridge. The examples below run identically on Windows, macOS and Linux.

Step 1: write the formula

Create a small workbook, add data, then assign a formula to the cell that should carry it:

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])
ws.append(["B-200", 2, 49.50])

ws["D2"] = "=B2*C2"
ws["D3"] = "=B3*C3"
ws["C5"] = "Total"
ws["D5"] = "=SUM(D2:D3)"

wb.save("orders.xlsx")
print(ws["D5"].value)     # '=SUM(D2:D3)'

The print returns the formula text, because openpyxl has no calculation engine — it writes what you give it and reads back the same string. Open the saved file in Excel or LibreOffice and D5 shows 178.84.

Any Excel function works the same way, including ones openpyxl knows nothing about:

ws["E2"] = '=IF(D2>100,"large","small")'
ws["E3"] = '=TEXT(TODAY(),"yyyy-mm-dd")'
ws["E4"] = '=IFERROR(VLOOKUP(A2,Prices!$A$2:$B$50,2,FALSE),0)'

Note the outer single quotes in Python whenever the formula itself contains double quotes. That is a Python quoting matter, not an Excel one; the string that reaches the file is identical either way.

Step 2: avoid the Text-format trap

If a cell's number format is Text, Excel shows the formula rather than running it — a common surprise when writing into a template where someone formatted a column as text years ago. Reset the format before you assign:

from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
ws = wb["Orders"]

cell = ws["D2"]
if cell.number_format == "@":          # "@" is Excel's Text format code
    cell.number_format = "General"

cell.value = "=B2*C2"
wb.save("filled.xlsx")

The same trap has a second face: a cell that already holds a formula keeps its number format when you overwrite it, so a currency-formatted cell stays currency-formatted. That is usually welcome, but it means a percentage cell that inherits 0.0% will display 7996.0% for a value of 79.96 until you set the format you actually want.

Step 3: build references from indexes

Hardcoding D2 works for one cell. For a generated report, translate column numbers into letters so the formula follows the layout:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

headers = ["SKU", "Quantity", "Unit_Price", "Line_Total"]
data = [("A-100", 4, 19.99), ("B-200", 2, 49.50), ("C-300", 7, 12.25)]

wb = Workbook()
ws = wb.active
ws.append(headers)

qty = get_column_letter(headers.index("Quantity") + 1)          # 'B'
price = get_column_letter(headers.index("Unit_Price") + 1)      # 'C'
total = get_column_letter(headers.index("Line_Total") + 1)      # 'D'

for row in data:
    ws.append(row)
    r = ws.max_row
    ws[f"{total}{r}"] = f"={qty}{r}*{price}{r}"

ws[f"{price}{ws.max_row + 2}"] = "Total"
ws[f"{total}{ws.max_row}"] = f"=SUM({total}2:{total}{len(data) + 1})"

wb.save("orders_indexed.xlsx")

Deriving the letters from headers.index(...) means a column added at position two does not silently point the multiplication at the wrong data. The same idea applies to the cell reference of a chart series and to any range you hand to conditional formatting.

Step 4: verify what landed in the file

Reading the file back proves the formula was written and lets you assert on it in a test:

from openpyxl import load_workbook

wb = load_workbook("orders_indexed.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=2, max_row=4, min_col=4, max_col=4):
    for cell in row:
        assert isinstance(cell.value, str) and cell.value.startswith("="), (
            f"{cell.coordinate} is not a formula: {cell.value!r}"
        )

print("formulas present:", [c.value for c in ws["D"][1:4]])

If a later job needs the number rather than the formula, either open the file once in a spreadsheet application, or compute the figure in Python and write a literal instead. Reading formula results with data_only goes through that decision in detail.

Step 5: add formulas to a workbook that already exists

Most real jobs do not create the file — they open last month's workbook, append the new rows and extend the formulas to cover them. Loading in the default mode keeps every existing formula intact, so you only write the new ones:

from openpyxl import load_workbook

new_rows = [("D-400", 5, 8.75), ("E-500", 3, 22.40)]

wb = load_workbook("orders_indexed.xlsx")
ws = wb["Sheet"]

# The old total row sits at the bottom; drop it before appending data
last_data_row = max(
    cell.row for cell in ws["A"] if cell.value and str(cell.value).startswith(("A-", "B-", "C-"))
)
ws.delete_rows(last_data_row + 1, ws.max_row - last_data_row)

for sku, qty, price in new_rows:
    ws.append([sku, qty, price])
    r = ws.max_row
    ws[f"D{r}"] = f"=B{r}*C{r}"

total_row = ws.max_row + 2
ws[f"C{total_row}"] = "Total"
ws[f"D{total_row}"] = f"=SUM(D2:D{ws.max_row - 2})"

wb.save("orders_extended.xlsx")

The delete-then-rebuild pattern is deliberate. Appending under an existing total row leaves the total pointing at a range that no longer covers the data, which is the single most common cause of a report that quietly under-reports. Removing the summary block, appending, and rewriting the summary is both simpler and safer than trying to patch the range inside the old formula string.

If the workbook you are extending was produced by another team, load it once and check what is actually in the summary cells before you assume the layout:

from openpyxl import load_workbook

wb = load_workbook("orders_indexed.xlsx")
ws = wb.active
formulas = {
    cell.coordinate: cell.value
    for row in ws.iter_rows()
    for cell in row
    if isinstance(cell.value, str) and cell.value.startswith("=")
}
for coord, formula in formulas.items():
    print(coord, formula)

That inventory is a good thing to log on every run. When a report breaks three months from now, the diff between two runs' formula inventories usually points straight at the change.

Common pitfalls and gotchas

Performance and scale notes

Formula strings are cheap to write — a few hundred thousand assignments cost far less than the styling around them, because each one is stored as a short string rather than a computed number. The cost lands on the reader: a workbook with 200,000 volatile formulas takes many seconds to open and recalculates on every edit. When a sheet is that large, compute the column in pandas and write literal values, and keep formulas for the handful of summary cells a reader might want to change. write_only=True mode also accepts formula strings, which lets you stream a large sheet out without holding it in memory; the trade-off is that you can no longer revisit a cell once its row has been written, so every formula has to be built at the moment the row is appended.

Conclusion

Writing a formula is one assignment of a string beginning with =, and openpyxl stores it verbatim. Everything that goes wrong afterwards traces back to three things: a Text number format that stops Excel parsing the string, references built by hand instead of from the row you are writing, and reading the file back in data_only mode and finding text-free Nones. Set the format, generate the references, assert on the reloaded file, and the formulas you write will be the formulas your readers see.

Frequently asked questions

Do I need to escape anything in the formula string? No. The string is stored verbatim, so write it exactly as you would type it in Excel. Use an f-string or normal concatenation to build the cell references.

Why does Excel display my formula instead of calculating it? The cell's number format is Text. Set cell.number_format = "General" before assigning the formula, or clear the Text format on the range you are writing into.

Can I write a formula to a merged cell? Yes, but write it to the top-left cell of the merged range. Assigning to any other cell in the range raises an AttributeError because those cells are read-only MergedCell objects.

How do I write a formula that references another sheet? Prefix the reference with the sheet name and wrap the name in single quotes if it contains spaces, for example ='Q1 Sales'!B2.

Related

Up to the parent guide:

• Working with Excel Formulas in Python — the full round trip, from writing to freezing.

Related guides:

• Read Formula Results with openpyxl data_only — what to do when results come back as None.
• Add SUM and AVERAGE Formulas with Python — totals that track a growing table.
• Copy a Formula Down a Column in openpyxl — fill a pattern across every data row.
• Using openpyxl for Excel File Manipulation — the workbook, sheet and cell model.