Number formats

Control how numbers, dates and text appear in your spreadsheet

A number format changes how a value is displayed, never the value itself. A cell holding 1500 can read as 1,500, $1,500.00 or 150000% depending on its format. Excel still knows it’s 1500 and calculates with it accordingly.

Set formats per column through column_formats.

jet.write_sheet_arrow(
    df.to_arrow(),
    "report.xlsx",
    column_formats={
        "Price": "currency",
        "Growth": "percentage",
    },
)

Built-in formats

Start here. These cover most reports and need no knowledge of Excel format codes. Pass the name and Jetxl fills in the rest.

Name	Displays	1500 becomes
general	Default, no formatting	1500
integer	Whole numbers	1500
decimal2	Two decimal places	1500.00
decimal4	Four decimal places	1500.0000
thousands	Thousands separator	1,500
currency	Currency with cents	$1,500.00
currency_rounded	Currency, no cents	$1,500
percentage	Whole percent	150000%
percentage_decimal	Percent with decimals	150000.00%
percentage_integer	Percent as integer	150000%
scientific	Scientific notation	1.50E+03
fraction	Simplest fraction	1500
fraction_two_digits	Two-digit fraction	1500
date	Short date, locale-dependent	—
datetime	yyyy-mm-dd hh:mm:ss	—
time	hh:mm:ss	—

Percentage formats multiply by 100 for display, because they map to Excel’s built-in percent formats. A column already holding 15 for “15 percent” reads as 1500%. Store percentages as decimals, so 0.15, and let the format do the conversion.

date maps to Excel’s built-in short-date format, which renders according to the reader’s locale — mm-dd-yy in a US locale, not yyyy-mm-dd. Only datetime is fixed at yyyy-mm-dd hh:mm:ss. For a date that looks the same everywhere, pass the custom code "yyyy-mm-dd" instead of the built-in name.

Custom formats

Any string that isn’t one of the names above goes to Excel as a format code. This is the escape hatch: anything Excel can display, you can specify.

You don’t have to learn the syntax. Format a cell the way you want in Excel, right-click it, choose Format Cells > Custom, and copy the string from the Type field. Paste that string into column_formats.

The four sections

A format code has up to four sections separated by semicolons, applied in this order:

[Positive];[Negative];[Zero];[Text]

You don’t need all four. How many you supply changes the meaning:

One section

Applies to every number. #,##0.00 displays all values with thousands separators and two decimals.

Two sections

The first covers positive and zero, the second covers negative. #,##0;[Red]-#,##0 shows negatives in red.

Three sections

Positive, negative, then zero separately. #,##0;-#,##0;"—" replaces zeros with a dash.

Four sections

Adds a final section for text values. #,##0;-#,##0;0;[Blue]@ colors any text entry blue.

Symbols

0 · digit placeholder

Shows a digit, or 0 if there is none. 00000 turns 42 into 00042.

# · digit placeholder

Shows a digit, or nothing if there is none. #,##0 turns 42 into 42, not 00042.

? · digit placeholder

Shows a digit, or a space. Use it to align decimal points down a column.

, · separator or scale

Between digits it’s a thousands separator. After the last digit it divides by 1,000, so one comma gives thousands and two gives millions.

"text" · literal

Anything in double quotes prints as-is. #,##0" units" appends a label.

[Color] · modifier

Colors the section. Excel limits this to its built-in set: [Red], [Blue], [Green], [Yellow], [Cyan], [Magenta], [White], [Black], and [Color1] through [Color56].

[>=100] · condition

Applies the section only when the value meets the test, so one format can branch on magnitude.

Worked examples

Accounting

column_formats = {
    # Negatives in parentheses and red, decimals aligned
    "Balance": "$#,##0.00_);[Red]($#,##0.00)",
}

What Jetxl checks

Checking is partial. Jetxl catches the obvious mistakes and passes everything else through.

Raises an error

An empty code, or one made entirely of letters such as "accounting", raises an OSError naming the bad code. This catches the common mistake of inventing a format name that doesn’t exist.

Prints a warning

Passing a raw code that matches a built-in, such as "$#,##0.00" instead of "currency", writes the file and suggests the built-in name.

Passes through unchecked

Anything else. A malformed code containing digits still reaches Excel, which complains when you open the file rather than when you write it.

Limits

255 characters

Excel’s own ceiling on the length of a format code.

Escaping is handled

Jetxl escapes the XML-significant characters for you.

Built-in colors only

Color names inside a format code come from Excel’s fixed set, not arbitrary hex.

Version differences

Locale codes and DBNum variants may not render in every Excel version.

For the full syntax, see Excel number format codes from Microsoft.