To format numbers, right-click the cell or cells, and then click
Format Cells. Then, alter the way that Excel displays the numbers.
When you calculate numbers, Excel performs the calculations by using the stored values. When you calculate formatted values, you may receive unexpected results because the displayed numbers may be slightly different from the stored values.
For example, if two cells each contain the value 10.005, and the cells are
formatted to display values as currency, the value $10.01 is displayed in
each cell. If you add the two cells together, the result is $20.01 because
Microsoft Excel adds the stored values (10.005 + 10.005), not the displayed
values ($10.01 + $10.01).
If you want the calculations to be based on the displayed values, you can
change the precision of calculations so that they use the displayed values
instead of the stored values. To do this, use the "Precision As Displayed"
feature. Alternatively, make sure that the values are rounded to the same precision as
the number formatting.
How to round values to the same precision as number formatting
You can use the ROUND worksheet function to round values to a specific
precision.
For example, type the following data into a new worksheet:
$A$1: 5
$A$2: 200%
$A$3: =A1*A2
The value in cell $A$3 is 10.005. The value in cell $A$3 displays $10.00 if
you format the value as currency. To round the precision correctly, replace
the formula in cell $A$3 with the following formula:
When you do this, the worksheet function takes the result of the expression
in the first argument and rounds it to the hundredths place (two digits to
the right of the decimal).
How to round all values in a worksheet by using precision as displayed
If you want the calculations to be based on the displayed values, change
the precision of calculations so that they use the displayed values instead
of the stored values. To do this, follow these steps, as appropriate for the version of Excel that you are running.
Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003
- On the Tools menu, click Options, and then click the Calculation tab.
- Under Workbook Options, click to select the Precision as displayed check box.
Microsoft Office Excel 2007
- Click the Microsoft Office Button, click Excel Options, and then click the Advanced category.
- In the When calculating this workbook section, click to select the workbook that you want, click to select the Set precision as displayed check box, and then click OK.
Caution When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes any constant values on the worksheets in the workbook. If you subsequently calculate values with full precision, Excel cannot restore the original underlying values.