How Cell Formatting Works
In an Excel worksheet, what you see in a cell is not necessarily what's stored in that cell. If you enter a formula, for example, Excel stores the formula but displays its result. When entering numbers, dates, and text, you can go as quickly as you want, without too much regard for how they'll look in your worksheet; afterwards, use cell formatting instructions to specify how you want the cells'contents to display, including such details as decimal places, currency symbols, and how many digits to use for the year. Other cell formatting options let you adjust fonts, colors, borders, and other attributes of a cell or range.
A handful of buttons on the Formatting toolbar let you bypass dialog boxes for some common tasks, such as choosing a font or changing a range of cells to bold. If you're building a financial worksheet, click the Currency button to ensure that every number in a given range lines up properly and includes the correct currency symbol. To see the full assortment of Excel formatting options, select a range and choose Format, Cells, or right-click a cell or selection and choose Format Cells from the shortcut menu. All available cell formatting options are arranged on six tabs in the Format Cells dialog box.
Using the General Number Format
On a new worksheet, every cell starts out using the General format. When the cell contains a constant value, Excel usually displays the exact text or numbers you entered; in cells that contain a formula, the General format displays the results of the formula using up to 11 digits—the decimal point counts as a digit. (Date and time values follow a special set of rules, as you'll see shortly.) If the cell is not wide enough to show the entire number, Excel rounds the portion of the number to the right of the decimal point, for display purposes only; if the portion of the number to the left of the decimal point won't fit in the cell or contains more than 11 digits, the General format displays the number in scientific notation.
To remove all number formats you've applied manually and restore a cell to its default General format, right-click and choose Format Cells, and then click the Number tab and choose General from the Category list. Although it's not particularly intuitive, there's also a keyboard shortcut that applies the General format instantly to the active cell or current selection: Press Ctrl+Shift+~ (tilde) to reset cells to General format.
Controlling Automatic Number Formats
When you enter data in a format that resembles one of Excel's built-in formats, Excel automatically applies formatting to the cell. In some cases, the results might be unexpected or unwelcome:
If you enter a number that contains a slash (/) or hyphen (-) and matches any of Windows'date and time formats, Excel converts the entry to a date serial value and formats the cell using the closest matching Date format. If the date you enter includes only the month and date, Excel adds the current year.
→ In some cases, Excel 2002 picks up formatting from your Windows version; for details of how this interaction works, see "Setting Date and Time Formats".
If you import data into a worksheet, Excel might convert values that look like dates or times. For suggestions on how to prevent this from occurring, see "Stopping Automatic Conversions" in the "Troubleshooting" section at the end of this chapter.
If you enter a number preceded by a dollar sign, Excel applies the Currency style, with two decimal places, regardless of how many decimal places you entered. (If you've used the Regional Settings option in Control Panel to specify a different currency symbol, Excel applies the Currency style when you enter data using that symbol.)
Note
As explained later in this chapter, the Currency style is actually a variation of the Accounting format.
If you enter a number that begins or ends with a percent sign, Excel applies the Percent style with up to two decimal places.
Tip from 
Excel supports fraction formats as well, but entering data in this format is tricky. If you enter 3/8, for example, Excel interprets your entry as a date—March 8—and formats it accordingly. To enter a fraction that Excel can recognize automatically, start with 0 and a space: 0 3/8. Excel correctly enters that number as 0.375 and changes the cell format to Fraction. Although Excel stores the number as 0.375, it is displayed as 3/8.
Tip from 
Excel also supports compound fractions—fractions that include a whole number and a fractional number, such as 12 1/8. Enter the whole number part (in this case, 12) followed by a space and then the fraction part. Excel displays the entry as 12 1/8 but stores it as 12.125. You'll find this technique invaluable if you ever have to perform calculations involving stock market prices; although more and more markets are moving to decimal pricing, some exchanges still use archaic fractional pricing—16ths, 32nds, even 64ths of a dollar!
When you enter a number that contains a colon (:), or if the number is followed by a space and the letter A or P, Excel converts it to a time format.
If the number you enter contains leading zeros (as in part numbers, for example, which might need to fill a precise number of characters), Excel drops the leading zero.
When you enter a number that contains the letter E anywhere in the middle (3.14159E19, for example), Excel formats the cell using the Scientific option, using no more than two decimal places. In this case, Excel would display 3.14E+19.
If you enter a number that includes a comma to set off thousands or millions, Excel applies the Number format using the default thousands separator as defined in Windows'Regional Settings. If the number you entered contains more than two decimal places, Excel stores the number you entered but rounds it for display purposes to no more than two decimal places.
To override any of these automatic number formats, you have four choices:
After entering the data, choose Format, Cells and select a new format. (Press Ctrl+1 to quickly open this dialog box.) This is your best choice if the underlying data stored in the cell is correct and you just want to use a different display format.
Enter an apostrophe before entering the number. When you do this, Excel formats the number as text and displays it exactly as entered. Note that this solution might have unintended consequences in formulas that use the value shown in that cell!
Enter a space character before entering the number. This prefix also tells Excel to format the number as text and display it exactly as entered. Note that this technique will not prevent Excel from converting a number to scientific notation nor will it preserve leading zeros. It will, however, work with all other automatic formatting described in the previous list.
Format the cell as text (choose Format, Cells, click the Number tab, and select Text) before entering the data. This option might also have unintended side effects, as explained a bit later in this chapter.
Avoiding Rounding Errors
It's tempting to assume that because numbers look so orderly in Excel's row-and-column grids, they're also unfailingly accurate. That's not exactly so. To squeeze data so that it fits in a cell, Excel rounds numbers and truncates cell contents, usually without telling you. And there's an absolute limit on the precision of Excel calculations that affects every calculation you make.
Note
What's the difference between rounding and truncating? When Excel rounds a number, it changes the value displayed in the cell without affecting the underlying number stored in the cell. If you enter 3.1415926 in a cell and format it to display two decimal points, Excel displays 3.14. If you later change the display format to show all seven decimal points, your number will appear exactly as you entered it. When Excel truncates data, on the other hand, it chops off digits permanently. If you enter a number with more than 15 decimal places, for example, Excel lops off the 16th and any subsequent numbers to the right of the decimal point. Likewise, if you copy a worksheet that contains cells with more than 255 characters, Excel discards all characters after the first 255.
When Excel alters the display of a number, the most common cause is that the number is too long to fit in the active cell. Excel deals with this sort of data in one of the following three ways:
When you enter data that is wider than the current cell, Excel automatically resizes the column. It does not resize a column if you have already set the column width manually. If the cell is formatted using General format, this automatic resizing stops when the number reaches 11 digits, at which point Excel converts it to scientific notation. If the cell is formatted using Number format, automatic resizing continues until the number reaches 30 digits.
In cells using the default General format, Excel uses scientific notation to display large numbers if possible. The General format rounds numbers expressed this way to no more than six digits of precision (8.39615E+13, for example).
Note
It's no accident that the total number of characters in the preceding example—including the decimal point, plus sign, and E—is 11. Regardless of column width, cells using the General format are always limited to 11 digits.
In cells using any number format other than General, Excel displays a string of number signs (####) if the column is too narrow to display the number in scientific notation. You must change the cell's number format or make the column wider before Excel can display the number correctly.
The second most common cause of apparent errors in a worksheet occurs when the number of decimal cells you specify in a number format doesn't match the number of decimal places stored in that cell or range. Figure 22.1, for example, shows two identical columns of numbers. Because column A uses the General format, each number appears exactly as entered. Column B, on the other hand, is formatted with the Number format to show zero decimal places. When Excel performs the calculation on the numbers in column B, it uses the actual amount stored in the cell, not the rounded version you see here. It then displays the result without any decimal places, exactly as specified in the cell format. Although the sum of the rounded numbers in column B appears to be 16, Excel rounds the actual result to 15 for display purposes. Because of the mismatch between the numbers and their formatting, Excel (and, by extension, the author of this worksheet) appears incapable of basic arithmetic.
Figure 22.1. The values in these two columns are identical, with different formatting. Because of cumulative rounding errors, the numbers in column B appear to add up to 16, despite what the SUM formula suggests.
That's a simple and obvious example, but subtle rounding errors can wreak havoc in an environment where you require precise results. To prevent rounding from making it look like your worksheet contains errors, always match the number of decimal places displayed with the number of decimal places you've entered in the row or column in question.
Tip from 
If you must use rounded numbers in a worksheet, indicate that fact in a footnote on charts and reports you plan to present to others. Rounding can cause apparent mistakes, and anyone who sees your worksheet—or a chart or presentation slide based on those numbers—might make unflattering judgments about your accuracy if totals in a pie chart, for example, don't add up to 100%.
The Limits of Precision
There's an overriding limit to the degree of precision you can achieve with Excel. If you enter a number that contains more than 15 significant digits, Excel permanently and irrevocably converts the 16th and subsequent digits to 0. (It doesn't matter which side of the decimal point the digits appear on—the total number of digits allowed includes those on both sides of the decimal point.) Although you can display numbers with up to 30 decimal places, your calculations will not be accurate if Excel has to store more than 15 digits.
Excel includes a useful, but extremely dangerous, option to permanently store numbers using the displayed precision. If you've increased the numbers in a budget worksheet by 8.25%, for example, you might end up with three decimal places for some entries, even though only two are displayed using the Currency format. If you choose Tools, Options, and click the Calculation tab, you can check the Precision As Displayed box to convert all stored numbers in the current workbook to the values actually displayed.
Caution
When you use the Precision As Displayed option, Excel displays a terse dialog box warning you that your data will permanently lose accuracy. Believe it. This option affects every cell on every sheet in the current workbook, and it remains in force until you explicitly remove the check mark from this box. If you forget you turned on this option, even simple formatting choices like changing the display of decimal places will permanently change stored data. Unless you're absolutely certain that using this option will have no unintended consequences, you should treat it like dynamite.
Tip from 
The Precision As Displayed option affects all cells in the current workbook, and there's no way to apply it just to a selected range. If you want to change the precision of a selection, use the Windows Clipboard to control this option precisely—in the process, you can also avoid any unintended ill effects. Open a new, blank workbook, copy the range you want to change from the original workbook, and paste it into the blank workbook. In the blank workbook, choose Tools, Options, click the Calculation tab, and check the Precision As Displayed box. Click OK when you see the warning dialog box. Now copy the changed data to the Clipboard and paste it over the original data. Close the blank workbook without saving it, and you're finished.
Working with Numbers in Scientific Notation
Scientific (or exponential) notation displays large numbers in a shorthand form that shows the first few digits along with instructions on where to place the decimal point. To convert a number written in scientific notation to its decimal equivalent, move the decimal to the right by the number that appears after "E+"; if there's a minus sign after the E, move the decimal to the left. In either case, add extra zeros as needed. Thus, 8.23E+06 is actually 8,230,000, and 3.82E-07 is .000000382.
Numbers expressed in scientific notation are often rounded. When you see numbers in General format expressed in scientific notation, you'll see a maximum of six significant digits, even if the cell is wide enough to hold more. To display a number in scientific notation using more digits of precision, choose Format, Cells, and choose the Scientific option from the Category list. Use the spinner control to set a fixed number of decimal places, between 0 and 30.
Entering Numbers as Text
Hands down, the most confusing option on the Number tab of the Format Cells dialog box is Text. Use this format when you want to enter numbers in a cell, but you want Excel to treat them as though they were text. You might use this format, for example, when entering a list of part numbers that you will never use in calculations.
If you apply the Text format to a cell and then enter or paste a numeric value into that cell, Excel adds a small green triangle in the top-left corner of the cell, indicating a possible error. Selecting that cell reveals a Smart Tag that gives you the option to convert the cell to number format.
→ For more information about Smart Tags, see "Common Formatting Options".
→ To learn how to check an Excel workbook for errors, see "Checking for Errors in a Worksheet".
When you format numbers as Text, Excel ignores them in formulas such as SUM() and AVERAGE(). It also aligns the cell's contents to the left rather than the right. Unfortunately, applying the Text format requires that you work around an admitted bug that still exists in Excel 2002. If you format the cells first, then apply the Text format, and finally enter the numbers, Excel treats the data as text, just as you intended. However, if you try to apply the Text format to numbers that are already in your worksheet, Excel changes the alignment of the cell, but not the data stored there. After applying the Text format, you must click in each reformatted cell, press F2, and then press Enter to store the number as text. The new error-checking tools in Excel 2002 do not identify cells formatted this way, either.