Join Dennis Taylor for an in-depth discussion in this video Creating a simple macro, part of Excel 2016 Essential Training.
- After identifying the steps that you would like to record and turn into a macro, it's time to begin that process. We've got the file open, 15 - Macros, we're on the H.R. list. Let's imagine we want to record a macro that applies a particular set of formatting features. Click a cell. It's your job to indicate which of these cells needs to have some editing done to them and you simply want to apply a format. We'll go to the Home tab here. Click the drop arrow for the Fill Color bucket. Maybe choose an orange-like color there, maybe a different colored font too.
Now admittedly, what I'm doing is a bit of overkill. I'm applying multiple features: I'll apply bold, italic... maybe get one of the Border features out here, the Thick Box Border or maybe one of the others. Maybe that one. That's probably enough for now. For whatever reason, I like that. That's the set of steps that I want to use, and I want to use it frequently. Sure, I could copy the format, but I want it available all the time. So, what do we do? Let's turn on the feature called a Macro Recorder. Now, before I do that, look in the lower left-hand corner: we see the word Ready.
If you right-click in the status bar at the bottom of the screen, you'll see a ton of different checks here. Here's one for Macro Recording. It's not necessary to select this but if you do, and I just did, down in the status bar now we see a little icon. If you are about to record some macros, it's kind of handy to see that there to remind you, as we slide over it, no macros are currently recording. Click to begin recording a new macro. So we could use that button.
It's not necessary to have it, though. You've identified the steps, you've written them down or you remember them: you'd like to start recording a macro. Now as we record the macro, we will be formatting a certain cell, but it makes no difference which one. Suppose you're about to highlight Diane Andrews over here. Maybe that's a misspelling of her name, or there's some other aspect of that, maybe there's a middle initial we forgot. You're simply flagging it for attention later, and you like those formatting features. So let's start the process of recording a macro.
If that button is present in the lower left corner, we can click that. If not, we can go to the View tab in the ribbon. The far right button is called Macros. It's got a drop arrow below it. Click it, and Record Macro. This opens the Record Macro dialogue box. The macro name can be quite long. It cannot begin with a number, it cannot contain spaces. Ideally you describe what the macro does. In this case, how about Highlight_Cells.
Something like that. We can use the underscore character, no spaces though. I might use underscore here, or simply shift into uppercase; either way. Underscore, or both maybe. Highlight_Cells. We can use a keystroke shortcut; it's not required, there's a tendency to use these frequently as you're getting started. Over time, what might happen, you record another, another, another, all using keystroke shortcuts, you're trying to use meaningful letters, maybe, you've run out of those.
And furthermore, maybe eventually you'll just have so many you kinda forget which one is which. So, let's do use one. And what can we use here? Only letters: we've got 52 choices. Any of the lower-case letters, any of the upper-case letters. If you use lower-case letters, you might be on the way to inhibiting one of the other Excel features. For example, if the keyword in my mind right now is Cells, and I put in "C" right now, the letter "C", as I'm using Excel, everytime I press Control + C, it's not going to mean copy, it's going to activate or make this macro happen.
I don't want that to be the case. I could use Shift + C, as far as I know that's not in use by Excel, but even if it were, if I didn't know what the shortcut meant, it wouldn't mean that much anyway. If, for example, I simply use "H" here, now that does mean Replace, but if I never use Control + H for Replace, this is okay. There is a tendency to use the Shift key and that's what I'm going to do here. I'm holding down the Shift key right now, pressing "H". So that's going to be the keystroke shortcut. Later we might change this or get rid of it, or if we didn't, apply it here or apply it later.
When you record a macro, you should also be thinking, and you probably should have thought ahead of time also, is this macro likely to be used only in this Workbook? And sometimes that's going to be the case, particularly if it's manipulating data in a certain way. Store the macro in This Workbook. If not, you will want to store the macro in Personal Macro Workbook. I always think this should be called "Global Macro Workbook" A macro stored here is available to all Workbooks as you work with Excel.
It's also important to note that when you store a macro in Personal Macro Workbook, that location is on the current computer. We'll talk more about this later. For now, let's say we only want this macro to be used in this Workbook. At a later time, we might decide to use this elsewhere. We can copy and paste it into another workbook, or into the Personal Macro Workbook. A Description: certainly not required, but for longer macros, really helpful. Short macros, you might just copy this: Control + C, paste it down here.
That's a bit redundant; we don't worry about that just now, but, you know, maybe take more time in this cell or on a cell, something like that, or maybe even describe it if you wish. In longer macros, it's important, particularly if you're opening a macro that somebody else has written, and you see a description in there, it's going to help you understand. Not critical just yet. Before I click OK, a reminder: when I do click OK, we are in Record mode, and that means that every action that I take will be recorded, and later when we try to use this macro, and by the way, we sometimes say: "run the macro" "play back the macro", "execute the macro", those all mean the same thing, the steps that I take in here won't happen again.
That means if I scroll up and down during the process of recording, that's going to be in the macro. Sometimes it will be innocuous, we won't see it, but you want to stay focused while recording. So I'm going to click OK, look in the lower-left corner to the right of the word Ready is a box. Slide over it: "A macro is currently recording." So we could come down here later to stop recording. Or we could go back to the View tab and the Macros button. So we're in Record mode right now. We can put the active cell anywhere, it's on the Diane Andrews cell right now, that's fine.
Let's go through those actions again. Home tab, remember we're recording right now. If this button retains the color that we want, we'll just click it for Fill Color. Same thing with the font. Or we could click the dropper arrow and make another choice. If we want it to be blue, a tan or orange background, we want that border like this, we want it to be bold, we want it to be italic. Again, we're sort of overplaying this, but still. This is what we want, and everytime in the future when we click on a cell and you use that keystroke shortcut we want this look to appear.
So we're done recording. We can go down to the lower-left corner, or back to the View tab, the drop-arrow for Macros, Stop Recording. The macro has been recorded. Let's try it over here. Control + Shift + H, and it looks like it works. Does it work on two cells? Forgot to try that. Let's see. Highlight this. Control + Shift + H. Looks like it does. So it's available to us indefinitely, until we possibly get rid of that or make changes to it.
This is always available in this Workbook. Now when you close a Workbook, of course, you'd want to save it to make sure that happens, too. So we've got a macro available. Keep in mind too, there are other macros we might be recording; the process will be the same. We've identified the sequence of steps, maybe on this Workbook here we want a macro that always highlights the formula cells. That's the kind of macro that we might be using elsewhere, too. So, we've identified what we wanted to do, and in our example here, admittedly small, it's a six or seven-step sequence, but from now on, at any time in this Workbook, any time we want to highlight cells in a certain way, we can simply press Control + Shift + H.
Now, it's also important to note that you can't undo a macro. In other words, I can go back here, Undo is not selectable. Now if I happen to click on a cell here, go to the Home tab, make it bold, and then I run the macro over here, Control + Shift + H, I change my mind, notice that in the Quick Access toolbar, the Undo capability is not available. In that previous action that I took here, I can't Undo either.
Now admittedly, these are small actions. On the other hand, we can undo this by copying the format from another cell. In other words, if the macro is taking actions that apply formats, although we can't literally say Undo and have the macro turn that cell back into the way it was, we can take other steps to make sure that format is no longer there. But be thinking about this idea: if you create a macro that deletes data, and then after you try the macro or run the macro on certain cells, you can't then say: "Well, I'll undo this and get my data back." No, you can not.
In situations like that, you might have to close your file without saving it and then re-open it. So be wary of, and stay away from, macros that delete data. Sure, after a while, as you get more comfortable with this concept, if you become the kind of person who uses macros frequently, you'll understand that concept and be very careful with it, but you can not undo a macro. Again, what we did here was simply applying formats, you can easily apply other formats to get rid of them. So we've seen an example here of how to record a macro that applies a special set of formatting tools to a given cell or group of cells.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros