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 #,##0, 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!!