Excel 2010 Bible

Part IV: Using Advanced Excel Features

556

You construct a number format by specifying a series of codes as a number format string. You enter

this code sequence in the Type field after you select the Custom category on the Number tab of the

Format Cells dialog box. Here’s an example of a simple number format code:

0.000

This code consists of placeholders and a decimal point; it tells Excel to display the value with three

digits to the right of the decimal place. Here’s another example:

00000

This custom number format has five placeholders and displays the value with five digits (no deci-

mal point). This format is good to use when the cell holds a five-digit zip code. (In fact, this is the

code actually used by the Zip Code format in the Special category.) When you format the cell with

this number format and then enter a Zip Code, such as 06604 (Bridgeport, CT), the value is dis-

played with the leading zero. If you enter this number into a cell with the General number format,

it displays 6604 (no leading zero).

Scroll through the list of number formats in the Custom category in the Format Cells dialog box to

see many more examples. In many cases, you can use one of these codes as a starting point, and

you’ll need to customize it only slightly.

On the CD
The companion CD-ROM contains a workbook with many custom number format examples (see Figure 24.2).
The file is named number formats.xlsx.

As I mention earlier, the default number format is General. If you prefer a different default number for-

mat, you have two choices: Preformat the cells with the number format of your choice, or change the

number format for the Normal style.

You can preformat specific cells, entire rows or columns, or even the entire worksheet.

Rather than preformat an entire worksheet, however, a better solution is to change the number format

for the Normal style. Unless you specify otherwise, all cells use the Normal style. Therefore, by chang-

ing the number format for the Normal style you are essentially creating a new default number format for

the workbook.

Change the Normal style by displaying the Style gallery. Right-click the Normal style icon (in the Home ➪

Styles group) and choose Modify to display the Style dialog box. In the Style dialog box, click the

Format button and then choose the new number format that you want to use for the Normal style.

Changing the Default Number Format

for a Workbook