Fixing Python openpyxl That Fails to Apply Number Formatting to Cells

You call cell.number_format = '"$"#,##0.00', save the workbook, and open the file in Excel — but every number still looks like a plain integer. No dollar signs, no decimal places, nothing. The code ran without errors, which makes it even more confusing.

This problem comes down to a handful of specific mistakes that are easy to make because openpyxl's API doesn't complain when you get them wrong. This guide walks through each root cause and shows you the exact fix for currencies, dates, percentages, and custom formats.

What You'll Learn

• Why openpyxl silently ignores number_format in certain situations
• How to apply currency, date, percentage, and custom number formats correctly
• Why storing numbers as strings kills your formatting before it starts
• How Excel interprets format codes and what makes one valid
• How to verify your formatting worked before sending a file downstream

Why Number Formatting in openpyxl Is Trickier Than It Looks

In Excel, number formatting is purely cosmetic — it changes how a value is displayed without changing the underlying value. A cell holding 0.25 formatted as 0% shows 25%. The number stays 0.25.

openpyxl mirrors this: you set cell.number_format to an Excel format code string, and Excel renders it when the file opens. The problem is that openpyxl writes whatever you give it into the .xlsx XML without validating it. If the value is wrong type, the format code is malformed, or the assignment order is off, Excel quietly ignores the formatting or falls back to General.

Understanding these failure modes is faster than trial-and-error with format strings.

Root Cause 1: Assigning number_format After Writing the Value

This one surprises people. In openpyxl, the order of assignment matters in practice because each assignment to cell.number_format replaces the previous one — but more importantly, if you're working with a loop and accidentally re-reference the cell, you can overwrite the format with the default 'General'.

More common: developers set the format on a cell object, then write the value through a different reference, ending up with two different cell proxies in memory.

# BAD: formatting the wrong reference
ws['A1'].number_format = '"$"#,##0.00'
ws.cell(row=1, column=1).value = 1234.56  # same cell, but separate proxy write

# GOOD: set value and format on the same reference
cell = ws.cell(row=1, column=1)
cell.value = 1234.56
cell.number_format = '"$"#,##0.00'

The safest habit is to get one reference to the cell, set the value, then set the format on that same object. Mixing ws['A1'] and ws.cell(row=1, column=1) in the same write operation is a common source of hard-to-spot bugs.

Root Cause 2: Using a Format String That Excel Doesn't Recognize

openpyxl accepts any string for number_format and writes it into the file. Excel will silently fall back to General if the format code is not valid. You won't see an error in Python or in Excel — the cell just looks unformatted.

The most frequent mistakes are using Python-style format strings instead of Excel format codes, or wrapping the entire format in quotes incorrectly.

# BAD: Python-style format string — Excel won't understand this
cell.number_format = '{:.2f}'

# BAD: Literal dollar sign without quoting it in the Excel format code
cell.number_format = '$#,##0.00'  # works in some locales but not reliably

# GOOD: Dollar sign quoted inside the Excel format code
cell.number_format = '"$"#,##0.00'

# GOOD: Using openpyxl's built-in format constants
from openpyxl.styles import numbers
cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE  # '"$"#,##0.00'

Excel format codes follow a specific syntax. Special characters like $, +, -, and / must be enclosed in double quotes within the format string when you want them treated as literals. The openpyxl.styles.numbers module exposes named constants for common formats — use them when you can to avoid typos.

Here are the reliable built-in constants worth knowing:

Root Cause 3: Formatting a Cell That Holds a String, Not a Number

This is the single most common reason number formatting appears to do nothing. If the cell value is the string '1234.56' instead of the float 1234.56, Excel will not apply numeric formatting to it. The format code is stored in the file, but Excel treats the cell as text and ignores the format.

# BAD: value is a string — number_format will have no visible effect
cell.value = '1234.56'
cell.number_format = '"$"#,##0.00'

# GOOD: value is a float
cell.value = 1234.56
cell.number_format = '"$"#,##0.00'

# If you're reading data from a CSV or database and values might be strings:
cell.value = float(row['amount'])  # explicit cast
cell.number_format = '"$"#,##0.00'

When you're pulling data from a source like a CSV, a database query, or a JSON API, values often come through as strings even when they look numeric. Always cast to int or float before writing to a cell you intend to format. This is similar to the kind of silent type confusion covered in fixing Pandas read_csv that silently misreads date columns as strings — the underlying issue is always an unexpected type, not a formatting bug.

You can verify cell data types quickly before writing:

data = ['1234.56', 99, '0.75', 200.5]

for i, val in enumerate(data, start=1):
    cell = ws.cell(row=i, column=1)
    cell.value = float(val) if isinstance(val, str) else val
    cell.number_format = '"$"#,##0.00'

Root Cause 4: Forgetting That Dates Need a Numeric Value Too

Excel stores dates as floating-point serial numbers internally (days since 1900-01-01 by default). openpyxl handles this automatically when you assign a Python datetime object — it converts the date to the correct serial number and sets a default date format. But if you write a date as a string, the conversion never happens.

from datetime import datetime

# BAD: string date — format code will be ignored
cell.value = '2024-03-15'
cell.number_format = 'YYYY-MM-DD'

# GOOD: Python datetime object — openpyxl handles the conversion
cell.value = datetime(2024, 3, 15)
cell.number_format = 'YYYY-MM-DD'

# GOOD: Date with time
cell.value = datetime(2024, 3, 15, 9, 30, 0)
cell.number_format = 'YYYY-MM-DD HH:MM:SS'

One extra gotcha: Excel date format codes use M for months and D for days, with H and S for time. They are case-insensitive in Excel but the openpyxl docs use uppercase. Use YYYY-MM-DD, not Python's %Y-%m-%d strftime syntax. Mixing them up is a quiet failure — no error, just wrong or no formatting.

Applying Common Format Types Correctly

Here's a single working example covering the format types you'll use most often. Copy this as a starting template and adjust the column assignments to your data.

from openpyxl import Workbook
from openpyxl.styles import numbers
from datetime import datetime

wb = Workbook()
ws = wb.active
ws.title = 'Formatted Report'

# Headers
ws.append(['Date', 'Revenue', 'Units', 'Margin', 'Growth'])

# Data row
row_data = [
    datetime(2024, 3, 15),  # Date
    98432.75,                # Revenue
    412,                     # Units (integer)
    0.3215,                  # Margin as decimal (will display as 32.15%)
    0.0872,                  # Growth as decimal (will display as 8.72%)
]

ws.append(row_data)

# Apply formats to row 2
formats = [
    'YYYY-MM-DD',
    '"$"#,##0.00',
    '#,##0',
    '0.00%',
    '0.00%',
]

for col_idx, fmt in enumerate(formats, start=1):
    cell = ws.cell(row=2, column=col_idx)
    cell.number_format = fmt

wb.save('formatted_report.xlsx')

Notice that the margin value 0.3215 gets stored as-is — Excel's 0.00% format multiplies it by 100 for display. Do not pre-multiply to 32.15 before storing; that would display as 3215.00%.

Applying Formats Across an Entire Column

If you're writing a full dataset and want every cell in a column formatted consistently, iterate with ws.iter_rows() and set the format per cell. Column-level number_format is not supported in the way you might expect — you must set it cell by cell.

revenues = [12340.5, 9870.0, 45600.75, 8900.25]

for row_idx, amount in enumerate(revenues, start=2):  # start=2 to skip header
    cell = ws.cell(row=row_idx, column=2)
    cell.value = amount
    cell.number_format = '"$"#,##0.00'

Using a Helper Function to Keep Things Clean

If you format many different cell types across a large report, a small helper avoids repeating the same assignment pattern everywhere.

def write_formatted(ws, row, col, value, fmt):
    """Write a value to a cell and apply an Excel number format."""
    cell = ws.cell(row=row, column=col)
    cell.value = value
    cell.number_format = fmt

# Usage
write_formatted(ws, 2, 1, datetime(2024, 3, 15), 'YYYY-MM-DD')
write_formatted(ws, 2, 2, 98432.75, '"$"#,##0.00')
write_formatted(ws, 2, 3, 0.3215, '0.00%')

Common Pitfalls and Edge Cases

Even once you have the basics right, a few edge cases trip people up repeatedly.

Accounting Format vs. Currency Format

Excel's built-in Accounting format aligns currency symbols to the left of the cell and shows negatives in parentheses. If you want that style, use the accounting format code rather than the currency one:

# Accounting format: symbol aligned left, negatives in parentheses
cell.number_format = '_-"$"* #,##0.00_-;-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'

# Or the simpler variant that most people actually want
cell.number_format = '"$"#,##0.00;[Red]-"$"#,##0.00'  # negatives in red

Loading an Existing Workbook and Reformatting Cells

When you open an existing .xlsx file with openpyxl.load_workbook(), the same rules apply. Just be aware that if the existing cell holds a string (e.g., it was typed into Excel with a leading apostrophe or imported from CSV), reformatting it won't turn it into a number. You need to reassign cell.value = float(cell.value) first.

This pattern of read-modify-write is also relevant when you're dealing with formula cells — if you're working with cells that return formulas instead of computed values, you'll need to handle that separately before any reformatting makes sense.

Locale and Currency Symbols

The "$" in a format code is a literal character Excel displays, not a locale-aware currency symbol. For Euro, Pound, or other symbols, use the Unicode character directly in the format string:

cell.number_format = '"€"#,##0.00'   # Euro
cell.number_format = '"£"#,##0.00'   # British Pound
cell.number_format = '"¥"#,##0'      # Japanese Yen (no decimals)

Verifying Your Format Was Applied

If you're debugging and want to check what format a cell actually has before opening Excel, read it back:

import openpyxl

wb = openpyxl.load_workbook('formatted_report.xlsx')
ws = wb.active

cell = ws['B2']
print(f'Value: {cell.value}, Type: {type(cell.value)}, Format: {cell.number_format}')
# Expected: Value: 98432.75, Type: <class 'float'>, Format: "$"#,##0.00

If number_format prints as General, the format was never applied or was overwritten. If the type is str, that's your root cause.

Writing to the Correct Sheet

Number formatting bugs sometimes mask a separate issue: you're writing to the wrong worksheet entirely. If you're working with a multi-sheet workbook, double-check you're targeting the right sheet object. A full discussion of that class of bug is covered in fixing openpyxl that writes data to the wrong sheet tab.

Wrapping Up

The vast majority of openpyxl number formatting failures trace back to one of four causes: a string value where a number should be, an invalid format code, a wrong cell reference, or a datetime written as a string. Fix the data type first, then check the format code syntax.

Here are the concrete steps to take right now:

• Audit your cell values: Print type(cell.value) for any cell that isn't formatting correctly. If it's str, cast it to int or float before writing.
• Use openpyxl's named constants from openpyxl.styles.numbers for common formats to avoid format-code typos.
• Write datetime objects, not strings, for date cells, and use Excel date format syntax (YYYY-MM-DD), not Python strftime syntax.
• Consolidate value and format assignment onto a single cell reference to avoid proxy-object confusion in loops.
• Read the cell back with load_workbook() and inspect .number_format and type(.value) when a format still looks wrong after saving.