Format macros to make you a more efficient Excel user

- [Dennis] In this worksheet, we've got some data over in columns A through E. When I see the data in column A, often what I will do is change the format, and many times I'll use the Comma button found on the Home tab right here, and often I'll just keep it that way. Notice the negatives are in parentheses, and we have commas where appropriate, and we have two decimals. Now, there are often times, too, when I come to numbers like this, a format that I prefer is to have fewer decimals, and maybe I want negatives to have a leading minus. Also notice the extra half space left over when we have parentheses here. With a leading minus, we won't get that. So what can we do? Well, certainly not fast, but let's say if we select column A what we can do is then right-click column A, go to Format Cells, and choose Number on the Number tab here. Decimal place is zero, 1000 separator, negatives with a leading minus, click OK. I want to be able to get there pretty quickly. We can't do that in standard Excel usage. And furthermore, in column D, no matter what the Date style is, and here's a mix of them, I prefer the style we're looking at right over here, two-digit month, two-digit day, four-digit year. So what will I do in column E? Right-click, go to Format Cells, choose Date. This one's fairly close right here, then I'll jump over to Custom and make this layout be mm/dd/yyyy. That's what I want. We can create a macro to do this and get to the macro by way of a new button that we can put in the quick access toolbar at the top of screen. Let me undo this last action here, so we eventually will come back and work with the Date data. So I'll press Control + Z. And over in column A, I'll press Control + Shift + Tilde to turn that into General format. That's by the way, one of the keystroke shortcuts that's listed over here in columns G through M. Let's record a macro. Usually this works best if you have a few cells highlighted to make sure the macros doing what you want it to do. So I'm highlighting three cells and then going to the View tab in the ribbon off to the right, clicking the drop arrow for Macros. We're going to Record a Macro and give it a name that's somewhat meaningful, Comma. That could be meaningful enough. Underscore, we cannot use spaces here. And I'll put in 0_decimals. We'll avoid using a keystroke shortcut key here, but we could do that, too. Store Macro In: Personal Macro Workbook. If this says, for example, This Workbook or New Workbook, change it to this. When you create a macro and store it here, that means it will always be available in the future to use. Now, when you exit from Excel, you'll be prompted to save this, and you would do that of course. It will always be available. We click OK. We're in Recording mode. We've got cells already highlighted. So I'll simply right-click on them, jump into Format Cells, and apply the format we saw before. Number, zero decimals, Use the 1000 Separator, leading minus for negatives. Click OK. We're done recording. We go back to that macro arrow button, Stop Recording. Now, by going to the quick access toolbar, there's a drop arrow on the right, click that, go down to More Commands. In the Excel Options dialog box, choose Commands from Popular Commands? No, drop arrow, Macros. There's only one here. There's the name of it. I'll add this to the quick access toolbar. If I have time, I'll come back later, modify, and maybe choose a different button. I can even change the wording here, but for now, we're in a hurry, let's say, click OK. So, at a later time, I want to format column B maybe. There's that button up there. I slide over it. You see the indicator. Remember the text later we could change. I'll just click it, adds the macro. We ran it. And we could do the same kind of thing with the Date here. And again, very quickly highlight a few cells, off to the right click the drop arrow, Record Macro, I'll call it MyDateFormat, something like that. Personal Macro Workbook, OK, right-click, jump right into Format Cells again, this time Date, pick the enter the leading zero there over to custom, change this layout so that it ends in four Ys. Okay, done, once again, we'll go to that quick access toolbar, click the right button, More Commands, Macros, there we are. There's the one for MyDateFormat, add that. Click OK, and at a later time, we might want to format these or the entire column. There's the button. Now, of course, for the moment, both buttons are the same. Later we'll come back and change that. There we are and that works. So, two quickly utility macros, and we can have them around forever. Again, all based on the idea that we think we need these often, and we've been using the longer techniques. This is going to make life a lot faster and more efficient as we work with Excel.