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

Format codes worth memorising

A number format is a small language, and eight codes cover almost every reporting need:

The percentage row is the one that catches people: the format multiplies by a hundred, so a value already scaled to 48.35 displays as 4835.0%. Store the fraction and let the format do the presentation — the same division of labour that keeps dates and currency correct.

Applying formats per column, not per cell

Number formats belong to cells, so a generated report has to set them across the range it wrote. A column-oriented loop keeps that readable and cheap:

from openpyxl import load_workbook

FORMATS = {"D": "#,##0.00", "E": "0.0%", "B": "yyyy-mm-dd"}

wb = load_workbook("report.xlsx")
ws = wb["Orders"]

for letter, code in FORMATS.items():
    for row in range(2, ws.max_row + 1):
        ws[f"{letter}{row}"].number_format = code

wb.save("report_formatted.xlsx")

Mapping letters to codes in one dictionary makes the report's conventions visible in a single place, which matters when a workbook has six sheets that should all agree. Where the column positions vary, build the mapping from the header row instead — the same lookup-by-name habit that keeps every other part of a generated report robust.

Dates: value, format and the 1904 trap

Excel stores a date as a number of days since 1899-12-30, and the format decides how that number appears. Writing a real datetime and applying a date format is the only combination that behaves:

from datetime import date
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Orders"]

ws["B2"] = date(2026, 3, 1)          # a real date, not "2026-03-01"
ws["B2"].number_format = "yyyy-mm-dd"

ws["B3"] = "2026-03-01"              # text: no format will make this sort as a date
wb.save("dates.xlsx")

A date written as text sorts alphabetically, filters as a string, and cannot be used in arithmetic — and no number format repairs it, because formats apply to numbers. The other trap is the 1904 date system, used by some older Mac workbooks: dates read from such a file are four years and a day out unless the epoch is adjusted, and the symptom is a report where every date is consistently wrong by the same offset.

Custom sections and conditional colour

A format string can carry up to four semicolon-separated sections — positive, negative, zero and text — which is how a single code can show negatives in red, hide zeros, or append a unit:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Orders"]

ws["D2"].number_format = '#,##0.00;[Red]-#,##0.00;"–";@'      # zero shows as a dash
ws["E2"].number_format = '#,##0" units"'                        # a unit suffix
ws["F2"].number_format = '[>=1000000]#,##0,,"M";[>=1000]#,##0,"k";#,##0'
ws["G2"].number_format = '[Blue]▲ 0.0%;[Red]▼ 0.0%;–'

wb.save("report_custom_formats.xlsx")

The third example uses conditions in square brackets and trailing commas that divide by a thousand each — the standard trick for a summary column that must show 1.2M, 340k and 87 in the same space. The fourth pairs a colour with a direction arrow, which reads instantly on a variance column and still prints legibly in greyscale.

Two limits are worth knowing. Colour names inside brackets are restricted to a small set — black, blue, cyan, green, magenta, red, white, yellow — and anything else has to come from conditional formatting. And a format hides a value rather than changing it: a zero shown as a dash is still zero, so a total that includes it is unaffected, which is usually what you want and occasionally surprising.

Formats travel with the cell, not the column

Setting a format on a column dimension affects only cells Excel has not yet written, so a generated report has to apply formats to the cells it wrote. That is why every example here loops over rows. The one exception is a column that must format values a reader will type later — a data-entry template — where setting ws.column_dimensions["D"].number_format does apply to new entries.

The practical consequence for a monthly job is that formats must be re-applied on every run, because a fresh to_excel write replaces the sheet along with its formatting. Keeping the format map in one dictionary and applying it in the finishing step is what makes that reliable rather than something someone remembers.

Formats a reader will thank you for

Beyond correctness, a few conventions make a report noticeably easier to read. Thousands separators on anything above a thousand; two decimal places on money and none on counts; a consistent date format across every sheet; and alignment left to Excel, which right-aligns real numbers and dates automatically. A column that is left-aligned is a column of text, and that is a useful signal — if a figure column looks left-aligned, its values are strings and every sum over it is wrong.

Consistency matters more than any individual choice. A workbook where one sheet shows 1,234.50, another 1234.5 and a third £1,234.50 reads as three reports stapled together, and the fix is to keep the format map in one dictionary applied by one finishing function.

Formats and pandas do not talk to each other

Number formats live in the workbook, not in the data, so they disappear on a round trip through pandas. Reading a formatted workbook gives you the underlying values — 0.4835, not 48.4% — and writing a frame back applies no formats at all:

import pandas as pd
from openpyxl import load_workbook

frame = pd.read_excel("report.xlsx", sheet_name="Summary")
print(frame["Share"].head())         # 0.4835 — the value, not the display

with pd.ExcelWriter("refreshed.xlsx", engine="openpyxl") as writer:
    frame.to_excel(writer, sheet_name="Summary", index=False)
    sheet = writer.sheets["Summary"]
    for row in range(2, sheet.max_row + 1):
        sheet.cell(row=row, column=3).number_format = "0.0%"     # re-applied every run

That asymmetry is the reason formatting belongs in a finishing step rather than sprinkled through a pipeline: whatever pandas writes, the formats have to be put back afterwards, every time.

One format map per workbook

Keeping every number format in a single dictionary, applied by one finishing function, is what stops a six-sheet report showing money three different ways. It also makes a house-style change a one-line edit rather than an archaeology exercise across a script — and because formats have to be re-applied after every pandas write anyway, having them in one place is the difference between a reliable habit and something someone remembers on a good day.

Test the formats, not just the values

A format is easy to check and rarely checked. Reading the finished workbook back and asserting that the money column carries #,##0.00, the share column 0.0% and the date column an unambiguous date code takes four lines and catches the most common regression in a formatted report: a pandas rewrite that replaced the sheet and quietly removed every format applied last month. Because formats are invisible to a data check, they are exactly the kind of thing that stops being applied without anyone noticing until a reader mentions that the report "looks different".

Log what the run actually did

Row counts at each boundary, what was filled, what was quarantined, how long it took: five or six lines per run turn a question about a number into a lookup. The value is not in reading them on a good day but in having them on a bad one, when a total has moved and nobody can say whether the source changed, the cleaning changed, or a filter was added. A job that records its own behaviour is one that can be debugged after the fact rather than re-run and watched.

Formats are the last thing applied and the first thing noticed

A report's numbers can be entirely correct and still read as unfinished if the money column shows six decimal places or the dates arrive as five-digit serials. Because formats sit on cells rather than on data, they have to be re-applied after every write — which makes them the natural companion of column widths and frozen panes in a single finishing pass over the workbook.

Keeping the format map beside the width and freeze settings, in one function called by every report, is what turns presentation from something remembered into something guaranteed.

Key takeaways

• number_format is a display layer over an unchanged value — cell.value and every formula still see the raw number.
• Percentages need the ratio: store 0.25, not 25, and let the % code multiply by 100 for the display.
• Currency, separators, and red negatives all come from format codes; supply a negative section (positive;negative) to style losses.
• Dates need a real datetime.date or datetime.datetime, never a pre-formatted string, so Excel stores a true serial you can reformat.
• Loop ws["B"][1:] to format a whole column in one pass — the same code you would type under Excel's Format Cells → Custom.

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.

Related

• Formatting and Charting Excel Reports with Python — the parent guide 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.