MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comma Just Comma


June 07, 2018 - by Bill Jelen

Comma Just Comma

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.

If only it would do just what it says...
If only it would do just what it says...

But when you click on this icon, you will notice that four things happen:

  1. Any zero values are replaced with a hyphen. Not just a hyphen, but a hyphen mysteriously lined up with the decimal points.
  2. Excel inserts two decimal places
  3. Excel indents the final digit so it is not aligned with the right edge of the cell
  4. Excel adds a thousands separator
I only wanted 1 of these four things to happen
I only wanted 1 of these four things to happen

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 just want a comma
I just want a comma

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.


Note

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.

The Comma 0 style solves one of the three issues
The Comma 0 style solves one of the three issues

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.

Hot Idea
Click here to vote

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.

Watch Video

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

Title Photo: Alexander Mils instagram.com/alexandermils on Unsplash