Microsoft Office Tutorials and References

In Depth Information

**Formatting Numbers**

Formatting Cell

Values

hand, if your numbers have a similar degree of precision (for example, if you’re

logging the number of miles you run each day), the Number format makes more sense.

Number

The Number format is like the General format but with three refinements. First, it

uses a fixed number of decimal places (which you set). That means that the decimal

point always lines up (assuming you’ve formatted an entire column). The Number

format also lets you use commas as a separator between groups of three digits, which

is handy if you’re working with really long numbers. Finally, you can choose to have

negative numbers displayed with the negative sign, in parentheses, or in red lettering.

Up to speed

The Relationship Between Formatting and Values

The format that you choose for a number doesn’t affect

Excel’s internal storage of that number. For example, if a

cell contains the fraction 1/3, then Excel stores this value as

0.333333333333333. (The exact number of decimal places

varies, depending on the number you’ve entered, due to

the slight approximations computers need to make when

converting fractional numbers into 0s and 1s.) When

deciding how to format a cell, you may choose to show only two

decimal places, in which case the number appears in your

worksheet as 0.33. Or, maybe you choose just one

decimal place, in which case the number is simply 0.3. In both

cases, Excel still keeps the full 15 or so decimal places on

hand. To tell the difference between the displayed number

and the real number that Excel stores behind the scenes,

just move to the cell. Then look at the formula bar, which

always shows you the real deal.

together, you won’t end up with 0.3 + 0.3 + 0.3 = 0.9.

Instead, you’ll add the more precise stored values and end

up with a number that’s infinitesimally close to, but not

quite, 1. Excel rounds this number up to 1.

This is almost always the way you want Excel to work

because you know full well that if you add up 1/3 three

times you end up with 1. But, if you need to, you can

change this behavior.

To change what Excel does, select File➝Options, choose

the Advanced section, and then scroll down to the “When

calculating this workbook” group of settings. A “Set

precision as displayed” checkbox appears. When you turn on

this checkbox, Excel adjusts all the values in your current

spreadsheet so that the stored value matches the

displayed value. Unfortunately, with this choice, you get less

precise data. For example, if you use this option with the

1/3 example, Excel stores the display value 0.3 instead

of 0.333333333333333. Because you can’t reverse this

change, Excel warns you and asks for a final confirmation

when you try to apply the “Precision as displayed” setting.

Because of this difference between the stored value and the

displayed number, there may be some situations where it

looks like Excel’s making a mistake. For example, imagine

you have three cells, and each stores 0.333333333333333

but displays only 0.3. When you add these three cell values

Currency

The Currency format closely matches the Number format, with two differences.

First, you can choose a currency symbol (like the dollar sign, pound symbol, Euro

symbol, and so on) from an extensive list; Excel displays the currency symbol before

the number. Second, the Currency format always includes commas. The Currency

format also supports a fixed number of decimal places (chosen by you), and it lets

you customize how negative numbers are displayed.