# Displaying Thousands

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.

Posted in Uncategorized

## 7 thoughts on “Displaying Thousands”

1. And millions is #,##0,,

Each comma knocks off another triplet of digits off the right end of the number.

2. Ian Huitson says:

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…

3. Eye says:

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

4. Ian Huitson says:

Eye

Try “k\$” #,##0, for k\$
“M\$” #,##0,, for M\$ etc

Hui…

5. Charlie III says:

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.

6. Eye says:

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!!

Posting code? Use <pre> tags for VBA and <code> tags for inline.