Working with Formulas and Formula Cells in openpyxl

When you load an Excel workbook with openpyxl, the way you access a formula cell depends on the data_only parameter and whether the file contains cached values. This distinction is central to working with formulas correctly, especially when you need to extract calculated results or generate new formulas programmatically.

Reading Formulas vs. Cached Values

openpyxl does not evaluate formulas. When you load a workbook with data_only=False (the default), cell.value returns the formula string exactly as stored, including the leading equals sign. For example:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb.active
print(ws["C1"].value)  # "=SUM(A1:B1)"

If you load the same workbook with data_only=True, openpyxl returns the cached value that the spreadsheet application stored when it last saved the calculated workbook:

wb_data = load_workbook("report.xlsx", data_only=True)
ws_data = wb_data.active
print(ws_data["C1"].value)  # 42 (if the cached value exists)

Cached values are written when the file is opened and saved by a spreadsheet application that calculates formulas. Files generated purely by openpyxl do not contain cached values, so data_only=True will return None for any formula cell.

Writing Formulas to Cells

To write a formula, assign a string that starts with = to the cell's value attribute. openpyxl stores it as a formula without attempting to parse or validate it:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = 10
ws["A2"] = 20
ws["A3"] = "=SUM(A1:A2)"
wb.save("formula.xlsx")

When the file is opened in Excel, the formula is evaluated and the result appears. openpyxl itself will not compute the sum. This behavior is important: if you read the file back with openpyxl before opening it in Excel, the formula cell will contain the string =SUM(A1:A2), not the numeric result.

Choosing the Right data_only Mode

Your choice of data_only depends on what you need from the workbook:

If you need both formulas and their cached values, you must load the workbook twice—once in each mode. There is no way to retrieve both from a single load_workbook call.

Handling Missing Cached Values

A common problem appears when a workbook is generated programmatically and then read with data_only=True. Because no spreadsheet engine has calculated the formulas, the cached values are absent, and every formula cell returns None:

wb = Workbook()
ws = wb.active
ws["A1"] = 1
ws["A2"] = 2
ws["A3"] = "=A1+A2"
wb.save("no_cache.xlsx")

wb_read = load_workbook("no_cache.xlsx", data_only=True)
print(wb_read.active["A3"].value)  # None

To populate cached values, you must open and save the file in a spreadsheet application that calculates formulas, such as Microsoft Excel, LibreOffice Calc, or Google Sheets (via export). Alternatively, you can compute the values in Python and write both the formula and the result, but openpyxl does not provide a built-in way to store a cached value alongside a formula.

Formula Syntax and Locale Considerations

openpyxl writes formulas using English function names and comma separators, regardless of the system locale. This is the format Excel expects in the underlying XML. If you construct formulas with localized function names (e.g., SUMME in German) or semicolon separators, the file may be flagged as corrupt or the formula may not evaluate correctly. Always use the canonical English syntax:

ws["B1"] = "=IF(A1>10,\"high\",\"low\")"  # canonical
ws["B2"] = "=WENN(A1>10;\"high\";\"low\")"  # localized with semicolons; avoid this

openpyxl does not translate formulas. If you need to support multiple locales, generate the English version and let the spreadsheet application handle display localization.

Performance with Large Formula Sets

When a workbook contains thousands of formulas, loading it with openpyxl can consume significant memory because each cell object holds metadata. If you only need to read formulas or values, use read_only=True to stream cells instead of building the full object graph:

wb = load_workbook("large.xlsx", read_only=True, data_only=True)
ws = wb.active
for row in ws.iter_rows(values_only=True):
    # process each row without holding the whole sheet in memory
    pass
wb.close()

Keep in mind that read_only mode does not support writing. It is intended for read-heavy workflows. For writing many formulas, the normal mode is fine, but be aware that openpyxl's memory usage grows with the number of cells.

Preserving Formulas When Modifying a Workbook

If you load a workbook with data_only=False, modify some cells, and save, openpyxl writes the formulas back unchanged. However, if you load with data_only=True, the formulas are lost—only the cached values are loaded, and saving writes those values as static data. This is a frequent source of data loss:

wb = load_workbook("report.xlsx", data_only=True)  # loads cached values only
ws = wb.active
ws["C1"] = "new value"
wb.save("report_modified.xlsx")  # original formulas are lost; saved as static values

To preserve formulas while updating specific cells, always load with data_only=False. If you also need the calculated results, load a second workbook instance with data_only=True for reading, but keep the original instance for writing.

Detecting Whether a Cell Contains a Formula

openpyxl does not expose a direct is_formula property, but you can check the data type of the cell's value. A formula cell's value is a string starting with =. This works when data_only=False:

def is_formula(cell):
    return isinstance(cell.value, str) and cell.value.startswith("=")

With data_only=True, a formula cell with a cached value returns that value, often as a number but sometimes as a string, so this string-based check cannot reliably distinguish formulas from static values. If you need to distinguish formulas from static values, load the workbook with data_only=False. This is particularly useful when auditing a spreadsheet to identify all calculated fields before making changes.