Excel custom number format codes

1. What a number format is

An Excel number format changes how a value looks without changing the value. Apply a thousands separator to 1234567 and the cell shows 1,234,567 while still holding 1234567. Use that cell in a formula and nothing changes.

When none of the built-in formats gives the look you need, you write a code in the Custom box yourself. The rules for that code are what this page is about.

2. The basic rules: four sections, three placeholders

A code splits on semicolons into at most four sections, in the order positive, negative, zero, text. That is straight from Microsoft's documentation. One section applies to everything; two split positive from negative.

There are three placeholders. 0 pads a missing place with a zero. # leaves it out and shows only what is there. A question mark inserts a space instead of a zero so that decimal points line up down a column. All three descriptions come from the documentation.

So 1.5 under 0.00 renders as 1.50, and under #.## as 1.5. That difference is the rule you will use most.

A comma after the digits divides by 1,000. #,##0, shows 1234567 as 1,235, and a second comma scales to millions. The cell value is untouched.

There are eight colours (Black, Blue, Cyan, Green, Magenta, Red, White, Yellow), written in brackets. The documentation states that the colour code must be the first item in its section. Conditions go in brackets too, as in [Red][<=100].

3. The rules inside this page

Choose decimals and a negative style up top and the code is written for you. Sections that would only restate default behaviour are left out: if negatives just need a minus sign, Excel adds one without a second section, so the code stays short.

Paste a code you already have and the page shows how it splits into sections, the colour and condition on each, and how it renders against the sample value.

The preview is this page re-applying the documented rules, not Excel drawing it. Some codes it cannot handle: scientific notation (E+), fractions (?/?), fill characters (*), skipped width (_), alternate numerals ([DBNum4]), currency or calendar overrides ([$-...]), and elapsed time ([h]). Those are reported rather than approximated. The code itself still works in Excel.

In date codes, m means both months and minutes. The official table lists both and gives no rule for telling them apart, so this page treats m as minutes after an hour code or before a seconds code. The page says on screen that this is its own rule.

Nothing you enter is sent to a server. The work happens in your browser, and the server learns only three facts: that the page opened, that a code was produced, and that a link was copied. Nothing is stored, so a refresh clears it.

4. Getting it right

Copy the code, open Format Cells in Excel (Ctrl+1), pick Custom on the Number tab, and paste it into the Type box. It stays at the bottom of the list for reuse.

To append a unit, type it into the text-after box and the page wraps it in quotes for you. Without quotes, Excel reads some characters as codes rather than as text.

For a table of large figures, scaling down makes it far easier to read. Remember the underlying values do not change, so totals still come out at full size.

5. A little deeper

The fourth section handles text, using @ to stand for whatever text the cell holds. It rarely matters in a purely numeric table, but it helps in a mixed column.

Condition sections vary the look by value. The documentation's own example is [Red][<=100];[Blue][>100]. You get two conditions, and the last section catches everything else.

To hide zeros, leave the third section empty. The code then ends with a semicolon, and an empty section draws nothing.

6. Where people get stuck

If decimal points do not line up down a column, swap 0 for a question mark. It pads with spaces rather than zeros, which is exactly what alignment needs.

If mm in a date renders as minutes, look at what sits beside it. Following an hour code makes it minutes. Move it away from the time codes, or pair it with date codes.

If added text renders oddly, Excel read it as a code. Quotes make anything render literally, and the codes this page builds already include them.