Displaying a number in millions as thousands

[reddwarf]

Hi,
I have numbers which are displayed in millions but I need the cell to display as 1000's rounded up on the 6th decimal place.

For example:

0.00216966334633045
0.00372914538835134

should read:

2,170
3,729

What is the easiest way to do this? Hopefully it can be done by clever formatting or do I need to use formulas as used in the above example.

Thanks

[63falcondude]

The easiest way would probably be to type 1000000 into a cell, copy that cell, select all of your cells being shown in millions, and then paste special > multiply, then decrease decimals to 0.

[63falcondude]

If you don't want to change the values in the cells, and instead only want to change how they are being displayed, one option is to use the custom formatting:

#,###%%%

You can hide the %%% by hitting Ctrl J between the # and %, then using wrap text to put it on a new line in the cell.

[reddwarf]

Wow! How clever is that. Pure and simple, worked a treat. Thank you

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.