Page 199 - Excel Progamming Weekend Crash Course

k540629 ch14.qxd 9/2/03 9:34 AM Page 174

174 Saturday Afternoon

Table 14-1 Elements of Number Formatting Codes

Character Meaning Code example Format example
# Significant digit ##.# 12.67 displays as 12.7
0 Nonsignificant 0 #.000 1.2 displays as 1.200
. Decimal point ##.## 12.34 displays as 12.34
$ Currency symbol $#.## 21.98 displays as $21.98

% Percent symbol #.#% 0.05 displays as 5.0%
, Thousands separator #,### 98000 displays as 98,000

You can also display positive and negative numbers differently. In accounting, for exam-
ple, negative numbers may be displayed in red and/or enclosed in parentheses. To do this,
include two format specifiers in the format code separated by a semicolon. The first format
applies to positive numbers, the second to negative numbers. For example:
$#.##;($#.##)

This format will display positive numbers with a dollar sign and two decimal places.
Negative numbers will be formatted the same but will also be enclosed in parentheses.
To specify a display color, include the color name enclosed in square brackets at the start
of the format code. The available color names are:

Black
Cyan
Magenta
White
Blue
Green
Red
Yellow

The following code, for example, displays positive numbers in the default font color of
the cell and negative numbers in red.
$#.##;[Red]$#.##

A display color specified in this way overrides the font color that is set for the cell (see
the next section on font formatting).

For more details on Excel’s formatting codes, refer to the online help.

Cross-Ref