Chapter 17 · 17.20
Custom number formats: "12 pcs", amounts in thousands, red for negatives
If you type "12 pcs" or "7.5 hrs" in a cell, it becomes text and you can no longer calculate with it. With a custom number format, the cell stays a number (12) while you still see "12 pcs". You can also use custom formats to show negative amounts in red, a dash instead of a zero, or amounts in thousands. The standard formats are covered in Formatting numbers, dates and currency; this article is about your own formats.
Where do you type a custom format?
- Select the cells.
- On the Home tab, click the number format drop-down list and choose More formats at the bottom.
- In the Number Format window, under Category, choose Custom.
- Type the code in the field under Format, or pick an existing code from the list and edit it. Under Preview you immediately see how the cell will look.
- Click OK.

The codes follow the notation of your region: in English, a full stop for decimals and a comma for thousands, as in #,##0.00. You can also see this in the codes Euro-Office itself puts in the list.
Useful codes
| What you want | Code | Number | You see |
|---|---|---|---|
| Pieces | 0" pcs" | 12 | 12 pcs |
| Hours with one decimal | 0.0" hrs" | 7.5 | 7.5 hrs |
| Red for negative, dash for zero | € #,##0.00;[Red]-€ #,##0.00;"-" | -830 | -€ 830.00 in red |
| In thousands | #,##0.0,"k" | 12,345 | 12.3k |
| Leading zeros (see also Keeping the leading zero in a phone number or postcode) | 00000 | 42 | 00042 |
| Text with a prefix | 0;-0;0;"status: "@ | ordered | status: ordered |

How a code is built
- 0 is a digit that is always shown, # a digit that only appears when needed.
- Text goes between quotation marks: " pcs", " hrs".
- A code can have up to four sections, separated by a semicolon: positive;negative;zero;text. If you leave sections out, Euro-Office uses the first section for everything.
- You put a colour between square brackets at the start of a section. Euro-Office uses the English colour names: [Red], not [Rood].
- A comma after the number code divides by a thousand: #,##0.0, shows 12,345 as 12.3. With two commas you divide by a million.
- @ stands for the text in the cell.
Pitfalls
- Typed units: if the cell already contains "12 pcs" as text, a format won't help. Retype just the number.
- Letters without quotation marks: in a format, h, m, s, d and y mean hour, month, second, day and year. So always put text between quotation marks.
- The number doesn't change: showing it in thousands doesn't mean the cell contains 12.3. Calculations still use 12,345.
- A typo in the code quickly leads to an odd display. So check Preview before you click OK.
