Fix "Number Stored as Text" Warnings in Excel Output

The report looks right until someone clicks a cell. A small green triangle sits in the corner of every amount, the numbers are left-aligned, SUM returns zero, and a pivot table counts the column instead of adding it up. Excel is telling you that those cells contain text that looks like a number. The script did exactly what it was asked: the values were strings in Python, so they were written as strings. This guide, part of Troubleshooting Common Python Excel Errors, fixes the cause, and explains when the warning is actually correct.

Prerequisites

pip install pandas openpyxl xlsxwriter

This reproduces the problem with a DataFrame of strings, the shape data takes after reading a CSV without dtypes, scraping a web page or parsing a text report:

import pandas as pd

orders = pd.DataFrame({
    "OrderID": ["00017", "00018", "00019"],
    "Amount": ["1,250.50", "980", "2,015.25"],
    "Qty": ["3", "12", "7"],
    "Pct": ["12%", "8.5%", "10%"],
})
orders.to_excel("orders_text.xlsx", index=False)

Step 1 — Confirm what was written

Open the file with openpyxl and look at each cell's data_type: "n" is a number and "s" is a string. Checking in code is faster and more reliable than clicking cells.

from openpyxl import load_workbook

sheet = load_workbook("orders_text.xlsx").active
for cell in sheet[2]:
    print(cell.coordinate, repr(cell.value), cell.data_type)
# A2 '00017' s   B2 '1,250.50' s   C2 '3' s   D2 '12%' s

Every cell is a string, which is what triggers the warning. The same check is the quickest way to prove the fix worked, and a good line to add to an automated test of report output as described in Test Excel Output with pytest.

Step 2 — Convert real quantities before writing

Numbers belong in Excel as numbers. Convert each quantity column with pd.to_numeric, stripping thousands separators and percent signs first. Percentages become fractions so that a 0% format displays them correctly.

def to_number(series: pd.Series, percent: bool = False) -> pd.Series:
    cleaned = series.astype("string").str.replace(",", "", regex=False).str.strip()
    if percent:
        cleaned = cleaned.str.rstrip("%")
    numbers = pd.to_numeric(cleaned, errors="coerce")
    return numbers / 100 if percent else numbers

clean = orders.assign(
    Amount=to_number(orders["Amount"]),
    Qty=to_number(orders["Qty"]).astype("Int64"),
    Pct=to_number(orders["Pct"], percent=True),
)
print(clean.dtypes.to_dict())

with pd.ExcelWriter("orders_numbers.xlsx", engine="openpyxl") as writer:
    clean.to_excel(writer, index=False, sheet_name="Orders")
    ws = writer.sheets["Orders"]
    for row in range(2, len(clean) + 2):
        ws[f"B{row}"].number_format = "#,##0.00"
        ws[f"D{row}"].number_format = "0.0%"

errors="coerce" turns anything that cannot be parsed into a missing value instead of raising — follow it with a count of new missing values if silent data loss would matter. The number formats restore the separators and percent signs as display, which is where they belong. The same conversion on the reading side is covered in Convert Excel Text Columns to Numbers with pandas.

Step 3 — Keep identifiers as text on purpose

OrderID looks numeric but is not a quantity. Converting it would drop the leading zeros and invite someone to sum order numbers. The warning triangle on an ID column is Excel being cautious about a value that is correctly text. Leave the column as strings — which pandas already does — and decide separately whether to hide the warning.

check = load_workbook("orders_numbers.xlsx")["Orders"]
print([(cell.coordinate, cell.value, cell.data_type) for cell in check[2]])
# OrderID stays 's'; Amount, Qty and Pct are now 'n'

A useful rule: if you would never add, average or compare the values as magnitudes — IDs, postcodes, phone numbers, account codes — they are text. Keeping them as text end to end avoids a class of bugs described in Keep Leading Zeros When Reading Excel with pandas.

Step 4 — Beware of strings_to_numbers

xlsxwriter has a workbook option, strings_to_numbers, that converts any string that parses as a number while writing. It looks like a one-line fix, and on mixed data it is a trap: it converts identifiers along with quantities, while missing the amounts that contain a thousands separator.

with pd.ExcelWriter("orders_auto.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_numbers": True}}) as writer:
    orders.to_excel(writer, index=False)

auto = load_workbook("orders_auto.xlsx").active
print([(cell.value, cell.data_type) for cell in auto[2]])
# [(17, 'n'), ('1,250.50', 's'), (3, 'n'), ('12%', 's')]

The order ID lost its zeros, while 1,250.50 and 12% are still text. Converting explicitly per column, as in Step 2, is more code and far more predictable. Reserve strings_to_numbers for data where every numeric-looking string really is a plain number.

Step 5 — Silence the warning where text is correct

For an ID column that must stay text, xlsxwriter can tell Excel to ignore the "number stored as text" check for a range, so readers do not see a column of triangles. openpyxl has no equivalent option when writing.

with pd.ExcelWriter("orders_final.xlsx", engine="xlsxwriter") as writer:
    clean.to_excel(writer, index=False, sheet_name="Orders")
    book, sheet = writer.book, writer.sheets["Orders"]
    sheet.set_column("B:B", 12, book.add_format({"num_format": "#,##0.00"}))
    sheet.set_column("D:D", 8, book.add_format({"num_format": "0.0%"}))
    sheet.ignore_errors({"number_stored_as_text": f"A2:A{len(clean) + 1}"})

final = load_workbook("orders_final.xlsx")["Orders"]
print([(cell.value, cell.data_type) for cell in final[2]])

The ignore range should cover exactly the text columns and nothing else, so that a genuine mistake elsewhere in the sheet still shows its warning. Column formats with set_column are covered in Write a Formatted Excel Report with xlsxwriter.

Step 6 — Catch it before it ships

Add a check to the end of the report job that fails when a column meant to be numeric contains text cells. It takes a few lines and prevents the kind of report that looks fine and sums to zero.

def assert_numeric_columns(path: str, sheet: str, columns: list[str]) -> None:
    ws = load_workbook(path, read_only=True)[sheet]
    rows = ws.iter_rows(values_only=False)
    header = [cell.value for cell in next(rows)]
    positions = {name: header.index(name) for name in columns}
    offenders = []
    for row in rows:
        for name, position in positions.items():
            cell = row[position]
            if cell.value is not None and cell.data_type != "n":
                offenders.append(f"{name}{cell.row}={cell.value!r}")
    if offenders:
        raise ValueError("text in numeric columns: " + ", ".join(offenders[:10]))

assert_numeric_columns("orders_final.xlsx", "Orders", ["Amount", "Qty", "Pct"])
print("numeric columns verified")

Step 7 — Fix the source instead of the symptom

Converting at the end of the pipeline works, but the durable fix is to stop numbers becoming strings in the first place. Three sources account for nearly every case, and each has a small change upstream.

from io import StringIO

# 1. CSV input read without telling pandas about thousands separators
csv_text = 'OrderID,Amount\n00017,"1,250.50"\n00018,980\n'
raw = pd.read_csv(StringIO(csv_text))
typed = pd.read_csv(StringIO(csv_text), dtype={"OrderID": "string"}, thousands=",")
print(raw.dtypes.to_dict(), typed.dtypes.to_dict())

# 2. Formatting numbers for display inside the DataFrame
report = clean.copy()
report["Amount (display)"] = report["Amount"].map("{:,.2f}".format)   # text on purpose — keep it out of sums
print(report[["Amount", "Amount (display)"]].dtypes.to_dict())

# 3. Building rows from strings, e.g. parsed text files
lines = ["00020|315.75|4", "00021|88|1"]
parsed = pd.DataFrame([line.split("|") for line in lines], columns=["OrderID", "Amount", "Qty"])
parsed = parsed.astype({"Amount": "float64", "Qty": "int64"})
print(parsed.dtypes.to_dict())

read_csv with thousands="," parses 1,250.50 as a number directly, and dtype={"OrderID": "string"} keeps the ID text, so neither column needs repair later. Formatting numbers as strings for display belongs in the number format, not in the data; when a text version is genuinely needed — for an email body, say — keep it in a separate column and never write it where people expect to calculate. And anything parsed from text arrives as strings until astype says otherwise, so type the columns at the moment they are created.

Common pitfalls

Performance and scale notes

pd.to_numeric and the .str cleanup are vectorised and fast even for millions of values; the conversion adds almost nothing to a report job. Setting a number format per cell with openpyxl is slower on large sheets than a single set_column format with xlsxwriter, so prefer xlsxwriter for large outputs. The verification step reads in read-only mode and streams the sheet, which keeps it cheap to run on every report.

Conclusion

"Number stored as text" in a script's output means the values were strings when they were written. Convert quantity columns explicitly — strip separators and percent signs, use pd.to_numeric, divide percentages by 100 — and restore the look with number formats. Keep identifiers as text deliberately, avoid strings_to_numbers on mixed data, silence the warning for true text columns with xlsxwriter's ignore_errors, and verify numeric columns at the end of the job so the problem never reaches a reader.

Frequently asked questions

Why does pandas write my numbers as text? Because they are strings in the DataFrame, usually from a CSV read without dtypes, text parsing or string formatting. Check df.dtypes; object or string columns are written as text.

Can openpyxl hide the green triangle warning? Not when writing. openpyxl does not write the ignored-errors setting, so use xlsxwriter's ignore_errors, or convert the values to numbers if they are real quantities.

Will formatting a text cell as Number fix it? No. A number format changes how numbers display; a text value stays text. The value itself has to be written as a number.

Should phone numbers be numbers or text? Text. They are identifiers with meaningful leading zeros and plus signs, and nobody calculates with them.

Related

• Up one level: Troubleshooting Common Python Excel Errors — decoding the errors scripts hit most often.
• Fix IllegalCharacterError When Writing Excel with openpyxl — another text problem that appears at write time.
• Format Excel Cells as Currency with Python — formats for the converted amounts.
• Format Numbers as Percentages in Excel with Python — fractions and percent formats.
• Check Excel Data Types with pandas — dtype checks before writing.