Chapter 2: Entering and Editing Worksheet Data
2
The Number tab of the Format Cells dialog box displays 12 categories of number formats.
When you select a category from the list box, the right side of the tab changes to display
options appropriate to that category.The Number category has three options that you can control: the number of decimal places
displayed, whether to use a thousands separator, and how you want negative numbers
displayed. The Negative Numbers list box has four choices (two of which display negative
values in red), and the choices change depending on the number of decimal places and
whether you choose to separate thousands.The top of the tab displays a sample of how the active cell will appear with the selected
number format (visible only if a cell with a value is selected). After you make your choices,
click OK to apply the number format to all of the selected cells.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 to
which Excel should round. 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 Set Precision as Displayed option changes the numbers in your worksheets to match their appearance
on-screen permanently. This setting applies to all sheets in the active workbook. Most of the time, this option is not
what you want. Make sure you understand the consequences of using the Set Precision as Displayed option.
Chapter 9, “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 scientific
notation if the value is too wide to fit in the cell.