If you have a spreadsheet containing a lot of numbers, it will be easier for managers to read if you round them to the nearest thousand.
To display figures to the nearest thousand, go into Format – Custom, and add three things – a dot, a zero and a comma
Example: To display these numbers:
Type the first column of numbers into Excel, column A. Then copy them into column B.
Highlight column B.
From main menu: Format – Cells – Custom (at the bottom)
In the Type box you’ve probably got General
Find the format #,###0;[Red]-#,###0 (in Excel 2003, it’s at the bottom of the screen)
Put this format into the Type box.
Now, at the far right after the zero, insert a dot, then a zero, then a comma
It should now look like this: #,###0;[Red]-#,###0.0,
Then do the same thing after the zero just before the semi-colon, so that it now looks like:
Make sure you get the sequence right: dot – zero – comma
Press OK. The numbers are now nicely rounded to the nearest thousand.
Number formatsJohn - you might find the formatting section towards the bottom of our Excel Compendium dates & numbers article useful.
dot - zero - commaWell, I use the "dot-zero-comma" slogan because I just worked it years ago out via suck it and see. I think the meaning is:
dot = add decimal places
0 = add a single decimal place, and if it's 0 display 0 ( the difference between # and 0 is that # means if it's zero, don't print anything)
comma = round to the nearest thousand. I think two commas = to the nearest million
We need Richard Scoullar. He'll know..
Adding up rounded figuresIf you then add up those rounded figures you do not neccessarily get the correct figure, even if you apply the rounding format as described to the total. To do that you need to use one of the rounding formulae such as Round(number,places to round to) on each number to get the correct total. Play around with Round formulae and format in Excel to see what I mean.
Rounding including thousandsI included some information on rounding, including working with thousands in one of my blog entries which might help