Page 119 - Excel Timesaving Techniques for Dummies

23_574272 ch20.qxd 10/1/04 10:43 PM Page 104
104
Technique 20: Customizing Number Formats
TABLE 20-1 (continued)
Code What It Signifies
h Inserts the number of the hour without leading zeros, as in 9:15.
hh Inserts the number of the hour with leading zeros, as in 09:15.
m When immediately following the h or hh code, inserts the number of minutes without leading zeros (0-59),
as in 5:5.
mm Inserts the number of the minutes with leading zeros (00-59), as in 5:05.
s Inserts the number of seconds without leading zeros (0-59), as in 5:05:7.
ss Inserts the number of seconds with leading zeros (00-59), as in 5:05:07. For fractions of a second, use a
format that includes fractions such as h:mm:ss.00.
[ ] Displays hours greater than 24 and minutes or seconds greater than 60.
AM/PM Inserts AM or PM after time numbers depending upon whether the time is before or after noon, as in 10:15
PM. (Otherwise Excel formats the time using a 24-hour clock as in 22:15.)
[Color] Specifies the font color for the section of the format as specified by the color name, as in [BLACK], [BLUE],
[CYAN], [GREEN], [MAGENTA], [RED], [WHITE], or [YELLOW]. Note that [Color] must precede the other
codes in the section, as in [RED](#,##0) in the negative section, to have negative values displayed in red
and enclosed in parentheses.
[Colorn] Specifies a font color for the section of the format as specified by a number (n) between 1 and 56. Note that
this [Colorn] code must precede all other code in the section.

When defining custom formats for currency, you enter To get an idea of how you create your own custom
the currency signifier, as in $ for dollars, £ (Alt+0163) number format, follow these steps for building a
for pounds, ¥ (Alt+0165) for yen, and € (Alt+0128) format that displays your financial values in the
for euros. Use a comma (,) to indicate the presence of spreadsheet like you do when writing a check, as
the thousands separator and a period (.) to indicate in 1,234 and 56/100 dollars:
the decimal point. When defining custom date for-
mats, you can separate the parts of the date with 1. Enter 1234.56 in a blank cell of the current
either a hyphen (-) or a slash (/). When defining time worksheet to create a sample amount and make
formats, you separate each part with a colon (:). sure that this cell is current.
2. Choose Format➪Cells (Ctrl+1) to open the
To find out the keystroke numeric code that Format Cells dialog box, shown in Figure 20-1.
inserts special characters such as the cent
symbol or degree symbol into a custom 3. On the Number tab, select Fraction in the
format, open the Character Map program Category list box and then scroll down the
(Start➪All Programs➪Accessories➪System Type list and select As Hundredths (30/100).
Tools➪Character Map) and then select the
characters. Windows displays the Alt+key- The Sample area now shows the number in the
stroke combination, if one exists, in the lower current cell as 1234 56/100.
right of the Character Map dialog box. To use 4. Select Custom in the Category list box.
this numeric code, enter the code from the
numeric keypad while holding down the The Type text box now contains the code #
Alt key. ??/00, your starting point for creating your
custom format.