Fix Formulas Showing as Text in Excel Output from Python

The report opens, and instead of a total the cell shows the formula itself:

=SUM(B2:B9)

Excel shows formula text for one of two reasons: the cell was stored as a string that happens to start with =, or the sheet was saved with Excel's "Show Formulas" view switched on. Which one is easy to find out, and each has a short list of causes in Python code. This page diagnoses the file first, then fixes each cause for openpyxl, xlsxwriter and pandas. It is part of Troubleshooting Common Python Excel Errors.

Prerequisites

pip install openpyxl xlsxwriter pandas

report.xlsx is a small report whose total row shows formula text: three formula cells, written by a script that built the formula strings from a template.

Step 1 — Is the cell a formula or text?

Inside an .xlsx file a formula cell carries an <f> element; a text cell carries a string. openpyxl reports the difference as the cell's data_type: f for formula, s for string. Check every cell whose value starts with = after trimming:

from openpyxl import load_workbook

def formula_like_text(path):
    wb = load_workbook(path)
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for c in row:
                if isinstance(c.value, str) and c.value.strip().lstrip("'").startswith("="):
                    yield ws.title, c.coordinate, c.data_type, repr(c.value)

for sheet, cell, kind, value in formula_like_text("report.xlsx"):
    print(f"{sheet}!{cell:4} type={kind}  {value}")

B8 is a real formula (f). B6 and B7 are strings (s): one starts with a space, the other with an apostrophe. Excel displays both exactly as stored, text and all. If every cell reports f but Excel still shows formulas, skip to Step 4.

Cause 1 — A character before the equals sign

openpyxl treats a string as a formula only if its first character is =. A leading space, a non-breaking space, or an apostrophe copied from a spreadsheet makes it text. The apostrophe is worse than it looks: in Excel a typed apostrophe is a hidden "treat as text" marker, but written from Python it is part of the value, so the cell shows '=AVERAGE(B2:B4) including the quote. Clean formula strings before writing them:

def as_formula(text):
    cleaned = text.replace(" ", " ").strip().lstrip("'")
    if not cleaned.startswith("="):
        raise ValueError(f"not a formula: {text!r}")
    return cleaned

wb = load_workbook("report.xlsx")
ws = wb["Report"]
for coord in ("B6", "B7"):
    ws[coord] = as_formula(ws[coord].value)
    print(coord, ws[coord].data_type, ws[coord].value)
wb.save("report_fixed.xlsx")

Templates are the usual source: formula strings kept in a config file, a CSV or another sheet pick up spaces and quotes along the way. A formula pasted into a CSV or a YAML file by hand keeps whatever came with it, including a leading space from indentation or a quote character from the editor, and nothing looks wrong until the report opens. Clean them where they are loaded, and raise on anything that does not start with = after cleaning, so a broken template stops the run instead of producing a report full of text.

Cause 2 — The writer was told not to make formulas

xlsxwriter's write() turns strings starting with = into formulas by default, and pandas passes DataFrame strings through write(). Code that guards against formula injection often switches that off with the strings_to_formulas option, and then the report's own formula columns come out as text too. Compare the stored cell type:

import pandas as pd

df = pd.DataFrame({"Sales": [12500, 9800], "Double": ["=A2*2", "=A3*2"]})
df.to_excel("default.xlsx", index=False, engine="xlsxwriter")
with pd.ExcelWriter("no_formulas.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
    df.to_excel(writer, index=False)
for path in ("default.xlsx", "no_formulas.xlsx"):
    print(path, load_workbook(path).active["B2"].data_type)

Keep the protection for data and write the formulas you intend explicitly with write_formula, which ignores the option:

with pd.ExcelWriter("safe_and_working.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
    df[["Sales"]].to_excel(writer, index=False)
    ws = writer.sheets["Sheet1"]
    ws.write("B1", "Double")
    for row in range(2, len(df) + 2):
        ws.write_formula(f"B{row}", f"=A{row}*2")
print(load_workbook("safe_and_working.xlsx").active["B2"].data_type)

The same applies to write_string(), which always writes text: use it for data, never for formulas.

The combination that works for most reports is the middle and right panels together: every value that came from data goes through a path that cannot create formulas, and every formula the report means to contain is written with write_formula, so it is obvious in the code which cells calculate.

Cause 3 — Show Formulas is switched on

If every cell is a real formula and Excel still shows formula text everywhere, the sheet was saved with the "Show Formulas" view on, the button of that name on Excel's Formulas tab. The setting is stored per sheet, so a template saved in that state passes it to every report built from it. openpyxl exposes it as sheet_view.showFormulas:

tpl = load_workbook("report_fixed.xlsx")
tpl["Report"].sheet_view.showFormulas = True        # what a template saved in this mode contains
tpl.save("template_show_formulas.xlsx")

wb = load_workbook("template_show_formulas.xlsx")
for ws in wb.worksheets:
    print(ws.title, "showFormulas:", ws.sheet_view.showFormulas)
    ws.sheet_view.showFormulas = False
wb.save("template_fixed.xlsx")
print("after:", load_workbook("template_fixed.xlsx")["Report"].sheet_view.showFormulas)

This one is also visible in Excel: the columns become wider and every formula in the sheet shows as text, not just the ones your script wrote.

A check for your tests

The fixes above are easy once the problem is found; the point is to find it before a recipient does. The diagnostic function from Step 1 turns into a test with one more line: any formula-like text, or any sheet saved in Show Formulas mode, fails the build. Run it on the output of every report in the test suite:

def assert_formulas_are_formulas(path):
    problems = [f"{s}!{c} stored as text: {v}" for s, c, kind, v in formula_like_text(path) if kind != "f"]
    wb = load_workbook(path)
    problems += [f"{ws.title}: Show Formulas is on" for ws in wb.worksheets if ws.sheet_view.showFormulas]
    assert not problems, "\n".join(problems)

for path in ("report.xlsx", "report_fixed.xlsx", "template_show_formulas.xlsx", "template_fixed.xlsx"):
    try:
        assert_formulas_are_formulas(path)
        print(f"{path:28} ok")
    except AssertionError as e:
        print(f"{path:28} FAIL: {str(e).splitlines()[0]}")

Deliberate text that starts with =, such as a note explaining a formula, will trip the check; keep an allow-list of those cells rather than weakening the test. Test Excel Output with pytest shows how to wire checks like this into a test suite.

Look-alikes that are not formula text

Two other symptoms get reported as "my formula doesn't work" and have different causes:

• #NAME? instead of a result. The cell is a formula, but Excel does not recognise a function in it: a localised name such as SUMME, a newer function Excel wants with a prefix, or a typo. Files always store English function names with comma separators, whatever the language of Excel. See Fix #NAME? in Formulas Written from Python (XLOOKUP, TEXTJOIN, IFS).
• 0 or an empty cell in a preview. The formula is fine, but the file has no calculated result stored and the viewer (a mail preview, a web viewer, pandas with openpyxl) does not calculate. xlsxwriter stores 0 as the cached result. Excel recalculates on open; other tools may not. Recalculate Excel Formulas Without Excel in Python fills in the results.

Common pitfalls

• Stripping only spaces. Non-breaking spaces and apostrophes survive strip(); handle them explicitly.
• Turning off strings_to_formulas globally. Protect data columns, then write intended formulas with write_formula.
• Semicolons as separators. Write =SUM(A1,A2); separators and function names in files are always the English ones.
• Fixing the report, not the template. If Show Formulas or text-formatted formula cells come from a template, fix the template once.

Conclusion

Open the file with openpyxl and check each suspicious cell's data_type. A string (s) means the formula was written as text: strip leading spaces, non-breaking spaces and apostrophes, and write intended formulas with write_formula when strings_to_formulas is off. A real formula (f) that still shows as text means the sheet has Show Formulas enabled; set sheet_view.showFormulas = False.

Frequently asked questions

Why does my formula show as text in Excel after writing it with Python? Usually the value was written as a string: a space or apostrophe before the equals sign, or a writer configured not to convert strings to formulas. Check the cell's data_type with openpyxl; it should be f for a formula.

How do I write a formula with openpyxl? Assign a string that starts exactly with an equals sign, such as ws"B6" = "=SUM(B2:B4)". openpyxl stores it as a formula; Excel calculates it when the file is opened.

Why does pandas write my formula strings as text? The xlsxwriter option strings_to_formulas has been set to False, often to prevent formula injection. Write the formula cells separately with worksheet.write_formula.

How do I turn off Show Formulas in a workbook from Python? Set ws.sheet_view.showFormulas = False on each worksheet with openpyxl and save the file.

Related

• Up one level: Troubleshooting Common Python Excel Errors — the other common errors.
• Fix #NAME? in Formulas Written from Python (XLOOKUP, TEXTJOIN, IFS) — formulas that calculate to an error.
• Prevent Formula Injection in Excel Exports — the other side of strings_to_formulas.
• Recalculate Excel Formulas Without Excel in Python — filling in cached results.
• Fix "Number Stored as Text" Warnings in Excel Output — the numeric version of the same mix-up.