Displaying Thousands
Stu asks:
Is there a number format that I can use that will display numbers in the thousands without actually using a formula? So, 20,000,000 in sales in a cell would actually be displayed as 20,000.
Good question, and yes there is.
The number format
<span class="text">#,##0,</span>
works because of the comma at the end.
And millions is #,##0,,
Each comma knocks off another triplet of digits off the right end of the number.
and better then #,##0,, is
#,##0,.##
for 2 Decimal places etc ie 12,235,678 will show as 12,345.678
but is still 12,345,678 for Calculation purposes
Hui…
Great way to do this.
But then I would like to have the currency displayed also and Excel will not accept any
other than the previous ones. I would like e.g. kilo$ (k$) or mega$ (M$) is this possible?
Tried to find where to add this and had no success, as Excel seems to revert
to currency settings by default when # is used. Ok I may have missed something, some where
BTW a great page for learning more…. Keep it up and running as I go here every workday.
Best regards
Eye
Try “k$” #,##0, for k$
“M$” #,##0,, for M$ etc
Hui…
All this time I have been doing this formatting and I never knew why or how this worked, I just did it. Now I know. The things you learn from Dick. I use #,##0,;[RED](#,##0,)to get -987654821 to look like (987,655). We like to see our negatives in red. Notice the number is rounded up from a 4 to 5.
See J-Walk’s SpreadSheetPage Here http://www.j-walk.com/ss/excel/tips/tip19.htm for more custom number formats.
Thanks Ian
As I’m residing in Sweden I needed kkr (and million kr) and kkr worked but not any other…
Now I learned that I missed just the quotation marks, so now I’m rather pleased.
Nice to know that you still can learn things as you get older, just remembering them is harder
BTW comma doesn’t work either as we uses space for this… but I did know that.
Thanks again!!