Microsoft Office Tutorials and References

In Depth Information

**Using keyboard shortcuts to format numbers**

When Numbers Appear to Add Incorrectly

Applying a number format to a cell doesn’t change the value — it only changes how the value appears

in the worksheet. For example, if a cell contains 0.874543, you may format it to appear as 87%. If that

cell is used in a formula, the formula uses the full value (0.874543), not the displayed value (87%).

In some situations, formatting may cause Excel to display calculation results that appear incorrect,

such as when totaling numbers with decimal places. For example, if values are formatted to display

two decimal places, you may not see the actual numbers used in the calculations. But because Excel

uses the full precision of the values in its formula, the sum of the two values may appear to be incorrect.

Several solutions to this problem are available. You can format the cells to display more decimal places.

You can use the
ROUND
function on individual numbers and specify the number of decimal places Excel

should round to. Or you can instruct Excel to change the worksheet values to match their displayed

format. To do so, access the Excel Options dialog box and click the Advanced tab. Check the Set

precision as displayed check box (located in the When calculating this workbook section).

Selecting the Precision as displayed option changes the numbers in your worksheets to permanently match their

appearance on-screen. This setting applies to all sheets in the active workbook. Most of the time, this option is
not

what you want. Make sure that you understand the consequences of using the Set Precision as displayed option.

13

Chapter 15, “Introducing Formulas and Functions,” discusses
ROUND
and other built-in functions.

The following are the number format categories, along with some general comments:

General:
The default format; it displays numbers as integers, as decimals, or in

scientiﬁ c notation if the value is too wide to ﬁ t in the cell.

■

Number:
Enables you to specify the number of decimal places, whether to use a

comma to separate thousands, and how to display negative numbers (with a minus

sign, in red, in parentheses, or in red and in parentheses)

■

Currency:
Enables you to specify the number of decimal places, choose a currency

symbol, and how to display negative numbers (with a minus sign, in red, in

parentheses, or in red and in parentheses). This format always uses a comma to separate

thousands.

■

Accounting:
Differs from the Currency format in that the currency symbols always

align vertically

■