Excel 2010 Bible

Part IV: Using Advanced Excel Features

562

Displaying values in millions

The following format string displays values in millions with no decimal places. A value with this

number appears as if it’s divided by 1,000,000 and rounded to no decimal places.

#,###,,

A variation of this format string follows. A value with this number appears as if it’s divided by

1,000,000 and rounded to two decimal places.

#,###.00,,

Another variation follows. This adds the letter M to the end of the value.

#,###,,M

The following format string is a bit more complex. It adds the letter M to the end of the value —

and also displays negative values in parentheses as well as displaying zeros.

#,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_)

Table 24.7 shows examples of these format strings.

TABLE 24.7

Examples of Displaying Values in Millions

Value Number Format Display

123456789 #,###,, 123

1.23457E+11 #,###,, 123,457

1000000 #,###,, 1

(^5000000) #,###,, 5
–5000000 #,###,, –5
(^0) #,###,, (blank)
123456789 #,###.00,, 123.46
1.23457E+11 #,###.00,, 123,457.00
1000000 #,###.00,, 1.00
5000000 #,###.00,, 5.00
–5000000 #,###.00,, –5.00
0 #,###.00,, .00
123456789 #,###,,”M” 123M
1.23457E+11 #,###,,”M” 123,457M
1000000 #,###,,”M” 1M