« Happy Birthday Excel
New Charting Utility - Quick XY Charts »

Displaying Thousands

6th September 2006, 12:40 pm by Dick Kusleika

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.

Category: Formatting Cells  |  Comment (RSS)  |  Trackback

7 Comments

  1. Jon Peltier:

    And millions is #,##0,,

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

    6 September 2006, 4:08 pm

  2. Ian Huitson:

    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…

    6 September 2006, 4:56 pm

  3. Eye:

    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

    7 September 2006, 4:13 am

  4. Ian Huitson:

    Eye

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

    Hui…

    7 September 2006, 4:47 am

  5. Charlie III:

    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.

    7 September 2006, 5:26 am

  6. Tom:

    See J-Walk’s SpreadSheetPage Here http://www.j-walk.com/ss/excel/tips/tip19.htm for more custom number formats.

    7 September 2006, 8:01 am

  7. Eye:

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

    7 September 2006, 11:16 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment