Fixing Python xlsxwriter That Writes Numbers as Text and Breaks SUM Formulas

You generate an Excel file with xlsxwriter, open it, and everything looks right — the numbers are there, aligned to the left, and the column even has a currency format. Then you type a SUM formula and get zero. Excel is treating your numbers as text, and xlsxwriter handed them to Excel that way without a single warning.

This is one of the most common xlsxwriter bugs developers hit in production, and it's almost always caused by one of three things: passing a string where a number is expected, letting Python's type inference go unchecked, or using write() when the value is ambiguous. This guide walks you through each cause and its fix.

What You'll Learn

• Why xlsxwriter silently writes numbers as text and when it happens
• The difference between write() and write_number() and when each is appropriate
• How to coerce values to the correct Python type before writing
• How to validate that your output file actually contains numeric cells
• Edge cases involving formatted strings, None values, and mixed-type columns

The Problem: SUM Returns Zero and Green Triangles Appear

Open the file in Excel and look at the cells in question. If you see a small green triangle in the top-left corner of each cell, Excel has flagged them as "Number Stored as Text". Click one and you'll see the warning icon confirm it. Any SUM, AVERAGE, or COUNT formula that references those cells will either return zero or skip them entirely.

The visual alignment is your first clue even before you see the triangle. Excel right-aligns genuine numbers and left-aligns text by default. If a column of "numbers" is left-aligned without you explicitly setting alignment, they're almost certainly stored as text.

Why xlsxwriter Writes Numbers as Text

xlsxwriter's write() method uses Python's runtime type to decide what kind of cell to emit. If the value is a Python int or float, xlsxwriter writes a numeric cell. If it's a str, xlsxwriter writes a text cell — even if that string contains only digits.

The most common way this happens is data coming from a CSV read, a database query result, or a pandas DataFrame where a column that should be numeric has been inferred as object (string). Consider this example:

import xlsxwriter

workbook = xlsxwriter.Workbook("output.xlsx")
worksheet = workbook.add_worksheet()

# Data from a CSV or DB query — values look numeric but are strings
rows = [
    {"product": "Widget A", "revenue": "1200.50"},
    {"product": "Widget B", "revenue": "850.00"},
    {"product": "Widget C", "revenue": "3400.75"},
]

for i, row in enumerate(rows):
    worksheet.write(i, 0, row["product"])
    worksheet.write(i, 1, row["revenue"])  # Writes text, not a number!

worksheet.write_formula(len(rows), 1, f"=SUM(B1:B{len(rows)})")
workbook.close()

The SUM formula on row 4 will return 0. The revenue column contains strings like "1200.50", so xlsxwriter correctly (from its perspective) writes text cells. Excel then ignores them in arithmetic.

How Excel Detects Numbers vs Text

Inside an .xlsx file, each cell has an explicit type attribute in the underlying XML. A numeric cell has no type attribute (or t="n"), and a text cell has t="s" (shared string) or t="inlineStr". Excel reads these attributes and routes the cell value accordingly — there's no auto-conversion at read time.

This means there is no workaround on the Excel side that you can bake into the file itself. The fix must happen in Python, before workbook.close() is called. If you've already shipped files to users and they're complaining, tell them to select the column, use Data > Text to Columns and click Finish — it coerces the column in place. But that's a one-time manual workaround, not a fix.

Using write_number() Instead of write()

The safest fix is to replace write() with write_number() for any cell that must be numeric. This method bypasses the type inference step entirely and always writes a numeric cell, regardless of the Python type you pass.

import xlsxwriter

workbook = xlsxwriter.Workbook("output.xlsx")
worksheet = workbook.add_worksheet()
money_fmt = workbook.add_format({"num_format": "#,##0.00"})

rows = [
    {"product": "Widget A", "revenue": "1200.50"},
    {"product": "Widget B", "revenue": "850.00"},
    {"product": "Widget C", "revenue": "3400.75"},
]

for i, row in enumerate(rows):
    worksheet.write(i, 0, row["product"])
    worksheet.write_number(i, 1, float(row["revenue"]), money_fmt)

worksheet.write_formula(len(rows), 1, f"=SUM(B1:B{len(rows)})", money_fmt)
workbook.close()

Two things changed: write_number() is used for the revenue column, and the string is explicitly cast to float before passing it in. The SUM formula now returns the correct total. The number format is applied via a Format object, which keeps the numeric type intact.

Note that write_number() still requires an actual numeric value — passing a non-numeric string will raise a TypeError. That's actually a good thing: it surfaces the bug during development instead of silently writing broken cells.

Coercing Types Before You Write

If you're writing many columns and don't want to scatter float() calls throughout your loop, coerce the entire dataset first. This is especially important when your data comes from a source you don't fully control, like a REST API or a joined SQL result.

def safe_number(value, default=0.0):
    """Convert a value to float, returning default if conversion fails."""
    try:
        return float(value)
    except (TypeError, ValueError):
        return default

# Columns that must be numeric
NUMERIC_COLS = {"revenue", "units", "margin"}

for i, row in enumerate(rows):
    for col_idx, col_name in enumerate(columns):
        raw = row[col_name]
        if col_name in NUMERIC_COLS:
            worksheet.write_number(i, col_idx, safe_number(raw), fmt_map[col_name])
        else:
            worksheet.write(i, col_idx, raw)

The safe_number helper prevents your export from crashing on None, empty strings, or values like "N/A" that appear in otherwise numeric columns. You decide what the fallback should be — zero is common, but you might prefer writing an empty cell with write_blank() instead.

Checking Your Number Formats

A number format string changes how a number is displayed — it does not change the cell type. This is a point of confusion worth addressing directly. You can apply a format like "0.00" to a text cell and it will display exactly as before, still as text, still ignored by SUM.

The format object only affects display once the underlying value is already numeric. So always fix the type first, then apply the format. Here's a format map pattern that works well for multi-column exports:

currency_fmt = workbook.add_format({"num_format": "$#,##0.00", "align": "right"})
pct_fmt     = workbook.add_format({"num_format": "0.00%",     "align": "right"})
int_fmt     = workbook.add_format({"num_format": "#,##0",     "align": "right"})

fmt_map = {
    "revenue": currency_fmt,
    "margin":  pct_fmt,
    "units":   int_fmt,
}

If you're seeing numbers right-aligned but still getting a zero SUM, double-check that you're using write_number() or that the Python value is already a native int or float. A format alone cannot fix a text cell. This is the same root cause described in the guide on xlsxwriter ignoring cell formats on merged cells — formats and types are separate concerns in xlsxwriter.

Validating the Output File

Don't rely on visual inspection to confirm the fix. Instead, read the file back with openpyxl and check the cell type programmatically before shipping it.

from openpyxl import load_workbook

def assert_column_numeric(filepath, sheet_name, col_letter, start_row, end_row):
    wb = load_workbook(filepath, data_only=True)
    ws = wb[sheet_name]
    for row in range(start_row, end_row + 1):
        cell = ws[f"{col_letter}{row}"]
        assert isinstance(cell.value, (int, float)), (
            f"Row {row} is not numeric: {cell.value!r} (type={type(cell.value).__name__})"
        )
    print(f"All cells in {col_letter}{start_row}:{col_letter}{end_row} are numeric.")

assert_column_numeric("output.xlsx", "Sheet1", "B", 1, 3)

Run this in your test suite or as a post-export sanity check in CI. It catches regressions where a data pipeline change reintroduces strings into a column that was previously clean. If you also work with openpyxl directly, the guide on openpyxl column width autofitting shows a similar read-back pattern for validating other cell properties.

Common Pitfalls and Edge Cases

Pandas DataFrames with object dtype

When you export a pandas DataFrame using to_excel() or iterate its rows manually, a column with mixed types or any None value may be inferred as object dtype. Every cell in that column becomes a Python string or None at iteration time. Cast numeric columns explicitly before you pass them to xlsxwriter:

import pandas as pd

df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")
# NaN cells become float('nan') — handle them before writing
df["revenue"] = df["revenue"].fillna(0.0)

For a deeper look at pandas and Excel export issues, the article on fixing pandas DataFrame wrong column order in Excel covers related DataFrame-to-Excel export patterns worth reading alongside this one.

Numbers with thousand separators or currency symbols

Values like "1,200.50" or "$850" cannot be converted with plain float(). Strip the formatting characters first:

import re

def parse_currency(value):
    cleaned = re.sub(r"[^\d.\-]", "", str(value))
    return float(cleaned) if cleaned else 0.0

write() on integer-looking strings

xlsxwriter's write() tries to be smart: if you pass the Python string "42", it still writes it as text. There is no auto-promotion. If you call write(row, col, "42"), you get a text cell containing 42. Use write_number(row, col, 42) or pass an actual int.

write_string() used defensively in the wrong place

Some developers use write_string() to avoid xlsxwriter's URL-detection behavior or to preserve leading zeros. If you use it on a column that should be numeric, you've permanently set those cells to text. Reserve write_string() for columns that are genuinely textual — product codes, IDs, phone numbers with leading zeros.

Blank rows inside the SUM range

If some rows have no value and you write them as empty strings rather than blanks, SUM still works on the numeric cells — but the blank-string cells can confuse other formulas. Use worksheet.write_blank(row, col, None, fmt) for genuinely empty numeric cells rather than writing an empty string.

This kind of silent data issue is similar to the one described in the guide on xlsxwriter conditional formatting skipping filtered rows — both are cases where the cell content looks fine visually but the underlying cell type causes unexpected formula behavior.

Wrapping Up

The root cause is almost always the same: Python strings going into xlsxwriter cells that need to hold numbers. Here are the concrete steps to eliminate this problem in your codebase:

1. Replace write() with write_number() for every column that participates in Excel formulas. Pass a Python int or float, not a string.
2. Add a safe_number() helper that coerces incoming values and handles None, empty strings, and formatted numbers before you call any write method.
3. Cast pandas columns with pd.to_numeric(..., errors="coerce") before iterating rows, then fill NaN values with a sensible default.
4. Add a post-export assertion using openpyxl to verify that numeric columns contain int or float values, not strings. Run it in CI.
5. Audit write_string() calls in your codebase — make sure they're only used for columns that are semantically text, never as a catch-all safe default for all cell writes.

Once you've made these changes, your SUM formulas will work, the green triangles will disappear, and users won't need to run Text to Columns on every export you send them.