Working with Formulas and Formula Cells in openpyxl

When you load an Excel workbook with openpyxl, the way you access formula cells depends on the data_only parameter and whether the file contains cached values. This distinction is central to working with python openpyxl formulas and formula cells 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 attempts to return the cached value that Excel or LibreOffice saved when the file was last calculated:

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)

The cached value is only present if the file was 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\")"  # correct
ws["B1"] = "=WENN(A1>10;\"high\";\"low\")"  # not recommended

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)  # formulas become values
ws = wb.active
ws["C1"] = "new value"
wb.save("report_modified.xlsx")  # all original formulas are now 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 reliably when data_only=False:

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

When data_only=True, formula cells that have a cached value return a numeric or string result, so this check fails. If you need to distinguish formulas from static values, you must load the workbook with data_only=False. This is particularly useful when auditing a spreadsheet to identify all calculated fields before making changes.