Microsoft Office Tutorials and References
In Depth Information
Parts of a number format string
Parts of a number format string
A custom format string enables you to specify different format codes for four categories of values:
positive numbers, negative numbers, zero values, and text. You do so by separating the codes for
each category with a semicolon. The codes are arranged in four sections, separated by semicolons:
Positive format; Negative format; Zero format; Text format
The following general guidelines determine how many of these four sections you need to specify:
➤ If your custom format string uses only one section, the format string applies to all values.
➤ 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 example of a custom number format specifies a different format for each of these
This 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. By the way, using the Excel conditional formatting feature is a much
better way to apply color to cells based on their content.
When you create a custom number format, don’t overlook the Sample box in the
Number tab of the Format Cells dialog box. This box displays the value in the active cell
by using the format string in the Type field. Be sure to test your custom number formats
by using the following data: a positive value, a negative value, a zero value, and text.
Often, creating a custom number format takes several attempts. Each time you edit a
format string, it’s added to the list. When you finally get the correct format string, open
the Format Cells dialog box one more time and delete your previous attempts.
Custom number format codes
Table 16-1 briefly describes the formatting codes available for custom formats.
Search JabSto ::

Custom Search