Excel: Making Numbers Appear Rounded While Keeping Accuracy

To make a number appear rounded even though its exact value is being used for calculations, combine numeric and text formats in the Number dialog box on the Format menu.

For example, if you want the number 1,347,698 to appear as $1,348,000 while reserving its accuracy for calculations, you can format the number as $###,###,",000". Excel's ability to crop numbers after a particular value to the left of the decimal place, to add appropriate commas, and to add text to a number's appearance makes this possible. The following table provides examples:

   This Number              Formatted As              Yields
   -----------              ------------              ------

   1578999                  ###,###,                  1,579
                            ###,###, "Thousand"       1,579 Thousand
                            $###,###,",000"           $1,579,000
   5789                     $###,###,",000"           $6,000

Selecting Precision As Displayed from the Calculations dialog box on the Options menu does not permanently change the original value (because you are combining text with the number format) unless the digits following the decimal are changed.

Note: This information also applies to Excel for Windows 2.1, 2.1c, 2.1d, and 3.0