Microsoft Office Tutorials and References
In Depth Information
The following are guidelines for using the Special formats:
Zip Code and Zip Code +4 Leading zeros are retained to correctly display the
code, as in 04321. In most other number formats, if you type 04321 , Excel drops the
zero and displays 4321.
Phone Number Excel applies parentheses around the area code and dashes
between the digits, making it much easier to type many numbers at the same time
because you don’t have to move your hand from the keypad. Furthermore, the
numbers you type remain numbers instead of becoming text entries, which they would be
if you typed parentheses or dashes in the cell.
Social Security Number Excel places dashes after the third and fifth numbers. For
example, if you type 123456789 , Excel displays 123-45-6789.
Locale This drop-down list lets you select from more than 120 locations with
unique formats. For example, if you select Vietnamese, only two Special formats are
available: Metro Phone Number and Suburb Phone Number.
Creating Custom number formats
Most of the number formats you need are available through commands and buttons on
the ribbon, but you can use the Format Cells dialog box to accomplish minor feats of
formatting that might surprise you. We’ll use the Custom category on the Number tab in the
Format Cells dialog box, shown in Figure 9-41, to create custom number formats using
special formatting codes. (To quickly display the Format Cells dialog box, press Ctrl+1.) Excel
adds new formats to the bottom of the list of formatting codes in the Type list, which also
includes built-in formats. To delete a custom format, select the format in the Format Cells
dialog box, and click Delete. You cannot delete built-in formats.
Creating new number formats The quickest way to start creating a custom format is to
use one of the built-in formats as a starting point. Here’s an easy way to build on an
existing format, as well as to see what the codes in the Type list mean:
1. Type a number (or, in the case of our example, a date), and apply the built-in format
that most closely resembles the custom format you want to create. Leave this cell
On the Number tab in the Format Cells dialog box, select the Custom category. The
format you selected is highlighted in the Type list, representing the code equivalent
of the format you want to modify, as shown in Figure 9-41.