Number format codes

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)