"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Round Numbers To The Nearest 1000


Often in Excel, when working with large numbers it is common practice to show numbers as thousands, with a note to that effect shown somewhere on the report. To display your numbers to the nearest thousand in Excel you do not need to change any numbers or do any conversions. You simply change the way the number is displayed by creating a custom number format.
  1. Select the cells containing the numbers you want to display as thousands.
  2. Select Format, Cells, and click the Number tab.
  3. In the Category box select Custom
  4. In the Type box enter and click OK
The number 5,250,760.95 will now display as 5,251.

Always remember that formatting a number has no effect on its underlying value.

One issue you may have with this practice is that sometimes your totals may not appear to add up. In the above example, if we have three cells with amounts of 5,250,760.95 and total them, the total will show 15,752. This total appears to be wrong, but the fact is that the underlying values of those three cells add to 15,752,282.85, which when rounded to the nearest thousand is 15,752.

If this is a problem for you, you will either have to re-enter the values in your worksheet as thousands (i.e. 5,251 instead of 5,250,760.95) or may get away with using the Round function in some of your formulas. =ROUND(formula,-3)


Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.