Page 121 - Excel Timesaving Techniques for Dummies

23_574272 ch20.qxd 10/1/04 10:43 PM Page 106
106
Technique 20: Customizing Number Formats
For example, you can create a conditional custom zeros, and text) that determine how specific types of
format that displays a cent sign (¢) after all values entries are formatted, you can easily create custom
that are less than a dollar and displays a normal dol- formats that hide a particular type of entry by leaving
lar sign and two decimal places for all values over or its section blank.
equal to one dollar. To do so, open the Number tab
of the Format Cells dialog box, select the Custom For example, to create a number format that hides
number category on the Number tab, and enter the the contents of a cell only when it contains text,
following code in the Type text box: open the Format Cells dialog box, select the Custom
number category on the Number tab, and enter the
[<1].00¢;$0.00 following number codes in the Type text box:
In this conditional format, when the number in the cell _(* #,##0.00_);_(* (#,##0.00);0;
is less than 1, the program adds the cent sign (¢) to
the decimal number showing two places. Otherwise, In this custom number format, Excel applies the
when the number is 1 or any number greater, the standard Comma style formatting to positive and
program uses the second part of the conditional for- negative numbers, while at the same time displays 0
mat (after the semicolon), which adds the dollar sign for cells that contain zero. However, because I failed
($) and two decimal places to the number. to add a number format code in the fourth section
(after the third semicolon), Excel hides any text
When creating conditional custom formats, you’re not entry.
limited to two formats: the one used when the condi-
tion is true and the other when it’s false. You can, in To create a custom format that does just the oppo-
fact, create up to three formats: one format used when site — that is, hides all numbers and displays only
the first condition is true, a second format when the text — you simply enter two semicolons with no
second is true, and a third format for all other cases. number format codes whatsoever:
;;
For example, say that you want to create a condi-
tional format that applies the General number format
In this custom number format, the two semicolons
using a red font when a number is less than 1, blue
delineate three of the possible four sections: the pos-
when its value is greater than 100, and default black
itive number before the first semicolon, the negative
when its value is anywhere in between. To do so,
number immediately following the first semicolon,
open the Format Cells dialog box, select the Custom
and the zero section of the format immediately fol-
number category on the Number tab, and enter the
lowing the second semicolon. However, because all
following number codes in the Type text box:
three sections are empty, Excel hides the display of
[RED][<1]General;[BLUE][>100]General;General positive, negative, and zero values, displaying only
text entries. If you want to create a custom format
In this conditional custom number format, the font that hides the display of all entries in a cell, just add
color red is assigned to any values below 1, and blue the third semicolon to these other two with no codes
is assigned to any values higher than 100. The default, (and no spaces) as in:
black color is assigned to all other values (that is, any
;;;
number between 1 and 100).
Because all four sections of the number format are
Custom formats that hide certain entries now delineated and empty, Excel suppresses the dis-
play of all types of entries in the cells to which this
Because Excel number formats consist of four sections
custom number format is applied.
(one each for positive numbers, negative numbers,