> ## Documentation Index
> Fetch the complete documentation index at: https://jetxl.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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`.

```python theme={null}
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`                   | —              |

<Warning>
  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.
</Warning>

<Note>
  `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.
</Note>

## 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.

<Tip>
  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`.
</Tip>

### The four sections

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

```text theme={null}
[Positive];[Negative];[Zero];[Text]
```

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

<AccordionGroup>
  <Accordion title="One section" icon="1">
    Applies to every number. `#,##0.00` displays all values with thousands separators and two decimals.
  </Accordion>

  <Accordion title="Two sections" icon="2">
    The first covers positive and zero, the second covers negative. `#,##0;[Red]-#,##0` shows negatives in red.
  </Accordion>

  <Accordion title="Three sections" icon="3">
    Positive, negative, then zero separately. `#,##0;-#,##0;"—"` replaces zeros with a dash.
  </Accordion>

  <Accordion title="Four sections" icon="4">
    Adds a final section for text values. `#,##0;-#,##0;0;[Blue]@` colors any text entry blue.
  </Accordion>
</AccordionGroup>

### Symbols

<ParamField path="0" type="digit placeholder">
  Shows a digit, or `0` if there is none. `00000` turns `42` into `00042`.
</ParamField>

<ParamField path="#" type="digit placeholder">
  Shows a digit, or nothing if there is none. `#,##0` turns `42` into `42`, not `00042`.
</ParamField>

<ParamField path="?" type="digit placeholder">
  Shows a digit, or a space. Use it to align decimal points down a column.
</ParamField>

<ParamField path="," type="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.
</ParamField>

<ParamField path="&#x22;text&#x22;" type="literal">
  Anything in double quotes prints as-is. `#,##0" units"` appends a label.
</ParamField>

<ParamField path="[Color]" type="modifier">
  Colors the section. Excel limits this to its built-in set: `[Red]`, `[Blue]`, `[Green]`, `[Yellow]`, `[Cyan]`, `[Magenta]`, `[White]`, `[Black]`, and `[Color1]` through `[Color56]`.
</ParamField>

<ParamField path="[>=100]" type="condition">
  Applies the section only when the value meets the test, so one format can branch on magnitude.
</ParamField>

### Worked examples

<CodeGroup>
  ```python Accounting theme={null}
  column_formats = {
      # Negatives in parentheses and red, decimals aligned
      "Balance": "$#,##0.00_);[Red]($#,##0.00)",
  }
  ```

  ```python Scaled theme={null}
  column_formats = {
      # 5000000 reads as "5.0M", 15000 as "15.0K", 500 as "500"
      "Revenue": '[>=1000000]#,##0.0,,"M";[>=1000]#,##0.0,"K";#,##0',
  }
  ```

  ```python Signed theme={null}
  column_formats = {
      # +150 green, -75 red, 0 blue
      "Change": "[Green]+#,##0;[Red]-#,##0;[Blue]0",
  }
  ```

  ```python Text theme={null}
  column_formats = {
      # Numeric codes rendered as words
      "Status": '[=1]"Active";[=0]"Inactive";@',
  }
  ```
</CodeGroup>

## What Jetxl checks

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

<AccordionGroup>
  <Accordion title="Raises an error" icon="circle-exclamation">
    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.
  </Accordion>

  <Accordion title="Prints a warning" icon="triangle-exclamation">
    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.
  </Accordion>

  <Accordion title="Passes through unchecked" icon="circle-minus">
    Anything else. A malformed code containing digits still reaches Excel, which complains when you open the file rather than when you write it.
  </Accordion>
</AccordionGroup>

## Limits

<CardGroup cols={2}>
  <Card title="255 characters" icon="ruler-horizontal">
    Excel's own ceiling on the length of a format code.
  </Card>

  <Card title="Escaping is handled" icon="shield-check">
    Jetxl escapes the XML-significant characters for you.
  </Card>

  <Card title="Built-in colors only" icon="palette">
    Color names inside a format code come from Excel's fixed set, not arbitrary hex.
  </Card>

  <Card title="Version differences" icon="rotate">
    Locale codes and `DBNum` variants may not render in every Excel version.
  </Card>
</CardGroup>

<Note>
  For the full syntax, see [Excel number format codes](https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68) from Microsoft.
</Note>