Microsoft Office Tutorials and References
In Depth Information
Special Formats for Special Numbers
Formatting Cell
The second group (the formats that are independent of a computer’s regional
settings) is much more extensive. In order to choose one of these formats, you first
select a region from the Locale list, and then you select the appropriate date or
time format (that isn’t preceded by an asterisk). Some examples of locales include
“English (U.S.)” and “English (U.K.).”
If you enter a date without specifically formatting the cell, Excel usually uses the
short region-specific date format. That means that the order of the month and year
vary depending on the regional settings of the current computer. If you incorporate
the month name (for example, January 1, 2010), instead of the month number (for
example, 1/1/2010), Excel uses a medium date format that includes a month
abbreviation, like 1-Jan-2010.
Note: You may remember from Chapter 14 that Excel stores a date internally as the cumulative number of
days that have elapsed since a certain long-ago date. You can take a peek at this internal number using the
Format Cells dialog box. First, enter your date. Then, format the cell using one of the number formats (like
General or Number). The underlying date number appears in your worksheet where the date used to be.
Special Formats for Special Numbers
You wouldn’t ever want to perform mathematical operations with some types of
numeric information. For example, it’s hard to image a situation where you’d want to
add or multiply phone numbers or Social Security numbers.
When entering these types of numbers, therefore, you may choose to format them
as plain old text. For example, you could enter the text (555) 123-4567 to represent
a phone number. Because of the parentheses and the dash (-), Excel won’t interpret
this information as a number. Alternatively, you could just precede your value with
an apostrophe (‘) to explicitly tell Excel that it should be treated as text (you might
do this if you don’t use parentheses or dashes in a phone number).
But whichever solution you choose, you’re potentially creating more work for
yourself because you must enter the parentheses and the dash for each phone number
you enter (or the apostrophe). You also increase the likelihood of creating
inconsistently formatted numbers, especially if you’re entering a long list of them. For
example, some phone numbers may end up entered in slightly similar but somewhat
different formats, like 555-123-4567 and (555)1234567.
To avoid these problems, apply Excel’s Special number format (shown in Figure
16-8), which converts numbers into common patterns. And lucky you: In the
Special number format, one of the Type options is Phone Number (other formats are
for Zip codes and Social Security numbers).
Search JabSto ::

Custom Search