Adding Formulas and Number Formats From Python Code

People who receive a database export often want to keep working in it. They want a total that updates when they change a number, a percentage they can read, a date in the form they use. All of that is formulas and formats, and writing them from Python is easy once you know three rules about what the file stores. This post gives the rules and then the code for both common libraries. It is a spoke of sending database results to Excel.

Three rules about formulas

Write them in the file's language, not the reader's. A formula in the XML uses English function names and commas between arguments, always. A German Excel shows SUMME(A1;A2) to its user while the file holds SUM(A1,A2). So write =SUM(B2:B10) in your code and do not try to translate it to the reader's locale.

A formula is text that starts with an equals sign. In openpyxl, assigning a string beginning with = to a cell makes it a formula. In XlsxWriter, write_formula does, and the generic write() does too when the string starts with =. That convenience is also a risk for text from users (sending database results to Excel properly every time).

The file keeps a recipe and a remembered answer. Excel stores the formula and the last computed value. A library writes the formula and, unless you give one, no value. Excel recalculates when it opens the file, so people see numbers. A program that reads the file without recalculating, such as pandas or a database loader, sees empty cells (what happens to formulas when code reads an Excel file).

Formulas in openpyxl

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Item", "Qty", "Price", "Total"])
rows = [("Notebook", 3, 4.5), ("Pen", 10, 1.2), ("Stapler", 1, 12.0)]
for i, (item, qty, price) in enumerate(rows, start=2):
    ws.append([item, qty, price, f"=B{i}*C{i}"])

last = len(rows) + 1
ws[f"A{last + 1}"] = "Sum"
ws[f"D{last + 1}"] = f"=SUM(D2:D{last})"
wb.save("formulas.xlsx")

Cells D2 to D4 hold =B2*C2 and so on, and D5 holds =SUM(D2:D4). Opened in Excel, they show 13.5, 12 and 12, and the sum 37.5. Read back with data_only=True, openpyxl returns None for those cells, because no value was stored. openpyxl cannot supply one, since it does not evaluate formulas. If the file will be read by a program, you have three choices: write the values as numbers and keep the formula in a separate column, recalculate the file with a spreadsheet program before delivery, or use XlsxWriter, which can store the result.

For functions introduced in later Excel versions, such as XLOOKUP, FILTER and LET, openpyxl needs the internal prefix: write =_xlfn.XLOOKUP(A2, F:F, G:G). Without it Excel shows #NAME? until the cell is edited. Dynamic array functions that spill need more than a prefix. For generated files, the classic functions (SUM, SUMIF, COUNTIF, INDEX, MATCH, VLOOKUP, IF, ROUND) work everywhere.

Formulas in XlsxWriter, with cached values

import xlsxwriter

wb = xlsxwriter.Workbook("formulas.xlsx")
ws = wb.add_worksheet("Orders")
money = wb.add_format({"num_format": "#,##0.00"})
ws.write_row(0, 0, ["Item", "Qty", "Price", "Total"])

rows = [("Notebook", 3, 4.5), ("Pen", 10, 1.2), ("Stapler", 1, 12.0)]
for r, (item, qty, price) in enumerate(rows, start=1):
    ws.write_string(r, 0, item)
    ws.write_number(r, 1, qty)
    ws.write_number(r, 2, price, money)
    ws.write_formula(r, 3, f"=B{r + 1}*C{r + 1}", money, qty * price)

ws.write_string(4, 0, "Sum")
ws.write_formula(4, 3, "=SUM(D2:D4)", money, sum(q * p for _, q, p in rows))
wb.close()

The fourth argument to write_formula is the cached value: 13.5 for the first row, and 37.5 for the sum. Excel ignores it and recalculates on load, while programs that do not recalculate read it as the cell's value. The cost is that you compute the value in Python as well, and the two must agree; a mismatch between the cache and the formula is a latent bug. A cheap safeguard is to compute the cache with the same inputs you wrote to the cells.

XlsxWriter handles the newer functions with the use_future_functions workbook option, which adds the _xlfn. prefixes automatically for known functions:

wb = xlsxwriter.Workbook("modern.xlsx", {"use_future_functions": True})

With that option, =XLOOKUP(...) is written correctly. Dynamic arrays use write_dynamic_array_formula.

Number formats

A number format is a pattern that tells Excel how to display a value. It does not change the stored value. The most useful ones:

Notice the percent row: the cell stores 0.125, and the format multiplies by 100 for display. A value written as 12.5 with a percent format would show 1250.0%. Notice also the zero-padding row. The format 00000 makes the number 42 look like 00042, but the stored value is the number 42, and anything that reads the cell gets 42. For identifiers that must keep their zeros, store text.

Apply formats in openpyxl through the cell's number_format:

ws["C2"].number_format = "#,##0.00"
ws["E2"].number_format = "0.0%"
for cell in ws["F"][1:]:
    cell.number_format = "yyyy-mm-dd"

In XlsxWriter, formats are objects created once and passed to write calls or set_column. Setting the format on a column, not on every cell, is cheaper and covers rows added later (writing a DataFrame to Excel with formatting).

Table references and names

If you write an Excel table, formulas can use structured references that survive added rows, such as =SUM(Orders[Total]). XlsxWriter's add_table supports a formula per column with the formula key:

ws.add_table(0, 0, len(rows), 3, {
    "name": "Orders",
    "columns": [
        {"header": "Item"},
        {"header": "Qty"},
        {"header": "Price", "format": money},
        {"header": "Total", "format": money, "formula": "=[@Qty]*[@Price]"},
    ],
})

The [@Qty] notation means the Qty cell in the same row. The formula fills down the column and extends automatically when a reader adds a row, which is what makes a table pleasant to use. Cached values for such formulas are not set through add_table; if a non-recalculating reader needs them, write the cells yourself.

Defined names are another way to make formulas readable: wb.define_name("TaxRate", "=Settings!$B$2") in XlsxWriter, and a DefinedName in openpyxl. A formula =B2*TaxRate then explains itself (Excel named ranges and tables explained).

Good habits

Keep formulas simple and local. A workbook is read by people who click on cells. Short formulas that refer to visible cells are auditable. A formula that nests six functions and refers to three other workbooks is a liability.

Round where the business rounds. If an invoice total is the sum of rounded line amounts, write ROUND in the line formula, so the sheet matches the system. Formatting alone hides the difference until a total is off by a cent.

Avoid volatile functions in exports. NOW(), TODAY() and RAND() change every time the file opens, so the saved cache is already wrong. Write the date as a value.

Do not hard-code row counts you cannot know. Generate the range from the data's length, as in the examples, and test with zero rows and one row.

Test by reading back. Recalculate the file with LibreOffice in headless mode, open it with openpyxl using data_only=True, and compare the results with Python's own arithmetic. Any mismatch is a bug in the formula text.

When writing formulas from code is the wrong choice

The consumer is a program. Formulas add nothing for a loader, and a missing cache breaks it. Write values.

The logic is complex or sensitive. A model in a spreadsheet is hard to test and review. Keep the calculation in code or SQL, and export the results (translating Excel formulas into SQL).

Readers will not recalculate safely. Some viewers, such as mail previewers and mobile quick-look tools, show cached values only. If the cache is absent they show blanks.

The workbook is large. Hundreds of thousands of formulas slow opening. Convert them to values for archives.

Formula syntax and supported functions are described in XlsxWriter's working with formulas page, and openpyxl's approach in its simple usage guide. To check a small extract as JSON, use the CSV to JSON converter.