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.
Learn Excel from MrExcel Podcast, Episode 2215: Comma, Just a Comma.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today is about this comma up here. This comma says it's going to apply comma style, but when I click that-- look at these numbers over here before I click it-- and I click comma style, and it added the commas. That's beautiful. But it also added two decimal places, it moved everything away from the right edge of the cell, and it replaces the zeros with a tiny little hyphen that seemed to be lined up with a decimal point, which makes no sense at all. I don't know. This comma just annoys me and I'm not the only one. Alright?
This isn't my idea, it excelled out of UserVoice.com. This is written by someone named Tobias. And look at all this-- 260 votes and 78 comments-- a lot of people agree with Tobias that there ought to be a button to just add a thousand separator. We don't need all of the extra stuff, right? So go out and vote for this, I'll put the link down in the YouTube comments so you can vote. We should just pile on and get, you know, a thousand votes for this item.
Now, I was in Fort Wayne and a guy named Greg says, "Hey, [Inaudible 00:01:05] there's a better way to do this. Just choose your numbers, go to Cell Styles, and choose Comma (0)." Well, okay, that is a little bit better. I don't have the extra two decimal places. Yeah, it takes me two clicks to get there instead of one click. That's not bad. But, I mean, really, it's saving me four clicks, because over here, what do I have to do? I have to get rid of those decimals and I always click the wrong one first and then swear, and then click the other one three times. So that's four clicks to get rid of the decimals. So, yeah, that's a great idea, Greg. But we still have the problem that it's moving the numbers away from the right edge of the cell and the zeros are showing up as the dash.
Alright. So, some-- a great suggestion from Dustin W., who wrote and said, "Look, I have three lines of code in my personal macro workbook that just solves this problem.” Alright. Switch over to VBA-- Alt+F11. You're going to put this in your personal macro workbook. Now, I assume that you have a personal macro workbook. If you do, View, Project Explorer, you should see Personal.xlsb; if you don't, go to the outtake at the end of this video, I'll show you how to get this. Choose any one of these modules and then type this code: FixCommaStyle() ActiveWorkbook.Styles("Comma (0)").NumberFormat = "#,""0" and this is going to change the number style. See, now, that style is unique to the workbook so every time I have a workbook, I'm going to have to add this code. And what I've done up here-- Customize Quick Access Toolbar, I went to macros, I found my FixCommaStyle, I added it and then changed the icon. None of these here look like a comma that's being fixed. You'll just have to choose one that you'll remember-- I chose the cloud-- click OK, and then every time I open a workbook, before I choose Comma (0), I just have to come up here and click the cloud. Alright? And then Cell Styles, Comma (0), BAM! I get the commas without all the extra stuff over here, Cell Styles, Comma (0), here, Cell Styles, Comma (0), and it's better. I would still love one click for this, but, you know, who knows how long it'll take Microsoft to get this fixed? And so Kudos to Dustin for that bit of code. Now, again, if you don't have a personal macro workbook yet, wait till the end of the video. I'll show you how to get that.
Buy the book, Excel 2016 VBA Macros, by Tracy and myself. There's an edition of this for every version of Excel-- 3, 7, 10, 13, 16. Check it out.
Alright. So, the question is, why is this comma style doing so much more than just a comma? It has two decimal places, shows zeroes as a hyphen, moves the numbers away from the right edge of the cell, and then adds a comma. 75% of what it does is unadvertised, unexpected, unwanted. Gregg, in Fort Wayne, suggested using the Cell Styles drop-down and Comma (0), and it does remove a few clicks; but Dustin has some code for your Personal Macro Workbook, Fixed Comma Style, this one line of code, and then you can just change the Comma (0) style on-the-fly. Go out and vote. Vote for this idea-- let Microsoft know this irritates you as much as it irritates me.
And to download the workbook from today's video, visit the URL down in the YouTube description-- you can download the workbook.
Well, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Alright. So here's the outtake. Let's say that you don't have personal.xlsb-- you've never recorded macros before, never put anything in your personal macro workbook. it's really easy to get one of these. Just go Ctrl+N for a new workbook, and then we want to record a macro-- so, View, Macros, Record Macro, and we'll just call it anything-- and we're going to store it in the personal macro workbook. So make sure to choose that, click OK. Then do one thing-- I don't care what you do-- just type "Hello", Enter-- awesome. And you'll see down here, there's a Stop recording button. However, you can also go to View, Macros, Stop Recording-- either one, I don't care which one you use, stop recording. Alright. And now, we'll have created a personal.xlsb. And you will have Module1, mine Module4, right there. You can actually get rid of this, alright, but while you're here getting rid of it, make sure to click the Save icon. That will save the personal macro xlsb. When you close Excel, it'll say Hey, do you want to save personal.xlsb? But that'll be hours from now and you'll forget all about ever having recorded this, and I've lost so much stuff-- my personal.xlsb-- because I forget. So I always make a habit of clicking here and clicking Save right away. Well, hey, thanks for watching the outtake. We'll see you next time for another netcast from MrExcel.
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..."