Microsoft Office Tutorials and References

In Depth Information

**Creating Custom Cell Formats**

the first format code in the section. Thus, [Blue]#,##0;[Red]#,##0;[Black]0 displays positive

values as blue, negative values as red, and zero values as black.

The remaining codes available for custom formats divide neatly into four categories.

Number Format Codes

0
Digit placeholder. Displays zero if the number has fewer digits than placeholders. The

format 0.00 displays 2.30 if you enter 2.3. For numbers that are less than 1, it includes

a 0 to the left of the decimal point.

#
Significant digit placeholder. Using the format #.## displays all significant (nonzero)

digits to the left of the decimal point and rounds to two digits on the right of the

decimal point. Thus, if you enter 0.42, this format displays .42 in the cell, with nothing to

the left of the decimal point.

?
Align
decimals. This digit placeholder works like the # placeholder but aligns decimal

points.

Two punctuation marks are useful in custom number formats. Use a period (.) to indicate

the position of the decimal point. The comma (,) has two roles. Used with placeholders on

either side, it displays the thousands separator: #,###. Adding one or more commas after a

custom format scales the number by 1,000 for each comma; to round large numbers (like

42,420,000) by a factor of 1 million, enter this format:

#0.0,," million"

As we explain a bit later, the text in quotation marks appears alongside the result. Thus,

this format displays
42.4 million
in the cell, but the value is still stored as a number for

calculation.

Date/Time Format Codes

A custom date or time format allows you to create special date formats that aren’t included

in any of Excel’s ready-made selections. You can combine years, months, and days in any

order and keep track of elapsed time as well for recording timesheets in Excel format.

d, dd, m, mm
Day or month in numeric format. The two-digit varieties add leading zeroes

(mm/dd/yy looks like 05/01/11).

ddd, mmm, dddd, mmmm
Day or month in text format. Use ddd or mmm for

abbreviations such as Mon or Sep; use dddd and mmmm for the fully spelled out day of the

week or month.