Join Dennis Taylor for an in-depth discussion in this video Format macros to make you a more efficient Excel user, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Format macros to make you a more efficient Excel user