Comma Just Comma
June 07, 2018 - by Bill Jelen
There are a few things that continue to drive me crazy about Excel. One of them is the "Comma Style" icon that is in the middle of the Home tab of the Ribbon. The tooltip for this icon claims that it will add a thousands separator to your numbers.
But when you click on this icon, you will notice that four things happen:
- Any zero values are replaced with a hyphen. Not just a hyphen, but a hyphen mysteriously lined up with the decimal points.
- Excel inserts two decimal places
- Excel indents the final digit so it is not aligned with the right edge of the cell
- Excel adds a thousands separator
What's the deal with this, Microsoft? You promised me a thousands separator, but you added a whole bunch of baggage along with it. Here is a gratuitous use of a pie chart to show how much of the settings done by the Comma icon are actually expected:
I've been ranting about the comma icon recently in my Power Excel seminars. Greg in Fort Wayne suggested that I start using the Comma 0 style found in the Cell Styles gallery. This does eliminate the hassle of clearing the two decimal places.
Getting rid of the 2 decimal places is valuable: It saves at least four clicks. Every time I try to Decrease Decimal, I accidentally click on Increase Decimal instead. I then have to click the other icon three times to remove the three decimal places.
I rant about Comma style because it gets a laugh when I pull up Excel.UserVoice.com and show the leading Hot Idea is to offer a comma style that truly just adds a comma. I encourage you to click on that link and add your vote to tell Microsoft that this bothers you as well.
But in another seminar, Dustin suggests adding this tiny macro to your personal macro workbook:
Sub FixCommaStyle() ' Thanks to Dustin W for this code ActiveWorkbook.Styles("Comma").NumberFormat = "#,##0" End Sub
Add an icon to your Quick Access Toolbar to run this code. Run the code once per workbook. The Comma Style will change to add a thousands separator but nothing else.
Download Excel File
To download the excel file: comma-just-comma.xlsm
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"VLOOKUP is the Derek Zoolander of the Excel world..."