Excel Number Format Codes Cheat Sheet
Custom format codes with what each one actually displays — numbers, conditional colours, dates and elapsed time.
Numbers
| Format code | 1234.567 displays as | Use it for |
|---|---|---|
| 0 | 1235 | Whole numbers, rounded for display only |
| 0.00 | 1234.57 | Two decimal places, always shown |
| #,##0 | 1,235 | Thousands separators |
| #,##0.00 | 1,234.57 | The everyday accounting look |
| #,##0,, "M" | 0 M | Millions — each comma before the quote divides by a thousand |
| 0.0% | 123456.7% | Percentages (the value is multiplied by 100) |
| # ?/? | 1234 4/7 | Fractions |
| 0.00E+00 | 1.23E+03 | Scientific notation |
Conditional and multi-part
| Format code | 1234.567 displays as | Use it for |
|---|---|---|
| 0;[Red]-0 | 1235 | Negatives in red — parts are positive;negative |
| 0;-0;;@ | 1235 | Hide zeros — the third part (zero) is left empty |
| 0;-0;0;"n/a" | 1235 | Four parts: positive;negative;zero;text |
| [>=1000]#,##0,"k";0 | 1k | A condition in square brackets before the format |
| "£"#,##0.00 | £1,234.57 | A currency symbol that does not change the value |
Dates and times
| Format code | 1234.567 displays as | Use it for |
|---|---|---|
| dd/mm/yyyy | — | Day first, four-digit year |
| mmm yyyy | — | Short month and year, e.g. Aug 2026 |
| dddd | — | Full weekday name |
| hh:mm | — | Clock time, resets at 24 hours |
| [h]:mm | — | ELAPSED hours — the brackets stop it wrapping at 24 |
| @ | 1234.567 | Force text |
About this sheet
A custom number format changes only what is displayed, never the stored value — which is what makes it safe, and also what makes it confusing when a cell shows 1,235 and the formula bar says 1234.567. The four-part structure (positive;negative;zero;text) explains most of the codes that look cryptic, and [h]:mm is the one people search hardest for: it is how you total more than 24 hours without the clock wrapping.
Free to use, share and republish — including commercially. Attribution isn't required, but it's what keeps these free to make, and there's one ready to paste below.
Questions
Is this cheat sheet free?
Yes, and there is no email gate. The whole table is on this page, and the download is built in your browser when you click it.
Can I print it?
Yes — the print layout drops the navigation and page furniture and keeps the tables, so it comes out as a clean reference rather than a screenshot of a web page.
What formats can I download?
Excel (.xlsx) and CSV. Both hold exactly the rows shown on this page.
Is this reference complete?
It covers the entries people actually look up. A reference you can read in one sitting beats an exhaustive dump you scroll past — the same reason the shortcuts sheet is curated rather than a 350-row export.