Creating a Custom Number Format

The Custom category on the Number tab of the Format Cells dialog box (see Figure B-1) enables you to create number formats not included in any of the other categories. Excel gives you a great deal of flexibility in creating custom number formats.

Figure B-1: The Number tab of the Format Cells dialog box.

Tip 

Custom number formats are stored with the workbook in which they are defined. To make the custom format available in a different workbook, you can just copy a cell that uses the custom format to the other workbook.

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 decimal 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 displayed 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 of 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. The file is named number formats.xlsx.

Parts of a Number Format String

A custom format string can have up to four sections, which enables you to specify different format codes for positive numbers, negative numbers, zero values, and text. You do so by separating the codes with a semicolon. The codes are arranged in the following order:

 Positive format; Negative format; Zero format; Text format 

If you don't use all four sections of a format string, Excel interprets the format string as follows:

• If you use only one section: The format string applies to all types of entries.

• If you use two sections: The first section applies to positive values and zeros, and the second section applies to negative values.

• If you use three sections: The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros.

• If you use all four sections: The last section applies to text stored in the cell.

The following is an example of a custom number format that specifies a different format for each of these types:

 [Green]General;[Red]General;[Black]General;[Blue]General 

This custom number format example takes advantage of the fact that colors have special codes. A cell formatted with this custom number format displays its contents in a different color, depending on the value. When a cell is formatted with this custom number format, a positive number is green, a negative number is red, a zero is black, and text is blue.

Cross Ref 

If you want to apply cell formatting automatically (such as text or background color) based on the cell's contents, a much better solution is to use Excel's Conditional Formatting feature. Chapter 19 covers conditional formatting.

Pre-formatting Cells

Usually, you'll apply number formats to cells that already contain values. You also can format cells with a specific number format before you make an entry. Then, when you enter information, it takes on the format that you specified. You can pre-format specific cells, entire rows or columns, or even the entire worksheet. Rather than pre-format an entire worksheet, however, you can change the number format for the Normal style. (Unless you specify otherwise, all cells use the Normal style.) Change the Normal style by displaying the Style gallery (choose Home Styles). Right-click the Normal style icon and then 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.

Custom Number Format Codes

Table B-3 lists the formatting codes available for custom formats, along with brief descriptions.

Table B-3: CODES USED TO CREATE CUSTOM NUMBER FORMATS
Open table as spreadsheet

Code	Comments
General	Displays the number in General format.
#	Digit placeholder. Displays only significant digits, and does not display insignificant zeros.
0 (zero)	Digit placeholder. Displays insignificant zeros if a number has fewer digits than there are zeros in the format.
?	Digit placeholder. Adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font. You can also use ? for fractions that have varying numbers of digits.
.	Decimal point. % Percentage.
,	Thousands separator.
E- E+ e- e+	Scientific notation
$ - + / ( ) : space	Displays this character.
\	Displays the next character in the format.
*	Repeats the next character, to fill the column width.
_ (underscore)	Leaves a space equal to the width of the next character.
"text"	Displays the text inside the double quotation marks.
@	Text placeholder.
[color]	Displays the characters in the color specified. Can be any of the following text strings (not case sensitive): Black, Blue, Cyan, Green, Magenta, Red, White, or Yellow.
[Color n]	Displays the corresponding color in the color palette, where n is a number from 0 to 56.
[condition value]	Enables you to set your own criterion for each section of a number format.

Table B-4 lists the codes used to create custom formats for dates and times.

Where Did Those Number Formats Come From?

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 in the Mini Toolbar), Excel creates new custom number formats, which appear on the Number tab of 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.

Table B-4: CODES USED IN CREATING CUSTOM FORMATS FOR DATES AND TIMES
Open table as spreadsheet

Code	Comments
m	Displays the month as a number without leading zeros (1–12).
mm	Displays the month as a number with leading zeros (01–12).
mmm	Displays the month as an abbreviation (Jan–Dec).
mmmm	Displays the month as a full name (January–December).
mmmmm	Displays the first letter of the month (J–D).
d	Displays the day as a number without leading zeros (1–31).
dd	Displays the day as a number with leading zeros (01–31).
ddd	Displays the day as an abbreviation (Sun–Sat).
dddd	Displays the day as a full name (Sunday–Saturday).
yy or yyyy	Displays the year as a two-digit number (00–99) or as a four-digit number (1900–9999).
h or hh	Displays the hour as a number without leading zeros (0–23) or as a number with leading zeros (00–23).
m or mm	Displays the minute as a number without leading zeros (0–59) or as a number with leading zeros (00–59).
s or ss	Displays the second as a number without leading zeros (0–59) or as a number with leading zeros (00–59).
[ ]	Displays hours greater than 24 or minutes or seconds greater than 60.
AM/PM	Displays the hour using a 12-hour clock. If no AM/PM indicator is used, the hour uses a 24-hour clock.