Skip to main content
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.

​
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.
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:
You don’t need all four. How many you supply changes the meaning:
Applies to every number. #,##0.00 displays all values with thousands separators and two decimals.
The first covers positive and zero, the second covers negative. #,##0;[Red]-#,##0 shows negatives in red.
Positive, negative, then zero separately. #,##0;-#,##0;"—" replaces zeros with a dash.
Adds a final section for text values. #,##0;-#,##0;0;[Blue]@ colors any text entry blue.

​
Symbols

​
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.
​
literal
Anything in double quotes prints as-is. #,##0" units" appends a label.
​
modifier
Colors the section. Excel limits this to its built-in set: [Red], [Blue], [Green], [Yellow], [Cyan], [Magenta], [White], [Black], and [Color1] through [Color56].
​
condition
Applies the section only when the value meets the test, so one format can branch on magnitude.

​
Worked examples

​
What Jetxl checks

Checking is partial. Jetxl catches the obvious mistakes and passes everything else through.
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.
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.
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.