Comma Just Comma

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.