Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comma Style: default to 0 decimal places? (2003)

    Hi,

    I've seen a workmate click on the "comma style" toolbar button and have numbers displayed with thousands separator ("." or ",", depending on the global settings), but with no decimal places (the cell format is set to 0 decimal places, rounding the number consequently). AFAIK, the default is to show 2 decimal places.

    We tried to check which settings could trigger this behavior but weren't successful. In particular, I'd like to reproduce this behavior in my PC.

    Any ideas? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    The default is 2 decimal places. You can create a macro that sets a number format with a thousands separator and no decimal places, and assign this macro to a toolbar button. You can then assign this macro to the "comma style" button, overriding its built-in action.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comma Style: default to 0 decimal places? (2003)

    When you click on the comma style button, it is effectively assigning a style to the selected number. This style can be modified when you choose Format : Style.

    Choose Fomat : Style
    Select the Comma style from the drop down
    Choose modify
    Modify the format to have 0 decimal places
    Choose OK

    When you use this style again, it will assign thousands seperators without the decimal places
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    Thanks, Hans.

    How strange. My workmate doesn't know how to code in VBA or even record macros.

    In Excel I tend to avoid VBA since when you run a procedure, the Undo option gets grayed out. So you can't undo unless you close the document, at the risk of losing who knows how much information since your last save (which are frequent, but anyway...)

    In Word, as far as I remember, running a procedure not only does not gray out the Undo option, but the procedure itself can be undone step by step. There I feel more comfortable.

    I won't say I don't use VBA in Excel, but for these routine stuff, I'd rather not. I'm pretty sure there must be an alternate, non-VBA way around this! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    See Rudi's reply - it explains how you can modify the action of the Comma Style button without VBA.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    Excellent, Rudi! That's exactly what I wanted.

    So many years working with Excel and I think this is my first visit to the Format submenu.

    I have checked that by pressing Ctrl+Shift+1 Excel still applies the 2-decimal comma style, so both flavors are available at the same time.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comma Style: default to 0 decimal places? (2003)

    Hi Rudi
    Is there any way that I can make this a global change? It seems only to apply to the active workbook.
    Regards
    Don

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    Styles are stored in the workbook. You could change the style for newly created workbooks, but that wouldn't affect already existing workbooks.
    A macro in your Personal.xls workbook would work in all workbooks.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comma Style: default to 0 decimal places? (2003)

    Hi Don,

    If you adjust the style in the Book.xlt template that Excel uses for all new workbooks, then this will apply for all NEW workbooks from this point on. Existing workbooks will need to be adjusted induvidually (unfortunately).

    Hans's solution with a macro will cover this if you have a lot of existing WB's that need this setting changed.
    Regards,
    Rudi

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    <hr>this will apply for all NEW workbooks from this point on<hr>
    Not entirely - it will apply to all new workbooks created using the New Workbook icon, but not to those created if you use the File->New... Blank Workbook option. FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comma Style: default to 0 decimal places? (2003)

    ooops... <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Regards,
    Rudi

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    At least, that is the case in 2002 - I can't test 2003 or 2007 just now. (seems daft to me, though!)
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    <hr>Is there any way that I can make this a global change? It seems only to apply to the active workbook.<hr>
    Well that was going to be my next question this morning. Glad that you asked it (and that you got it answered)!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comma Style: default to 0 decimal places? (2003)

    Just tested it on 2003 and found that it works as you found
    <hr>Not entirely - it will apply to all new workbooks created using the New Workbook icon, but not to those created if you use the File->New... Blank Workbook option.<hr>
    But although it doesn't apply to those created if you use the File->New... Blank Workbook option, it does apply to those created with Ctrl+N

    Go Figure
    Regards
    Don

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Comma Style: default to 0 decimal places? (2003)

    There's nothing like a bit of consistency... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •