Excel Tutorials
Ready-to-use format codes for currency, abbreviated thousands, colored negatives and phone numbers, plus how the format code syntax actually works so you can build your own. This lesson uses the Order Manager workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Custom Number Format is a code you write that controls exactly how a number displays, currency symbols, thousands separators, decimal places, even color, without changing the actual value stored in the cell at all. A cell formatted to show "$1.2K" still holds the real number 1200 underneath, and every formula referencing it uses that real value, not the formatted text.
Excel ships with common presets like Currency and Percentage, but the Custom category lets you write your own format code from scratch, which is where the genuinely useful tricks live.
Step-by-step, matching the video above.
Click a single cell, or select a range or column to format multiple cells at once.
Press Ctrl+1, or right-click the selection and choose Format Cells.
Scroll to the bottom of the Category list and click Custom.
Enter a code in the Type box, like #,##0.00 for a number with a thousands separator and two decimals.
The Sample area above the Type box shows exactly how the format will look on your actual data before you commit to it.
Custom formats you create also get saved to the top of the Custom list, ready to reuse on other cells later.
A single format code can actually contain up to four different formats, separated by semicolons, each controlling a different kind of value.
Positive numbers first, then negative, then zero, then text, each section separated by a semicolon: positive;negative;zero;text. You don't have to fill in all four, but the ones you do include follow this exact order.
$#,##0;[Red]($#,##0);"-";"N/A" displays positive numbers in black with a dollar sign, negative numbers in red wrapped in parentheses, zero as a plain dash, and any text entry as "N/A".
A code with just one section, like $#,##0, applies to all numbers, positive and negative alike. Add a semicolon and a second section only once you need negatives to look different.
| Raw Value | Format Code | Displays As |
|---|---|---|
| 1200 | #,##0,"K" | 1K |
| 2500000 | #,##0,,"M" | 3M |
| 7 | 00000 | 00007 |
| -450 | #,##0;[Red]-#,##0 | -450 (in red) |
| 5551234567 | (000) 000-0000 | (555) 123-4567 |
Every one of these is still the exact same underlying number, only how it displays has changed. Notice the trailing comma in #,##0,"K" divides the value by 1,000 before displaying it, and two trailing commas in #,##0,,"M" divide by 1,000,000, a genuinely non-obvious trick in the format syntax.
Copy any of these directly into the Type box.
$#,##0 shows a dollar sign and thousands separator with no cents, useful for whole-dollar totals.
$#,##0.00 adds two decimal places for exact amounts.
0.0% displays a value like 0.256 as 25.6%.
[Green]#,##0;[Red]-#,##0 shows positive numbers in green and negative numbers in red, a quick visual cue without a separate conditional formatting rule.
#,##0;-#,##0;; leaves the third section blank, so any cell equal to exactly zero displays as nothing instead of a distracting 0.
mmm d, yyyy displays a date as "Sep 2, 2026" regardless of how the date was originally typed in.
These two look similar but behave differently, and picking the wrong one is the most common reason a custom format doesn't look right.
A # only shows a digit if one actually exists there. The code ##.## on the number 5 displays just "5", no trailing decimal or padding zeros.
A 0 forces a digit to appear no matter what, padding with a zero if needed. The code 00.00 on the number 5 displays "05.00".
A 5-digit zip code stored as 07030 loses its leading zero under General formatting, since Excel treats it as the number 7030. Formatting it as 00000 forces the leading zero to display correctly, purely visually.
A cell displaying $1.2K is still storing and calculating with 1200 underneath. Formulas referencing it use the real number, not the formatted text, which occasionally surprises people expecting otherwise.
The four sections are always positive;negative;zero;text in that fixed order. Writing them out of order applies the wrong formatting to the wrong kind of value.
# drops insignificant digits, 0 pads with zeros. Using # on something like a zip code or ID number that needs consistent leading zeros will silently drop them.
If a zip code like 07030 was typed into a General-formatted cell, Excel may have already stored it as the number 7030, losing the zero before any custom format is even applied. Format the column first, or enter the data as text.
A professional-looking number shouldn't need a format code behind it.
Free 14-day trial. No credit card required.
A format code that changes how a number displays, currency, percentage, abbreviated thousands, colored negatives, without changing the actual value stored in the cell.
No. It's purely visual. A cell formatted to show $1.2K still holds and calculates with its real underlying value, like 1200, in every formula that references it.
Select the cells, press Ctrl+1, go to the Number tab, choose Custom, and type a format code into the Type box.
Use #,##0,"K" for thousands (one trailing comma divides by 1,000) or #,##0,,"M" for millions (two trailing commas divide by 1,000,000).
Use a format code like 00000, each 0 forces a digit to show, padding with leading zeros as needed, useful for zip codes or ID numbers.
Use a format code like #,##0;[Red]-#,##0, the second section (after the first semicolon) controls how negative numbers display.
Use a format code like #,##0;-#,##0;, leaving the third section (for zero values) empty makes any zero display as nothing at all.
# is an optional digit that doesn't show insignificant zeros, while 0 always shows a digit, padding with zero if there isn't one, which is why 00000 pads short numbers with leading zeros and ##### doesn't.
Use a format code like (000) 000-0000 on a column of 10-digit numbers to display them as formatted phone numbers automatically.
Yes, codes like mmm d, yyyy or dd/mm/yyyy control exactly how a date displays, using the same custom format dialog as numbers.
Every business starts with a spreadsheet. Updoot is where you scale past it, no format code required.
Start Your Free Trial →