Excel - Rounding whole numbers

Asked By Bo on 07-Oct-08 05:37 PM
I have an existing excel worksheet with several numbers, subtotals, etc that
are all stated in whole numbers such as $1,245,789.

I would like to state all the cells in 1,000's.

For example, the number in my example above would become $1,245.

What is the easy way to do this?
Bob G.

Wig replied on 07-Oct-08 05:45 PM
With the value in cell A1:


http://www.wimgielis.be = Excel/VBA, soccer and music
Bob replied on 07-Oct-08 05:52 PM
If you simply want it to display the values in thousands (not actually round
the underlying values):

1.  Select all the values you want to format.
2.  From the FORMAT menu, choose CELLS.
3.  On the NUMBER tab, choose CUSTOM in the Category list.
4.  In the TYPE box, enter #,### (or 0,000)
5.  Click on the OK button.

Now all the cells will display as thousands, but keep their true values:

A1 = 1,234,567

Cell A1 will display 1,234 but still keeps its full value.
Bo replied on 07-Oct-08 06:33 PM
Bob Thanks for the suggestion but it does not work for me.

I am using Excel 2007.

I go the worksheet and highlight all the cells that I wish to change.

On the Home memu I find Cells and click on format cells

On the number tac under custom I have inserted #,### and have cliked the OK,

But the number does not change!

Bob G.
Peo Sjoblom replied on 07-Oct-08 07:22 PM
Your numbers are text. Copy an empty cell, select the "numbers" and do
edit>paste special and select add
then try the custom format



Peo Sjoblom
Fred Smith replied on 07-Oct-08 08:34 PM
Bob forgot the trailing comma which is needed to display only thousands. Use
a format of:

T. Valko replied on 07-Oct-08 11:46 PM
Note that using #,##0, will round the last digit:

$1,245,789 displays as $1,246

Microsoft Excel MVP