Page 120 - Excel Timesaving Techniques for Dummies

23_574272 ch20.qxd 10/1/04 10:43 PM Page 105
5. Position the Insertion point at the very begin- Creating Custom Number Formats 105
ning of the Type list box and then type #,##.
The Type text box now contains the code #,###
??/00, and the sample reads 1,234 56/100. Now,
all you have to do is add the stock text and
between the whole and fractional digits and the
dollars text at the end.
6. Position the Insertion point after the last pound
sign (#) and then type “ (open quote), press the
spacebar, type and, and then type “ (close
quote).
The Type text box now contains the code #,###”
and”??/00, and the sample reads 1,234 and
56/100.
7. Position the Insertion point at the very end of
the code (after the last 0) and type “ (open
quote), press the spacebar to insert a space,
type dollars, and then type “ (close quote).
• Figure 20-2: The Number tab of the Format Cells dialog
The Type text box now contains the code #,###” box showing the custom number format
and”??/00” dollars”, and the sample reads codes.
1,234 and 56/100 dollars, as shown in
Figure 20-2. 8. Click OK to close the Format Cells dialog box
and apply the new custom format to the cur-
rent cell in the worksheet.

When you close the Format Cells dialog box, Excel
applies the custom format to the 1234.56 sample
number in the current cell. To apply this custom for-
mat to other cells in the worksheet, you need to
select the cells, open the Number tab of the Format
Cells dialog box, select Custom in the Category list
box, select the #,###” and”??/00” dollars” code at
the very bottom of the Type list box, and click OK.

Custom formats that conditionally format entries

You can use Excel’s number format codes to create
custom number formats that do conditional format-
ting (much like the conditional formatting feature
discussed in Technique 19). To create a conditional
format, you set up the condition inside square brack-
ets using the same comparative operators (=, >, <,
• Figure 20-1: Selecting the Fraction category to apply a
>=, <=, and <>) as you do in simple formulas.
close format to the base number.