Microsoft Office Tutorials and References

In Depth Information

**Formatting Numbers**

Formatting Cell

Values

Accounting

The Accounting format is modeled on the Currency format. It also lets you choose

a currency symbol, uses commas, and has a fixed number of decimal places. The

difference is that the Accounting format uses a slightly different alignment. The

currency symbol’s always at the far left of the cell (away from the number), and there’s

always an extra space that pads the right side of the cell. Also, the Accounting format

always shows negative numbers in parentheses, which is an accounting standard.

Finally, the Accountancy format never shows the number 0. Instead, a dash (-) is

displayed in its place. There’s really no reason to prefer the Currency or the

Accounting format. Think of it as a personal decision, and choose whichever looks nicest on

your worksheet. The only exception is if you happen to
be an accountant, in which

case you really have no choice in the matter—stick with your namesake.

Percentage

The Percentage format displays fractional numbers as percentages. For example, if

you enter 0.5, that translates to 50%. You can choose the number of decimal places

to display.

There’s one trick to watch out for with the Percentage format. If you forget to start

your number with a decimal, Excel quietly “corrects” your numbers. For example, if

you type 4 into a cell that uses the Percentage format, Excel interprets this as 4%. As

a result, it actually stores the value 0.04. A side-effect of this quirkiness is that if you

want to enter percentages larger than 100%, you can’t enter them as decimals. For

example, to enter 200%, you need to type in 200 (not 2.00).

Fraction

The Fraction format displays your number as a fraction instead of a number with

decimal places. The Fraction format doesn’t mean you must enter the number as a

fraction (although you can if you want by using the forward slash, like 3/4). Instead

it means that Excel converts any number you enter and displays it as a fraction. Thus,

to have 1/4 appear you can either enter .25 or 1/4.

Note:
If you try to enter 1/4 and you
haven’t
formatted the cell to use the Fraction number format, you

won’t get the result you want. Excel assumes you’re trying to enter a date (in this case, January 4 of the

current year). To avoid this misunderstanding, change the number format
before
you type in your fraction.

Or, enter it as
0 1/4
(zero and one quarter).

People often use the Fraction format for stock market quotes, but it’s also handy

for certain types of measurements (like weights and temperatures). When using the

Fraction format, Excel does its best to calculate the closest fraction, which depends

on a few factors including whether an exact match exists (entering .5 always gets you

1/2, for example) and what type of precision level you’ve picked when selecting the

Fraction formatting.