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
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:
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
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.