Changing Formatting for a Cell or Range
In general, as noted previously, Excel stores exactly what you type in a cell. You have tremendous control over how that data appears, however. Number and date formats, for example, give you precise control over commas, decimal points, and whether months and days are spelled out or abbreviated. And if you can't find the precise format you're looking for, Excel lets you create your own custom format.
Setting Number Formats
How should Excel display the contents of a cell? You have dozens of choices, all neatly organized by category on the Number tab of the Format Cells dialog box. To specify exactly how you want the contents of a cell or range to appear, follow these steps:
Tip from 
Few keyboard shortcuts in all of Office are as useful as Ctrl+1, which opens Excel's Format Cells dialog box. When you're formatting a large or complex worksheet, this key combination can save a startling number of mouse clicks. Even if you generally don't use keyboard shortcuts, this one is worth memorizing. Note that you must use the number 1 on the top row of the keyboard; the 1 on the numeric keypad won't work.
In the Format Cells dialog box, choose an entry from the Category list on the left.
Tip from 
To quickly adjust the number of decimal points in a cell or range, make a selection and click the Increase Decimal or Decrease Decimal buttons on the Formatting toolbar. Each click adds or subtracts one decimal point from the selection.
Inspect the Sample box in the upper-right corner of the dialog box to see how the active cell will appear with the format settings you've selected. Click OK to accept the settings and return to the editing window.
The following number format categories are available:
General, the default format, displays numbers as entered, using as many decimal places as necessary, up to a maximum of 11 digits. It does not include separators between thousands. No additional options are available.
Number formats let you specify the number of decimal places, from 0 to 30 (the default is 2), as well as an optional separator for thousands, based on the Windows Regional Settings. You can also choose one of four formats for negative numbers (see Figure 22.2).
Figure 22.2.
Choices in the Currency category display values using the default currency symbol, as specified in the Regional Settings options of Control Panel. You can adjust the number of decimal places from its default of 2 to any number between 0 and 30 and select a format for negative values (see Figure 22.3).
Figure 22.3.
Accounting formats are similar to those in the Currency category, except that currency symbols and decimal points align properly in columns and you can't choose a format for negative values. With Accounting formats, the currency symbol ($ in U.S. English installations) sits at the left edge of the cell. This effect can be odd in wide columns that contain small numbers; in that case, choose a Currency format instead, if possible (see Figure 22.4).
Figure 22.4.
The Date category includes 15 formats that determine whether and how to display day, date, month, and year. Excel 2000 and 2002 include two Year-2000–compatible date formats that use four digits for the year (see Figure 22.5).
Figure 22.5.
The Time category includes eight formats that determine whether and how to display hours, minutes, seconds, and AM/PM designators (see Figure 22.6).
Figure 22.6.
Applying the Percentage format multiplies the cell value by 100 for display purposes and adds a percent symbol; the only option here lets you specify the number of decimal places, from 0 to 30 (the default is 2).
Fraction formats store numbers in decimal format but displays cell contents as fractions using any of 9 predefined settings; to display stock prices using 8ths, 16ths, and 32nds, click Up to Two Digits in the Type list (see Figure 22.7).
Figure 22.7.
Choose Scientific to display numbers in scientific notation; you select the number of decimal places, from 0 to 30.
Applying the Text format displays cell contents exactly as entered, even if the cell contains numbers or a formula.
The four choices in the Special category allow you to select formats for long and short U.S. ZIP codes, phone numbers, and Social Security numbers. You enter the number without any punctuation, and Excel adds hyphens and parentheses as necessary for display purposes only (see Figure 22.8).
Figure 22.8.
Choose the Custom option to define your own display rules. Start with a built-in format and use symbols in the formatting instructions; see "Custom Number Formats" later in this chapter for more details on custom number formats.
Setting Date and Time Formats
Normally, Excel stores exactly what you type into a cell. That's not the case when you type a recognizable date or time, however; when storing date and time information, Excel first converts the value you enter into serial date format. This numeric transformation explains how Excel can perform calculations using date and time information. Understanding the following facts is crucial to working effectively with serial date formats:
Excel converts the date to a whole number that counts the number of days that have elapsed since January 1, 1900. Thus, the serial date value of December 31, 2001 is 37256.
When you enter a time (hours, minutes, and seconds), Excel converts it to a fractional decimal value between 0 (midnight) and 0.999988 (11:59:59 PM). If you enter a time of 10:00 AM, for example, Excel stores it as 0.416667.
If you combine a date and time, Excel combines the serial date and time values. Thus, Excel saves December 31, 2001 10:00 AM as 37256.416667.
Note
When you enter only a date, Excel converts it to a serial value and uses 0 (or 12:00 AM) as the time value. If you enter only a time, Excel tacks on a date value of 1; if you later format this cell to show the date and time, Excel displays the nonsense date 1/0/1900.
The transformation to a serial value happens as soon as you enter a date or time value in a cell; at the same time, Excel automatically applies the default Date or Time format to your cell so that the data you enter displays correctly. You can choose a different Date or Time format to change the display format of date or time values. If you change the format of the cell to General or Number, however, you will see the serial values instead of the dates you expect.
Conversely, if you accidentally apply the Date format to a cell that contains a number, the result is likely to be nonsense, especially if the number is relatively low. Choose the General or Number format to display the cell's contents correctly.
Tip from 
If the display of dates is important to you, be aware of the unusual interaction between Excel's date and time formats and those you define in Windows'Regional Options Control Panel (in some Windows versions, this appears as Regional Settings). These linked formats appear at the top of the Date and Time lists in the Format Cells dialog box, with an asterisk in front of the format. When you change the date format in Windows, the format in your worksheet changes too—if you've used one of these formats.
Excel transforms dates and times to serial values so that you can use them in calculations. Because date and time values are stored as numbers, you can easily enter formulas that calculate elapsed time. If you include an employee's hire date as part of a list, for instance, you can use a simple formula to compare that value to today's date and determine whether the employee has qualified for participation in a profit-sharing or stock-option program. If you enter start and end times for each participant in a road race, you can easily calculate the total elapsed time and determine the top finishers.
After you enter the employee's start date in C1 and your report date in C2, for example, you can quickly calculate the difference between the two dates by using the formula =C1-C2.
Tip from 
Unfortunately, Excel outsmarts itself when you use this type of formula. Because it sees dates in both cells used in the formula, it automatically applies a date format to the cell containing the formula. As a result, the cell contents display as a nonsense date. Reset the cell's format to General or Number to correctly display the difference between the dates.
To use a date directly in a formula, enclose it in quotation marks first: =Today()-"1/1/2001" counts the number of days that have elapsed since January 1, 2001, for instance.s
Note
Excel's Options dialog box includes a setting for the 1904 date system. This obscure option is necessary only when exchanging files with users of old versions of Excel for the Macintosh, which started the calendar at the beginning of 1904 rather than 1900. Recent Mac Excel versions, including Excel 98, handle this conversion seamlessly. Under normal circumstances, this option should not be necessary.
Excel and Year 2000 Issues
The much-feared global Y2K crisis never happened. Planes continued to fly, power stations hummed along, and banks didn't run out of money. Yes, the world successfully entered the new Millennium, but that doesn't let you off the hook when it comes to Year 2000 (Y2K) issues. Excel's default settings correctly handle most formulas that include dates from different centuries. But a few "gotchas" linger for the unwary:
When you enter a date before January 1, 1900 in an Excel worksheet, the date appears as text. As far as Excel is concerned, dates before the 20th Century simply don't exist—that's bad news for historians hoping to use Excel to plot dates that go back more than a century.
On the other hand, dates after December 31, 1999 don't represent a problem. In fact, Excel worksheets will accept any date through December 31, 9999 (that's a serial date value of 2958465, if you want to try it for yourself).
Tip from 
If you need to track timelines and perform calculations for dates before the beginning of 1900 (to chart long-term records of earthquake activities, for example), don't use Excel. Instead, fire up Access, which can correctly handle dates as early as January 1, 100 (Common Era). If you're a student of ancient history, you'll need to use another program—or perhaps you can make do with clay tablets.
Because Excel stores dates as serial values, it is unaffected by most garden-variety Y2K problems. In practice, however, you might encounter Y2K problems if you enter or import data that includes only two digits for the year. When Excel encounters dates in this format, it has to convert the year to four digits; in the process, it's possible to select the wrong century. When translating two-digit years, Excel uses the following rules:
Excel automatically converts dates entered using the two-digit years 00 through 29 to the years 2000 through 2029. Thus, if you enter or import the value 5/23/02, Excel stores it as serial value 37399, or May 23, 2002.
When you enter the two-digit years 30 through 99 as part of a date, Excel converts the dates using the years 1930 through 1999. Thus, when you enter or import the value 9/29/55, Excel stores it as serial value 20361, or September 29, 1955.
If you're using a display format that shows only two years, you might not realize that Excel has stored the wrong data, but any calculations you make might be off by a full century. To avoid inadvertently entering or importing incorrect data, get in the habit of entering all dates using four-digit formats for the year: 5/23/2002. Excel stores this date correctly regardless of the Date format you've chosen for display purposes.
When importing data that includes dates with two-digit years, check the format of the original data carefully. You might need to manually edit some dates after importing. Pay special attention to worksheets that were originally created using older versions of Excel for Windows or the Macintosh, because the algorithms those programs use to convert two-digit years are different from those in Excel 2000 and 2002.
The automatic date conversion routine is a clever workaround, but don't rely on it. Entering or importing two-digit years is guaranteed to cause problems in the following circumstances:
In the banking industry, in which dates beyond 2029 are common in 30-year mortgages that begin in the year 2000 or later. If you enter the start date as 2/1/00 and the end date as 2/1/30, your loan will start out 70 years overdue.
In any group that includes milestone dates—birthdays, graduation dates, and so on—for an older population. If you enter a birthdate of 6/19/27, your worksheet might assume that the person in question isn't born yet.
Tip from 
This can't be said strongly enough or repeated too often: Get in the habit of using four-digit years whenever you enter or display a date in a worksheet.
Creating Custom Cell Formats
If the exact number format you need isn't in Excel's collection of built-in formats, create a custom format. Custom formats let you specify the display of positive and negative numbers as well as zero values; you can also add text to the contents of any cell.
Tip from 
Excel saves custom number formats in the workbook in which you create them. To reuse formats, add them to the template on which you base new workbooks. To copy cell formats from one workbook to another, copy the cell that contains the custom format, click in the workbook where you want to add the format, and choose Edit, Paste Special, Formats.
The list of 35 custom formats in the Type box includes some that are already available within other categories, as well as a few you won't find elsewhere. It's almost always easier to design a custom format if you start with one that already exists. To create a custom number format, open the Format Cells dialog box and choose the format you want to start with. Then click Custom at the bottom of the Category list. Excel displays the codes for the format you just selected in the Type box, ready for you to modify. The example shown in Figure 22.9, for example, shows the results when we chose a Currency format and changed the symbol from the U.S. dollar sign to the Euro. Although the switches for these codes are undocumented, this technique adds them to the Type box, making it easy to define a new format that uses this symbol correctly.
Figure 22.9. Enter custom format codes here. Note the Sample area, which shows how the contents of the active cell will appear.
Custom formats use format codes to tell Excel how to display digits, decimal places, dates and times, and other details. Each custom format can include up to four sections, separated by semicolons, as shown in the example in Figure 22.10. Using all four sections defines display formats for positive numbers, negative numbers, zero values, and text, respectively. If you enter only two sections, Excel uses the first set of instructions for positive numbers and zero values and the second for negative numbers. If you enter only one section, that format will apply to all numbers you enter. You don't need to enter a format for each section, but if you plan to skip a format option (specifying formats only for positive numbers and zero values, for example), insert a semicolon for each section you skip.
Figure 22.10. Custom number formats can contain up to four sections.
Tip from 
When creating an extremely complex custom format, working with the narrow text box in the Format Cells dialog box can be difficult. To make life easier, select the contents of this box, and then copy them to a friendlier editor, such as Notepad or Word. Edit the format codes, and then use the Clipboard to paste the results back into the dialog box.
Creative use of custom number formats can help you deal with tricky data-entry challenges. For example, how do you make it easy to enter a serial number with leading zeros? Say your company uses invoice or part numbers that must be exactly seven digits, with no exceptions. If the number you enter includes fewer than seven digits, you want Excel to pad the beginning of the entry with as many zeros as it takes to reach that magic number. Entering a number like 0001234 won't work, because Excel considers the leading zeros insignificant digits and strips them before storing the value in the cell.
The solution is to create a custom format that includes a zero for each digit you want to include in the displayed result—in this case, 0000000.
Tip from 
To guarantee that only correct data appears in the cell, combine this custom format with a data-validation rule, as described in Chapter 21, "Restricting and Validating Data Entry for a Cell or Range." If your company policy says the number must be larger than 1000, create a validation rule that restricts data entry to whole numbers (to prevent stray decimal points or text from messing up the list) between 1001 and 9999999. The all-zeros display format guarantees that any data within this range will display as exactly seven digits, with leading zeros if necessary.
Custom Number Formats
Custom number formats let you round or truncate numbers, control the number of decimal places or significant digits, and make sure amounts line up properly in columns. Use the codes shown in Table 22.1 to define the display format.
Custom Date and Time Formats
Excel's selection of ready-made date and time formats is extensive, but there are several situations in which you might want to create your own. For example, if your company uses a special date format to identify dates on an invoice, you can enter a format such as yyyymmdd to display a date as 19990321.
Custom date and time formats are also useful on billing worksheets for professionals who charge by the minute or hour, or if you've captured data from time sheets filled out by hourly workers. Table 22.2 includes examples of date and time codes you can add to custom formats.
Adding Text to a Cell
To display text in a cell that contains numbers, Excel includes a selection of special format codes. Use this type of format to add a word like "shortage" or "deficit" after a negative number, for example. Because the format doesn't change the contents of the cell, the number you entered will still work in formulas that reference that cell.
You can add the space character, left and right single quotation mark, and any of the following special characters without enclosing them in double quotation marks:
$ - + / ( ) : ! [ct] & ~ { } = < >
To add other text to a cell, use the codes in Table 22.3.
Tip from 
When creating a custom number format, first click in a cell that contains data you want to see in the new format. As you edit the custom format, the Preview region of the Custom Format dialog box shows you how the active cell's contents will appear in the new format.
Adding Conditions to a Display Format
You can also use conditions as part of custom number formats. Conditions use comparison operators and are contained in brackets as part of a format definition. Look at the built-in Phone Number format (in the Special category) to see how this option works:
[<=9999999]###-####;(###) ###-####
If you enter a number of seven or fewer digits in a cell that uses this format, Excel treats it as a local phone number and adds a hyphen where the prefix appears. If you enter a number greater than seven digits, Excel uses the second part of the format, displaying the last seven digits as a phone number and any number of digits prior to that number as an area code in parentheses.
The results of this format can be absurd if you enter a number that's smaller than 7 digits or larger than 10 digits. Here's how to use conditions to customize this format. The example shown here assumes you work in the 212 area code and want to add that code to the beginning of any 7-digit (local) number; if the number uses more than 10 digits, the default condition at the end kicks in, adding the international dialing prefix (+011) and splitting the digits before the number into country and city codes.
[<=9999999](212) ###-####;[<=9999999999](###) ###-####;"+011 "(#-##) ###-####
Tip from 
Don't confuse these custom formats with conditional formatting, which is described later in this chapter. If you want to change the font or color of text based on values displayed in the cell, use the Conditional Formatting option on the Format menu (described later in this chapter). The conditional display formats shown here are most useful when you want to subdivide a number with punctuation marks or change the number of digits displayed. You can effectively combine this type of format with conditional formatting—for example, if the user enters a phone number with six or fewer digits, you might display it in red to help it stand out as a possibly invalid number.