Basic number format codes
Decimal points To format fractions or numbers with decimal points, include the following digit placeholders in a section. If the number has more digits to the right of the decimal point than there are placeholders in the format, the number is rounded to as many decimal places as there are placeholders. If the number has more digits to the left of the decimal point than there are placeholders in the format, the extra digits are displayed. If the format contains only number signs (#'s) to the left of the decimal point, numbers less than 1 begin with a decimal point.
The placeholder # displays only significant digits; it does not display insignificant zeros.
| To display | Use this format code |
|---|---|
| 1234.59 as 1234.6 | ####.# |
The placeholder 0 (zero) displays insignificant zeros if a number has fewer digits than there are 0's in the format.
| To display | Use this format code |
|---|---|
| 8.9 as 8.900 | #.000 |
| .631 as 0.6 | 0.# |
| 12 as 12.0, and 1234.568 as 1234.57 | #.0# |
The placeholder ? adds spaces for insignificant zeros on either side of the decimal point so that when formatted with a fixed-width font, such as Courier New, decimal points align. You can also use this symbol for fractions that have varying numbers of digits.
| To display | Use this format code |
|---|---|
| 44.398, 102.65, and 2.8 with aligned decimals | ???.??? |
| 5.25 as 5 1/4, and 5.3 as 5 3/10, with aligned division symbols | # ???/??? |
Thousands separator To display a comma as a thousands separator or to scale a number by a multiple of one thousand, include a comma in the number format.
| To display | Use this format code |
|---|---|
| 12000 as 12,000 | #,### |
| 12000 as 12 | #, |
| 12200000 as 12.2 | 0.0,, |
Using colors To set the color for a section of the format, type the name of the color in square brackets in the section.
| Color codes | |
|---|---|
| [BLACK] | [BLUE] |
| [CYAN] | [GREEN] |
| [MAGENTA] | [RED] |
| [WHITE] | [YELLOW] |
Conditional number format codes To set number formats that will be applied only if the number meets a condition you specify, enclose the condition in square brackets; the condition consists of a comparison operator and a value. For example, the following format displays numbers less than or equal to 100 in a red font color and numbers greater than 100 in a blue font color.
[Red][<=100];[Blue][>100]