You can use number format codes to control the display of digits before and after the decimal place. Use the number sign (#) if you want to display only the significant digits in a number. This sign does not allow the display non-significant zeros. Use the numerical character for zero (0) if you want to display non-significant zeros when a number might have fewer digits than have been specified in the format code. Use a question mark (?) if you want to add spaces for non-significant zeros on either side of the decimal point so that the decimal points align when they are formatted with a fixed-width font, such as Courier New. You can also use the question mark (?) to display fractions that have varying numbers of digits in the numerator and denominator.
If a number has more digits to the left of the decimal point than there are placeholders in the format code, the extra digits are displayed in the cell. However, if a number has more digits to the right of the decimal point than there are placeholders in the format code, the number is rounded off to the same number of decimal places as there are placeholders. If the format code contains only number signs (#) to the left of the decimal point, numbers with a value of less than 1 begin with the decimal point, not with a zero followed by a decimal point.
To display
As
Use this code
1234.59
1234.6
####.#
8.9
8.900
#.000
.631
0.6
0.#
12
1234.568
12.0
1234.57
#.0#
Number:
44.398
102.65
2.8
Decimal points aligned:
44.398
102.65
2.8
???.???
Number:
5.25
5.3
Numerators of fractions aligned:
5 1/4
5 3/10
# ???/???
To display a comma as a thousands separator or to scale a number by a multiple of 1000, include a comma (,) in the code for the number format.
To display
As
Use this code
12000
12,000
#,###
12000
12
#,
12200000
12.2
0.0,,
To display leading and trailing zeros prior to or after a whole number, use the codes in the following table.
To display
As
Use this code
12
123
00012
00123
00000
12
123
00012
000123
“000”#
123
0123
“0”#
To specify the color for a section in the format code, type the name of one of the following eight colors in the code and enclose the name in square brackets as shown. The color code must be the first item in the code section.
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
To indicate that a number format will be applied only if the number meets a condition that you have specified, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following number format will display numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.
[Red][<=100];[Blue][>100]
To hide zeros or to hide all values in cells, create a custom format by using the codes below. The hidden values appear only in the formula bar. The values are not printed when you print your sheet. To display the hidden values again, change the format to the General number format or to an appropriate date or time format.
To hide
Use this code
Zero values
0;–0;;@
All values
;;; (three semicolons)