Microsoft Office Tutorials and References

In Depth Information

**Formatting Numbers**

Formatting Cell

Values

simply ignores the number format. For example, if you enter
Half past 12
in a

column full of times, Excel considers it plain ol’ text—although, under the hood, the

cell’s numerical formatting stays put, and Excel uses it if you change the cell content

to a time.

Figure 16-4:

You can learn about the different

number formats by selecting a cell

that already has a number in it,

and then choosing a new

number format from the Category list

(Home
Cells
Format
Format
➝

Cells). When you do so, Excel uses the

Format Cells dialog box to show how

it’ll display the number if you apply

that format. In this example, you see

that the cell value, 5.18518518518519,

will appear as 5.19E+00, which is

scientific notation with two decimal

places.

When you create a new spreadsheet, every cell starts out with the same number

format: General. This format comes with a couple of basic rules:

• If a number has any decimal places, Excel displays them, provided they fit in the

column. If the number has more decimal places than Excel can display, it leaves

out the ones that don’t fit. (It rounds up the last displayed digit, when

appropriate.) If you change a column width, Excel automatically adjusts the amount of

digits it displays.

• Excel removes leading and trailing zeros. Thus, 004.00 becomes 4. The only

exception to this rule happens with numbers between –1 and 1, which retain the

0 before the decimal point. For example, Excel displays the number .42 as 0.42.

As you saw in Chapter 14, the way you type in a number can change a cell’s

formatting. For example, if you enter a number with a currency symbol, the number format

of the cell changes automatically to Currency. Similarly, if you enter three numbers

separated by dashes (-) or forward slashes (/), Excel assumes you’re entering a date,

and adjusts the number format to Date.

However, rather than rely on this automatic process, it’s far better just to enter

ordinary numbers and set the formatting explicitly for the whole column. This approach

prevents you from having different formatting in different cells (which can confuse