Microsoft Office Tutorials and References
In Depth Information
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.
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
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.