Microsoft Office Tutorials and References
In Depth Information
Tip 17: Using Custom Number Formats to Scale Values
Using Custom Number Formats
to Scale Values
If you work with large numbers, you may prefer to display them scaled to thousands or millions
rather than display the entire number. For example, you may want to display a number like
132,432,145 in millions: 132.4.
The way to display scaled numbers is to use a custom number format. The actual unscaled number,
of course, will be used in calculations that involve that cell. The formatting affects only how the
number is displayed. To enter a custom number format, press Ctrl+1 to display the Format Cells dialog
box. Then click the Number tab and select the Custom category. Put your custom number format in
the Type field.
Table 17-1 shows examples of number formats that scale values in millions.
Table 17-1: 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
–5000000
#,###,,”M”
–5M
123456789
#,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_)
123.5M
1000000
#,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_)
1.0M
–5000000
#,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_)
(5.0M)
0
#,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_)
0.0M
Search JabSto ::




Custom Search