The Custom formats guide
Press Ctrl+1 (⌘+1 on a Mac), choose Custom and type a code. 0 is a digit that always shows, text goes in quotes, and a comma after the last digit divides by 1,000. The value in the cell stays the same; only what it shows changes.
The pieces of a code
| Code | Means | 2134 shows as |
|---|---|---|
0 | a digit, shown even when it's a leading zero | 00000 → 02134 |
# | a digit, shown only if there is one | ##### → 2134 |
, between digits | thousands separator | #,##0 → 2,134 |
, after the last digit | divide by 1,000 (two commas: a million) | 0.0,"K" → 2.1K |
"text" | shown as it is | "INV-"0 → INV-2134 |
Microsoft's own example: 0.0,,"M" shows 12,200,000 as 12.2M.
Why a format, not typing
Typing 02134 into a cell gives 2134: Excel drops the zero. Typing it as text ('02134) keeps the zero but turns the cell into text, which sorts and looks up differently from numbers. A format keeps the number and shows the zero. For codes that are really labels, like phone numbers or account numbers longer than 15 digits, text is the right choice.
Up to four sections
A code can have four parts separated by semicolons: positive; negative; zero; text. #,##0;(#,##0);"–" shows negatives in brackets and zero as a dash. With one section, it applies to every number.
Common mistakes
- Using # for leading zeros. # never adds a zero; 0 does.
- A K without the comma.
#,##0"K"shows 1,250,000K: the label is there, the division isn't. - Too few zeros.
"INV-"00pads to two digits, not four. - Expecting the value to change. A cell showing 1,250K still holds 1,250,000, and SUM uses that.
FAQ
- How do I keep leading zeros in Excel?
- Format the cells with a custom code of zeros, one per digit: 00000 shows 2134 as 02134 and keeps it a number. Or set the cells to Text before you type, if the codes are labels rather than numbers.
- How do I show numbers in thousands with K in Excel?
- Use the custom format #,##0,"K". The comma after the last 0 divides by 1,000 for display; the "K" is text. For millions, use two commas: 0.0,,"M".
- How do I add text to a number format in Excel?
- Put the text in double quotes in a custom format, like "INV-"0000. The cell still holds the number.
- Does a custom number format change the value?
- No. It changes only what the cell shows. Formulas use the stored value.



































































