Microsoft Office Tutorials and References
In Depth Information
Using the Format Cells Dialog Box
This change may lead you to believe that Excel rounded the value up to two
decimal places. In fact, the program has rounded up only the display of the
calculated value — the cell still contains the same old value of 25.6456. If you
use this cell in another worksheet formula, Excel uses the behind-the-scenes
value in its calculation, not the spiffed-up one shown in the cell.
If you want the values to match their formatted appearance in the worksheet,
Excel can do that in a single step. Be forewarned, however, that this is a
oneway trip. You can convert all underlying values to the way they are displayed
by selecting a single check box, but you can’t return them to their previous
state by deselecting this check box.
Well, because you insist on knowing this little trick anyway, here goes (just
don’t write and try to tell me that you weren’t warned):
1. Make sure that you format all the values in your worksheet with the
correct number of decimal places.
You must do this step before you convert the precision of all values in
the worksheet to their displayed form.
2. Choose File ➪ Options ➪ Advanced or press Alt+FTA to open the
Advanced tab of the Excel Options dialog box.
3. In the When Calculating This Workbook section, click the Set
Precision as Displayed check box (to fill it with a check mark).
Excel displays the Data Will Permanently Lose Accuracy alert dialog box.
4. Go ahead (live dangerously) and click the OK button or press Enter to
convert all values to match their display. Click OK again to close the
Excel Options dialog box.
Save the workbook with the calculated values. After converting all the values
in a worksheet by selecting the Set Precision as Displayed check box, open the
Save As dialog box (File ➪ Save As or press Alt+FA). Edit the filename in the File
Name text box (maybe by appending as Displayed to the current filename)
before you click the Save button or press Enter. That way, you’ll have two
copies: the original workbook file with the values as entered and calculated
by Excel and the new as Displayed version.
Make it a date!
In Chapter 2, I mention that you can easily create formulas that calculate the
differences between the dates and times that you enter in your worksheets.
The only problem is that when Excel subtracts one date from another date