The main advantage of this approach is that you can alter the look of your data without changing the actual values. This means that you do not need to use additional spaces or formulas to create the layout you want and preserve the raw data.
If you want to modify your data anyways, or need to change a value inside a formula, you can use the TEXT function with all custom formatting syntax we are going to cover in this article. It should be noted that the TEXT function returns a text, and the return value cannot be used in mathematical calculations. If you do, you will receive a #VALUE! error. In this article we're going to be using a workbook template. You can download it below.
How to create a custom number format in Excel
- Select the cell to be formatted and press Ctrl+1 to open the Format Cells dialog. An alternative way to do is by right-clicking the cell and then going to Format Cells > Number Tab.
- Under Category, select Custom.
- Type in the format code into the Type
- Click OK to save your changes.
Note: In Format Cells dialog you can modify the built-in format codes by selecting the format you want to modify in its own category (i.e. Currency > ($1,234.10)) and then selecting Custom Category. Don’t worry, Excel will not let you delete built-in formats.
Basics
Syntax
The format code has 4 sections separated by semicolons.
POSITIVE; NEGATIVE; ZERO; TEXT
These sections are optional,
- If a code contains only 1 section, the format is applied to all number types - positive, negative and zero.
- If a code contains 2 sections, the first section is used for positive and zero values, while the second section is applied to negative values.
- If a code contains 3 sections, the first is for positive, the second is for negative, and the third is for zero.
- A code only affects text values if all sections exist.
Default format type in Excel is called General. You can type General for sections you don’t want formatted. Make sure you use a minus sign (-) with General if you want to skip negative values.
If you want to completely hide a type, leave it blank after the semicolon. For example; to hide 0 values, General;-General;;General
Placeholders and the Cheat Sheet
| Placeholder | Description | Raw Value | Format Code | Formatted Value |
| General | Default format | 1234.567 | General | 1234.567 |
| # | Placeholder for digits (numbers) and does not add any leading zeroes. | 1234.567 | #####.#### | 1234.567 |
| 0 | Placeholder for digits (numbers) and add any leading zeroes. | 1234.567 | 00000.0000 | 01234.5670 |
| ? | Placeholder for digits (numbers) and add space characters. | 1234.567 | ?????.???? | 1234.567 |
| . | Placeholder for the decimal place. | 1234.567 | 0.00 | 1234.57 |
| _ | Adds a blank space, to the width of the following character. You can use in combination with parentheses to add left and right indents, _( and _) respectively. | 99 | _(#_);(#) | 99 |
| -25 | (25) | |||
| 58 | 58 | |||
| 12 | 12 | |||
| -71 | (71) | |||
| 36 | 36 | |||
| * | Repeats the character after asterisk until the width of the cell is filled. | 66 | 0 *! | 66 !!!!!!!!!!!!!!! |
| Full Name | @ *_ | Full Name ____ | ||
| % | Convert value to a percentage with % sign | 0.12 | % | 12% |
| , | Thousands separator | 1234.567 | #, | 1 |
| 12345678 | #, | 12,346 | ||
| 12345678 | #,###, | 12,346 | ||
| 12345678 | #,, | 12 | ||
| E | Scientific notation format. Requires a '+' symbol after, and a digit placeholder before and after. | 1234.567 | 0.00E+00 | 1.23E+03 |
| / | Represents fractions | 1.234 | # ##/## | 1 11/47 |
| 1.234 | # 000/000 | 1 117/500 | ||
| 1.234 | ##/## | 58/47 | ||
| "" | Text placeholder for multiple characters | 1234.567 | #,##0 "km/h" | 1,235 km/h |
| Good | "Result is: "@ | Result is: Good | ||
| \ | Text placeholder for single character | 1234 | #.00, \K | 1.23 K |
| 1234567 | #.00,, \M | 1.23 M | ||
| @ | Placeholder for text | Bad | "Result is: "@ | Result is: Bad |
| [color] | Change Color of value. Options: [Black], [Green], [White], [Blue], [Magenta], [Yellow], [Cyan], [Red] | 1234.567 | [Green]#,##0.00_); [Red](#,##0.00); [Blue]0.00_); [Magenta]@ |
1,234.57 |
| -1234.567 | (1,234.57) | |||
| 0 | 0.00 | |||
| This is a text | This is a text |
Common Practices
Display and control of the first digit and decimals
Decimal places in the code are indicated with a period (.). Number of zeroes after the period (.) define the number of decimal places. For example,
- 0 - display 1 decimal place
- 00 - display 2 decimal places
If the number has more decimals than the decimal placeholders defined, the number will be rounded to the nearest number of placeholders.
| Raw Value | Format Code | Formatted Value |
| 123.4 | 0.0 | 123.4 |
| 123.4 | 0.00 | 123.40 |
| 123.45 | 0.00 | 123.45 |
| 123.45 | 0.00 | 123.46 |
| 123.456 | 0.0 | 123.5 |
Alternatively, hash (#) and question mark (?) symbols can be used as decimal places. However, because any missing decimal places will be filled with zeroes, using zeroes instead will be easier to read.
| Raw Value | Format Code | Formatted Value |
| 0.25 | 0.00 | 0.25 |
| 0.25 | #.## | .25 |
| 123 | 0.00 | 123.0 |
| 123 | #.?? | 123.00 |
| 123 | #.## | 123. |
Add text to numbers
Custom text can be added to the beginning or the end of a value. Text and characters should be added inside quotes ("") and backslashes (\). You can use backslash (\) to add single character.
| Raw Value | Format Code | Formatted Value |
| 123.4 | 0.0 "ft." | 123.4 ft. |
| 123.4 | 0.00 \l | 123.40 l |
| 123.45 | "Approx." 0 | Approx. 123 |
| 123.45 | "Result:" 0.00 \C | Result: 123.46 C |
| Bad | "Result is: "@ | Result is: Bad |
Quotation marks or backslashes are not necessary for spaces ( ) and some special characters.
| Symbol | Description |
| + and - | Plus and minus signs |
| ( ) | Left and right parenthesis |
| : | Colon |
| ^ | Caret |
| ' | Apostrophe |
| { } | Curly brackets |
| < > | Less-than and greater than signs |
| = | Equal sign |
| / | Forward slash |
| ! | Exclamation point |
| & | Ampersand |
| ~ | Tilde |
| Space character |
Below are some special characters you can use by copying or typing in the numerical code while pressing down Alt button.
| Symbol | Code | Description |
| ™ | Alt+0153 | Trademark |
| © | Alt+0169 | Copyright symbol |
| ° | Alt+0176 | Degree symbol |
| ± | Alt+0177 | Plus-Minus sign |
| µ | Alt+0181 | Micro sign |
Hide value
If you leave any number of sections blank, the value of those sections will be hidden. A section should always be separated (defined) by a semicolon (;). Here are some examples,
| Raw Value | Format Code | Formatted Value |
| 1 | 0;;0; | 1 |
| -2 | 0;;0; | |
| 0 | 0;;0; | 0 |
| Some text | 0;;0; | |
| 1 | ;(0);;@ | |
| -2 | ;(0);;@ | (2) |
| 0 | ;(0);;@ | |
| Some text | ;(0);;@ | Some Text |
| 1 | ;;; | |
| -2 | ;;; | |
| 0 | ;;; | |
| Some text | ;;; |
Replace zeroes with dashes
Zeroes can make data tables look more complicated than they actually are. You can hide them completely by using the previous method, or replace them with any character of your choice. Dash (-) is a common example. All you need to is place a dash into the ‘Zero section’.
| Raw Value | Format Code | Formatted Value |
| 0 | General;-General;"-";General | - |
| 3487 | General;-General;"-";"-" | - |
| 12 | #,##0.00;(#,##0.00);"-"; | - |
Start with zeroes
If try to enter a ZIP number that starts with 0, the leading zeroes will be removed automatically by Excel. To keep the leading zeros, use zero (0) placeholder for whole numbers.
| Raw Value | Format Code | Formatted Value |
| 10010 | 00000 | 10010 |
| 3487 | 00000 | 03487 |
| 12 | 00000 | 00012 |
| 0 | 00000 | 00000 |
| 123456 | 00000 | 123456 |
Dealing with thousands, millions, and more
You may have noticed that ‘0.0’ or other simple formats do not separate thousands or millions. Adding a comma into the code will insert commas to separate numbers.
| Raw Value | Format Code | Formatted Value |
| 1234 | #,##0 | 1,234 |
| 123456 | #,##0 | 123,456 |
| 12345678 | #,##0 | 12,345,678 |
| 123456.789 | #,##0 | 123,457 |
| 123456.789 | #,##0.0 | 123,456.8 |
There must be placeholders for numbers smaller than one thousand, otherwise such values will be hidden. This behavior allows us to round and format our value to show only thousands or millions.
| Raw Value | Format Code | Formatted Value |
| 1234 | #, | 1 |
| 123456 | #, | 123 |
| 12345678 | #, | 12345 |
| 12345678 | #,, | 12 |
| 123456 | #.0, \K | 123.5 K |
| 12345678 | #.0,, \M | 12.3 M |
Display numbers as phone numbers
Phone numbers can be hard to read without any separators. Custom Number Format Codes is perfect for this job. The hash (#) character should be your best bet to avoid any redundancy of placeholders (0, ?)
| Raw Value | Format Code | Formatted Value |
| 1234567890 | (###) ###-#### | (123) 456-7890 |
| 12345678900 | (###) #### #### | (123) 4567 8900 |
| 1234567890 | (##) #### #### | (12) 3456 7890 |
Showing Month and Weekday Names
Date and time values are stored as numbers in Excel. When you enter a date, Excel automatically converts it into a numerical value, and then formats the cell.
Before jumping into the code, let’s review some basics. Formatting code has special placeholders for date and time formatting that behave a bit differently. For example, while m and mm will show month as a number, mmm and mmmm will show as a text string. Below are some examples.
| Raw Value | Format Code | Formatted Value |
| 4/1/2018 | m | 4 |
| 4/1/2018 | mm | 04 |
| 4/1/2018 | mmm | Apr |
| 4/1/2018 | mmmm | April |
| 4/1/2018 | mmmmm | A |
| 4/1/2018 | d | 1 |
| 4/1/2018 | dd | 01 |
| 4/1/2018 | ddd | Sun |
| 4/1/2018 | dddd | Sunday |
| 4/1/2018 11:59:31 PM | dddd, mmmm dd, yyyy h:mm AM/PM;@ | Sunday, April 01, 2018 11:59 PM |
Here is the full list of options for the date 4/1/2018 23:59:31 ,
| Format Code | Description | Example (4/1/2018 23:59:31) |
| yyyy | Displays the year as a four-digit number. | 2018 |
| yy | Displays the year as a two-digit number. | 18 |
| m | Displays the month as a number without a leading zero. | 4 |
| mm | Displays the month with a leading zero. | 04 |
| mmm | Displays the month as text, as an abbreviation. | Apr |
| mmmm | Displays the month as text. | April |
| mmmmm | Displays the month as a single character | A |
| d | Displays the day as a number, without a leading zero. | 1 |
| dd | Displays the day as a number, with a leading zero. | 01 |
| ddd | Displays the day as a day of the week, as an abbreviation. | Sun |
| dddd | Displays the day as a day of the week, without abbreviation | Sunday |
| h | Displays the hour without a leading zero. | 23 |
| hh | Displays the hour with a leading zero. | 23 |
| [h] | Displays elapsed time in hours (to be used when the time value exceeds 24 hours). | 1036607 |
| m | Displays the minute without a leading zero. | 4 |
| mm | Displays the minute with a leading zero. | 04 |
| [m] | Displays elapsed time in minutes (to be used when the time value exceeds 60 minutes). | 62196479 |
| s | Displays the second without a leading zero. | 31 |
| ss | with a leading zero. | 31 |
| [s] | Displays elapsed time in seconds (to be used when the time value exceeds 60 seconds). | 3731788771 |
| AM/PM | Converts to 12-hour time. Displays either AM/am/A/a or PM/pm/P/p depending on the time of day. | PM |
| am/pm | pm | |
| A/P | P | |
| a/p | p |
Come in Colors Everywhere
Number Formatting can be used color sections of a code. A common example is using the color red for negative numbers. Color code must be placed inside square brackets (i.e. [color]), and entered at the beginning of a section. Here are some available colors,
- [Black]
- [Blue]
- [Cyan]
- [Green]
- [Magenta]
- [Red]
- [White]
- [Yellow]
| Raw Value | Format Code | Formatted Value |
| 1234.567 | [Green]#,##0.00_);[Red](#,##0.00);[Blue]0.00);[Magenta]@ | 1,234.57 |
| -1234.567 | (1,234.57) | |
| 0 | 0.00 | |
| This is a text | This is a text |
Conditions
Although Excel has conditional formatting menu, basic conditions can be applied through code. Condition should be placed inside square brackets (i.e. [condition]) just like colors. Conditions are similar to the conditions in some functions (i.e. SUMIF). First add a logical operator, and then a value. For example, “[>=1000000]” means “if value of cell is greater than or equal to 1,000,000 apply the following format”. Conditions should come before the actual code, again, just like with colors. If you want to a color as well, the color code should come first.
Another important thing to note here is, that section structure changes from Positive, Negative, Zero, Text to First Condition, Second Condition (if exists), if previous conditions are not applied. There should be at least two sections for conditions.
If you enter only one condition code and then save the format, Excel will automatically add the second section with ";General". This means that if the condition is not met, General format will be used.
| Raw Value | Format Code | Formatted Value |
| 1234567890 | [>=1000000]#,##0,,"M";[>=1000]#,##0,"K";0 | 1,235M |
| 12345 | 12K | |
| 1 | [=1]0" apple";0" apples" | 1 apple |
| 10 | 10 apples | |
| 25 | [Green][>=85]"PASSED";[Blue][>=60]"RE-CHECK";[Red]"FAILED" | FAILED |
| 72 | RE-CHECK | |
| 91 | PASSED |
