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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

this will apply for all NEW workbooks from this point on

---

[/indent]
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.

Reply | Quote

ooops…

Reply | Quote

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

Reply | Quote

Just tested it on 2003 and found that it works as you found [indent]

---

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.

---

[/indent] 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

Reply | Quote

There’s nothing like a bit of consistency…

Reply | Quote

[indent]

---

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

---

[/indent]Well that was going to be my next question this morning. Glad that you asked it (and that you got it answered)!

Reply | Quote