Excel 2010 Bible

Chapter 24: Using Custom Number Formats

563

Value Number Format Display

5000000 #,###,,”M” 5M

–5000000 #,###,,”M” –5M

0 #,###,,”M” M

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

1.23457E+11 #,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_) 123,456.8M

(^1000000) #,###.0,,”M”);(#,###.0,,”M)”;0.0”M”) 1.0M
5000000 #,###.0,,”M”);(#,###.0,,”M)”;0.0”M”) 5.0M
–5000000 #,###.0,,”M”);(#,###.0,,”M)”;0.0”M”) (5.0M)
0 #,###.0,,”M”);(#,###.0,,”M)”;0.0”M”) 0.0M
Adding zeros to a value
The following format string displays a value with three additional zeros and no decimal places. A
value with this number format appears as if it’s rounded to no decimal places and then multiplied
by 1,000.
#”,000”
Examples of this format string, plus a variation that adds six zeros, are shown in Table 24.8.
TABLE 24.8
Examples of Displaying a Value with Extra Zeros
Value Number Format Display
1 #”,000” 1,000
1.5 #”,000” 2,000
43 #”,000” 43,000
–54 #”,000” –54,000
5.5 #”,000” 6,000
0.5 #”,000,000” 1,000,000
0 #”,000,000” ,000,000
1 #”,000,000” 1,000,000
1.5 #”,000,000” 2,000,000
43 #”,000,000” 43,000,000
–54 #”,000,000” –54,000,000
5.5 #”,000,000” 6,000,000
0.5 #”,000,000” 1,000,000