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.

format cells dialog

The number format #,##0, works because of the comma at the end.

Posted in Uncategorized

7 thoughts on “Displaying Thousands

  1. and better then #,##0,, is


    for 2 Decimal places etc ie 12,235,678 will show as 12,345.678
    but is still 12,345,678 for Calculation purposes


  2. 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

  3. 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.

  4. 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.

Leave a Reply

Your email address will not be published.