Excel 2010 Bible

Part IV: Using Advanced Excel Features

560

Custom Number Format Examples

The remainder of this chapter consists of useful examples of custom number formats. You can use

most of these format codes as-is. Others may require slight modification to meet your needs.

Scaling values

You can use a custom number format to scale a number. For example, if you work with very large

numbers, you may want to display the numbers in thousands (that is, display 1,200,000 as 1,200).

The actual number, of course, will be used in calculations that involve that cell. The formatting

affects only how it is displayed.

Displaying values in thousands

The following format string displays values without the last three digits to the left of the decimal

place and no decimal places. In other words, the value appears as if it’s divided by 1,000 and

rounded to no decimal places.

#,###,

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

by 1,000 and rounded to two decimal places.

#,###.00,

Table 24.5 shows examples of these number formats:

Excel may create custom number formats without you realizing it. When you use the Increase Decimal

or Decrease Decimal button on the Home ➪ Number group of the Ribbon (or on the Mini toolbar),

Excel creates new custom number formats, which appear on the Number tab in the Format Cells dialog

box. For example, if you click the Increase Decimal button five times, the following custom number

formats are created:

0.0

0.000

0.0000

0.000000

A format string for two decimal places is not created because that format string is built-in.

Where Did Those Number Formats Come From?