Microsoft Office Tutorials and References

In Depth Information

**Formatting Numbers**

Formatting Cell

Values

You can choose to have fractions with three digits (for example, 100/200), two digits

(10/20), or just one digit (1/2), using the top three choices in the Type list. For

example, if you enter the number 0.51, then Excel shows it as 1/2 in one-digit mode,

and the more precise 51/100 in three-digit mode. In some cases, you may want all

numbers to use the same denominator (the bottom number in the fraction) so that

it’s easy to compare different numbers. (Don’t you wish Excel had been around when

you were in grammar school?) In this case, you can choose to show all fractions as

halves (with a denominator of 2), quarters (a denominator of 4), eighths (8),

sixteenths (16), tenths (10), and hundredths (100). For example, the number 0.51

displays as 2/4 if you choose quarters.

Tip:
Entering a fraction in Excel can be awkward because Excel may attempt to convert it to a date. To

prevent this confusion, always start by entering 0, and then a space. For example, instead of typing
2/3

enter
0 2/3
(which means zero and two-thirds). If you have a whole number and a fraction, like 1 2/3, you’ll

also be able to duck the date confusion.

Scientific

The Scientific format displays numbers using scientific notation, which is ideal

when you need to handle numbers that range widely in size (like 0.0003 and 300)

in the same column
. Scientific notation displays the first non-zero digit of a number,

followed by a fixed number of digits, and then indicates what power of 10 that

number needs to be multiplied by to generate the original number. For example, 0.0003

becomes 3.00 × 10
−4
(displayed in Excel as 3.00E-04). The number 300, on the other

hand, becomes 3.00 × 10
2
(displayed in Excel as 3.00E02). Scientists—surprise,

surprise—like the Scientific format for doing things like recording experimental data

or creating mathematical models to predict when an incoming meteor will strike

the Earth.

timesAVinG tip

Shortcuts in the Ribbon

You don’t need to waste hours jumping between your

worksheet and the Format Cells dialog box. The ribbon gets

you to some of the most commonly used number formats

in the Home➝Number section.

One of the neatest features is the list of currency options

for the Accounting button. If you click the drop-down

arrow on the Accounting button (which looks like a dollar

sign), you see a list with different currency symbols you can

choose (like Pounds, Euros, Chinese Yuan, and so on). But

if you click the other portion of the Accounting button (not

the arrow), you get the currency symbol that’s appropriate,

based on your computer’s regional settings.

The Home➝Number section’s most prominent part is the

drop-down list of number formats (Figure 16-6). Just

underneath are buttons that let you apply one of the three

most common formats: Accounting, Percent, or Number.

Just to the right are two buttons that let you increase or

decrease the number of decimal places that you see at once.