Applying Number and Date Formats in Excel

When a report looks wrong, the value is usually fine and the display is off. In Excel, every cell holds a raw value and a separate display format. cell.number_format controls only the display — it never changes the number underneath. This guide, part of Formatting and Charting Excel Reports with Python, shows how to apply currency, separators, percentages, red negatives, and dates with openpyxl. Every snippet builds its own sample workbook so you can run them in order.

Install openpyxl

pip install openpyxl

openpyxl writes .xlsx/.xlsm without Excel installed and runs on Windows, macOS, and Linux.

Number format is display-only

Setting number_format changes how a value looks, not what it is. A cell formatted as currency still holds the plain float, so formulas, sorts, and exports see the original number. Confirm it yourself: write a value, format it, then read .value back — it is unchanged.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = 1234.5
ws["A1"].number_format = "$#,##0.00"   # displays as $1,234.50

print("Stored value:", ws["A1"].value)        # 1234.5
print("Display code: ", ws["A1"].number_format)
wb.save("number_format_basics.xlsx")

The stored value prints 1234.5 even though Excel will render $1,234.50. Because the format is cosmetic, the cell must already contain a real number for the format to mean anything — a text string like "1234.5" will not pick up a currency format.

Currency and thousands separators

The placeholder # shows a digit only when present; 0 forces a digit. Group thousands with a comma and fix two decimals with 0.00. Drop the currency symbol for a plain grouped integer.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Item", "Amount"])
ws.append(["Revenue", 1234567.5])
ws.append(["Units", 42000])

ws["B2"].number_format = "$#,##0.00"   # $1,234,567.50
ws["B3"].number_format = "#,##0"       # 42,000

wb.save("currency_and_separators.xlsx")
print("Formatted currency and a grouped integer")

For a deep dive on symbols, accounting parentheses, and whole-column currency, see Format Excel Cells as Currency with Python.

Percentages: store the ratio, not the integer

The % format code multiplies the stored value by 100 for display and appends a percent sign. So a cell showing 25.0% must store 0.25, not 25. Store the raw ratio and let the format do the conversion — storing 25 would render as 2500.0%.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Metric", "Rate"])
ws.append(["Conversion", 0.25])
ws.append(["Churn", 0.073])

ws["B2"].number_format = "0.0%"    # 25.0%
ws["B3"].number_format = "0.00%"   # 7.30%

print("B2 stored value:", ws["B2"].value)   # 0.25, not 25
wb.save("percentages.xlsx")

The stored value stays 0.25, so a =SUM() or a downstream pandas read gets the true ratio.

Show negatives in red

A format string can carry up to four sections separated by semicolons: positive; negative; zero; text. Supply a negative section to style losses. Wrap a section in [Red] to color it, and use parentheses instead of a minus sign for an accounting look.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Account", "Balance"])
ws.append(["Operating", 8200.40])
ws.append(["Overdraft", -1530.75])

red_neg = "$#,##0.00;[Red]($#,##0.00)"
ws["B2"].number_format = red_neg     # $8,200.40
ws["B3"].number_format = red_neg     # ($1,530.75) in red

wb.save("red_negatives.xlsx")
print("Applied positive/negative two-section format")

The positive 8200.40 shows normally; the negative -1530.75 displays as a red ($1,530.75). The underlying value remains -1530.75.

Apply a format down a whole column

Reports format an entire column, not single cells. Iterate the column and set number_format on each data cell, skipping the header. ws["B"] yields every cell in column B top to bottom; slice off the header with [1:].

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Region", "Revenue"])
for region, value in [("North", 23990.5), ("South", 12475.0), ("West", 15992.2)]:
    ws.append([region, value])

for cell in ws["B"][1:]:           # column B, skip the header
    cell.number_format = "$#,##0.00"

wb.save("format_column.xlsx")
print("Formatted", ws.max_row - 1, "revenue cells")

Formatting an empty cell is harmless — the format simply applies once a value lands there — so you can format a few rows past the current data if more is coming.

Date and time formats

Dates need a real Python datetime.date or datetime.datetime so Excel stores a true date serial number. Then number_format controls the rendered layout. Write the object directly; do not pre-format it into a string, or Excel treats it as text and cannot reformat it.

from datetime import date, datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Event", "When"])
ws.append(["Invoice", date(2024, 3, 9)])
ws.append(["Logged", datetime(2024, 3, 9, 14, 30)])

ws["B2"].number_format = "yyyy-mm-dd"          # 2024-03-09
ws["B3"].number_format = "mmm d, yyyy h:mm"    # Mar 9, 2024 14:30

wb.save("date_formats.xlsx")
print("Wrote real date serials with display formats")

For string-to-date pitfalls, locale layouts like dd/mm/yyyy, and pandas datetime columns, read Format Dates in Excel Cells with Python.

Common number-format codes

Frequently asked questions

Does number_format change the cell's value?

No. It only changes the display. cell.value returns the same number you stored, and formulas operate on that raw value. This is the single most important rule when debugging a "wrong" report.

Why does my percentage show as 2500%?

You stored 25 instead of 0.25. The % code multiplies by 100 for display, so store the ratio. Divide integer percentages by 100 before writing.

Why is my currency format being ignored?

The cell almost certainly holds text, not a number. A value like "1234.5" (a string) will not format as currency. Write a real int or float, or convert with float(value) before assigning.

Can I reuse one format across many cells?

Yes — assigning the same string to many cells' number_format is cheap. For richer reuse, wrap a format in a NamedStyle and apply the style by name; see Styling Excel Cells with openpyxl.

Do these codes match Excel's Format Cells dialog?

Yes. The strings are the same custom format codes Excel shows under Format Cells → Custom. Anything you can type there, you can assign to number_format.

Conclusion

number_format is a display layer over an unchanged value. Store real numbers and real date/datetime objects, then choose a format code for presentation. Keep ratios as ratios for percentages, supply a negative section for red losses, and loop a column to format a report in one pass. Because formatting never mutates the value, your formulas and exports stay correct no matter how the cells look.

Where to go next

• Formatting and Charting Excel Reports with Python — the parent pillar covering visual report polish end to end.
• Format Excel Cells as Currency with Python — symbols, accounting parentheses, and whole-column currency.
• Format Dates in Excel Cells with Python — real date serials and pandas datetime export.
• Styling Excel Cells with openpyxl — fonts, fills, borders, and reusable named styles.
• Creating Charts in Excel with openpyxl — turn formatted tables into native Excel charts.