Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Write Custom Number Format Codes in Excel with Python

The four-section format language explained: dashes for zero, red parenthesised negatives, thousands scaling with a trailing comma, literal units — all without changing the stored value.

A number format decides what a reader sees without touching what the cell contains, which is what makes it the right tool for currency, percentages, thousands and the small conditional touches that make a report legible. The syntax is a compact language with four sections and a handful of placeholders, and once it is legible the whole surface opens up. This guide is part of Applying Number and Date Formats in Excel.

The four sections of a number format code Semicolons split a format into parts applied to positive values, negative values, zero and text in that order, so a single code can show a red parenthesised negative and a dash for zero. Section Applies to Example part first positive numbers #,##0.00 second negative numbers [Red]-#,##0.00 third zero "–" fourth text @ fewer sections widen the earlier ones; an empty one hides the value

Prerequisites

Bash
pip install openpyxl xlsxwriter pandas

The four sections

A format code is up to four parts separated by semicolons: what to show for positive values, negative values, zero, and text. Supplying fewer is allowed and changes the meaning.

Python
from openpyxl import Workbook

book = Workbook()
sheet = book.active

sheet["A1"] = 1234.5
sheet["A1"].number_format = '#,##0.00;[Red]-#,##0.00;"–";@'

sheet["A2"] = -1234.5
sheet["A2"].number_format = '#,##0.00;[Red]-#,##0.00;"–";@'

sheet["A3"] = 0
sheet["A3"].number_format = '#,##0.00;[Red]-#,##0.00;"–";@'

book.save("formats.xlsx")

That single code shows 1,234.50, a red -1,234.50, and a dash for zero. Rendering zeros as a dash rather than 0.00 is one of the highest-value formatting choices in a report — a table full of zeros reads as noise, and a table of dashes reads as absence.

The @ in the fourth section means "show the text as it is". Leaving that section off means text is displayed normally anyway; leaving it empty — a trailing semicolon with nothing after it — hides text entirely, which is occasionally useful and more often an accident.

The placeholders that matter

What each placeholder does A zero always shows a digit and pads, a hash shows one only when present, a question mark reserves alignment space, and a trailing comma scales the displayed value by a thousand. 0 and # 0 pads with zeros # shows only if present 0000 gives 0042 ? and . ? reserves space aligns decimal points ??0.00 lines up , and "text" trailing , scales by 1000 quoted text is literal value is unchanged the stored number never changes — only what is drawn

Four symbols do most of the work. 0 is a digit that is always shown, padding with zeros. # is a digit shown only if present. ? reserves space without showing a digit, which aligns decimals in a column. And . and , are the decimal and thousands separators, with a comma after the last digit placeholder scaling by a thousand.

Python
examples = {
    "0":            1234.5,     # 1235      — no decimals, rounded
    "0.00":         1234.5,     # 1234.50
    "#,##0":        1234.5,     # 1,235
    "#,##0.00":     1234.5,     # 1,234.50
    "#,##0,":       1234500,    # 1,235     — displayed in thousands
    '#,##0,, "M"':  1234500,    # 1 M       — displayed in millions
    "0000":         42,         # 0042      — zero-padded identifier
    "0.0%":         0.0725,     # 7.3%      — multiplies by 100
    "??0.00":       7.5,        #    7.50   — aligned on the decimal point
}

for index, (code, value) in enumerate(examples.items(), start=1):
    cell = sheet.cell(row=index, column=3, value=value)
    cell.number_format = code

The scaling comma is the one worth remembering: #,##0, shows 1,234,500 as 1,235 while the cell still contains 1,234,500, so totals and charts remain correct. Dividing the value in Python instead would break both.

Currency, and where the symbol goes

Python
codes = {
    "GBP":      '£#,##0.00',
    "EUR":      '€#,##0.00',
    "USD red":  '$#,##0.00;[Red]($#,##0.00)',
    "accounting": '_-£* #,##0.00_-;-£* #,##0.00_-;_-£* "–"_-;_-@_-',
}

The accounting format is the unreadable one and it is worth decoding once: _- inserts a space the width of a minus sign, * repeats the following character to fill the cell, and the combination is what pushes the currency symbol to the left edge while the digits stay right-aligned. Nobody writes that from memory — copy it, and change only the symbol.

The parenthesised negative in the third entry is the convention most finance readers expect, and it is a formatting choice rather than a data one, which is exactly the distinction this whole guide turns on. Format Excel Cells as Currency with Python covers the currency case in more depth.

Text, units and conditions inside a format

Anything in double quotes is shown literally, which is how units get attached without turning the number into a string.

Python
sheet["E1"] = 12.5
sheet["E1"].number_format = '0.0" kg"'            # 12.5 kg, still a number

sheet["E2"] = 0.0725
sheet["E2"].number_format = '0.0%" YoY"'          # 7.3% YoY

sheet["E3"] = 4200
sheet["E3"].number_format = '[>=1000]#,##0,"k";[<1000]#,##0'   # 4k

Square-bracket conditions let a single format branch on the value, which covers the common case of showing large numbers in thousands and small ones in full. Excel allows two conditions plus a fallback — beyond that, conditional formatting is the right tool, as in Highlight Cells Above a Threshold with openpyxl.

Applying formats efficiently

Setting number_format per cell is fine for a few hundred rows and wasteful for fifty thousand. xlsxwriter attaches a format to a whole column in one call, which is both faster to write and smaller in the finished file.

Python
import pandas as pd

frame = pd.DataFrame({"Region": ["North", "South"], "Revenue": [1234500.0, 987650.0],
                      "Share": [0.5556, 0.4444]})

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    frame.to_excel(writer, sheet_name="Summary", index=False)
    book, sheet = writer.book, writer.sheets["Summary"]
    thousands = book.add_format({"num_format": '#,##0,"k"'})
    percent = book.add_format({"num_format": "0.0%"})
    sheet.set_column("B:B", 14, thousands)
    sheet.set_column("C:C", 10, percent)

set_column applies to every cell in the column that is not individually formatted, which means one call covers however many rows arrive next month. In openpyxl the equivalent is a loop, and the loop is what makes formatting a large sheet slow.

Building a small format library

Once a report uses more than three or four codes, keeping them as literals scattered through the script guarantees they drift apart. A dictionary of named formats, created once per workbook, keeps the appearance consistent and makes a change to the house style a single edit.

Python
def build_formats(book) -> dict:
    return {
        "money":     book.add_format({"num_format": '#,##0.00'}),
        "thousands": book.add_format({"num_format": '#,##0,"k"'}),
        "percent":   book.add_format({"num_format": '0.0%'}),
        "signed":    book.add_format({"num_format": '▲ 0.0%;▼ 0.0%;–'}),
        "date":      book.add_format({"num_format": 'yyyy-mm-dd'}),
        "header":    book.add_format({"bold": True, "bg_color": "#F0F4FF", "border": 1}),
    }

formats = build_formats(book)
sheet.set_column("B:B", 14, formats["money"])
sheet.set_column("D:D", 10, formats["percent"])

Naming them by meaning rather than by code — money, not hash_comma_hash_hash_zero — is what makes the calling code readable a year later. It also makes the set auditable: a quick look at the dictionary answers "how many ways does this report show a number", which is a question worth asking of any workbook that has grown organically.

The openpyxl equivalent uses NamedStyle, which is registered on the workbook once and then assigned by name — the approach described in Apply a Reusable Style Theme Across an Excel Report.

Locale, and the workbook that looks wrong elsewhere

Format codes are stored in a locale-neutral form and rendered according to the reader's settings, which mostly works and occasionally does not. The separators are the safe part: a comma in the code means "thousands separator" and renders as a full stop in locales that use one. The currency symbol is the unsafe part, because a literal £ is a literal £ wherever the file is opened.

Where a report crosses locales, the honest options are to state the currency in the column header and use a plain numeric format, or to embed the locale identifier in the code:

Python
uk_pounds = '[$£-en-GB]#,##0.00'
euros_de  = '[$€-de-DE]#,##0.00'

The bracketed form tells Excel both the symbol and the locale it belongs to, which keeps the number grouping consistent with the currency rather than with the reader's machine. It is rarely needed for an internal report and worth knowing about the first time one is sent abroad, alongside the date question covered in Format Dates in Excel Cells with Python.

Common pitfalls

SymptomCauseFix
The percentage shows as 725%The value was already multiplied by 100Store the proportion; the format multiplies
The format is ignoredThe cell contains text, not a numberConvert with pd.to_numeric before writing
Column formatting does not applyCells written individually after set_columnPass the format to the write call as well
Text disappears from a cellAn empty fourth section in the codeUse @ or omit the section entirely
The number changes when reopenedThe value was divided in Python, not scaled by formatUse a trailing comma in the code instead
Dates show as five-digit numbersNo date format applied to a serialSee fixing Excel serial numbers

Reading the formats already in a workbook

When matching an existing report's appearance, the fastest route is to read the codes it already uses rather than guessing at them.

Python
from collections import Counter
from openpyxl import load_workbook

book = load_workbook("existing-report.xlsx")
codes = Counter()
for sheet in book.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            if cell.value is not None and cell.number_format != "General":
                codes[cell.number_format] += 1

for code, count in codes.most_common(10):
    print(f"{count:6d}  {code}")

That inventory is usually shorter than expected — a real report tends to use three or four codes — and copying them exactly is what makes a generated workbook indistinguishable from the hand-built one it replaces. It is the same technique as the formula inventory in Excel Formula Equivalents in pandas, applied to appearance instead of logic.

Performance and scale

Where formatting cost accumulates on a 50,000-row sheet Creating a format object per row fills the workbook's style table, formatting cells individually is better, and applying one format to the whole column is cheapest by a wide margin. a format per row 50,000 style entries one format, per cell one entry, many refs one format, per column one call relative cost reuse the format object; format the column, not the cells

Each distinct format in a workbook is an entry in its style table, and each formatted cell references one. That means the cost is in the number of distinct formats and the number of individually formatted cells, not in the complexity of the codes themselves.

Two habits keep a large workbook fast. Create each format object once and reuse it, rather than constructing one per row — in xlsxwriter a fresh add_format per row produces thousands of style entries. And prefer column-level formatting to per-cell formatting wherever the whole column shares a format, which is almost always.

Python
money = book.add_format({"num_format": "#,##0.00"})     # once
for index in range(1, 50001):
    sheet.write_number(index, 1, values[index - 1], money)   # reused

Conclusion

A format code has four sections — positive, negative, zero, text — and a handful of placeholders: 0 always shows a digit, # shows one only if present, a trailing comma scales by a thousand, and quoted text appears literally. Use them to show zeros as dashes, negatives in red parentheses and large numbers in thousands, all without changing the stored value. Apply them to columns rather than cells, and reuse format objects rather than creating one per row.

Frequently asked questions

How many sections can a format code have? Up to four, separated by semicolons: positive, negative, zero and text, in that order. With two sections the second covers negatives and zeros; with one, it covers everything. A missing section is not the same as an empty one — an empty section hides the value.

How do I show a number in thousands? Put a comma after the last digit placeholder: '#,##0,' divides the displayed value by a thousand, and two commas by a million. The stored value is unchanged, which is what makes it safe — the cell still sums correctly.

Why does my format work in openpyxl but not xlsxwriter? The code is the same in both; the difference is where you set it. openpyxl assigns to cell.number_format per cell, xlsxwriter attaches a Format object to a column or a write call. A column format in xlsxwriter does not apply to cells written individually afterwards.

Can a format change the cell colour? Yes, with a colour name in square brackets at the start of a section — Red-#,##0. It is limited to eight named colours and applies to the whole section, which is why conditional formatting is the better tool for anything more nuanced.