If you find yourself repeating a sequence of Excel actions frequently, you can automate those actions by creating a macro. A macro is a single action that encompasses multiple actions. Record those repetitious actions to create a macro and later use a keystroke shortcut to perform all the actions at once.
- [Voiceover] As you work with Excel there are times when you find yourself repeating actions. In other words, you're saying, "I did this yesterday," same eight steps, same nine steps. Sometimes even three steps get a little annoying because of the repetitious aspect of them. Any time that starts to bother you open your eyes to the idea that you could be automating those tasks, even small ones. The term macro is a bit hard to describe without an actual example. It essentially means, take a single action instead of multiple actions.
Let's just start with a simple example here. When people leave this organization we don't immediately remove their names but we do want to indicate they've left visually just by using Strikethrough. So I'm going to highlight these cells here, Robin Hutchinson has just left the organization, I want to apply Strikethrough. It's not in the Ribbon menu system. I know it's available by way of format cells, I used it a while ago. I started to use it more frequently. How do we get to format cells? Couple different ways. You can press Control + one.
You could also right-click on the highlighted data then go right to Format Cells, that's another way. And it's on the Font tab. Right-clicking and choosing Format Cells or pressing Control + one doesn't always take you to the Font tab, but nevertheless you can click there and here we see Strikethrough. There it is. We would also come back here to remove that feature as well. So, nothing terribly long about that, really, but let's imagine you start to use it more frequently and you say to yourself, "I wish I didn't have to "go through all those clicks to get there." Well, then you discover maybe, someone says to you, "You know, there's a keystroke shortcut for that? "It's called Control + five, try that." Well, Kevin has left recently, highlight this data, Control + five.
Good, it works. And furthermore, that's what we call a toggle shortcut, we can press Control + five to remove Strikethrough. Now, we don't call that process a macro but in a certain sense it's just like a macro. It's a single action instead of multiple actions. And in a certain sense you could say that for the most part most of our buttons, particularly on the Home tab, are like little macros. Now, almost anybody who uses Excel probably from day one knows what B means for Bold, I for Italic, U for Underline, and perhaps you've seen them in Microsoft Word as well, too.
If they were not here, though, how would we get to them? Well, there are keystroke shortcuts there, too, but even there, if we had no keystroke shortcuts we would be going through a command sequence. I'm going to format cells and font and applying bold, underline, or italics as needed. So, in a certain sense we've been using macros all along, we just don't call them that. Now, the idea of a macro might begin with some small ideas like I've mentioned here. Let's move on to something maybe a little bit more powerful. The next worksheet over is called Formulas.
Maybe in your organization you've become kind of the worksheet guru. You're the one that people turn to when they're trying to figure out what's going on in a worksheet. It would be helpful here if we could highlight all the cells that have formulas. We can do this manually. It doesn't take too long. Click on a single cell anywhere in the worksheet and from the Home tab, the far right button, Find and Select, we can choose Formulas, and all the formula cells are highlighted. But let's suppose that every time that does happen we'd also like them to have a background color.
And maybe we'll pick a certain color and stick with it. So manually what we might do, doing this for the first time let's say, on the Home tab we'll go to the fill color bucket, click the drop arrow here, and be sure to pick a light color so you can see the numbers through it. I'll pick this sort of tan orange color right here. Now, let's say that every time I encounter a worksheet that I'm unfamiliar with and I'm trying to help others go about what's going on, I want to be able to quickly highlight all the formula cells in color.
Typically when you work with Excel you're not counting how many steps that takes but let's revisit that quickly with how many clicks that might have taken. From the Home tab sometimes you'll have to click that so count that as one. Find and Select, count that as two. Formulas, count that as three. Clicking the drop arrow here, count that as four. Picking the color, that's five. Click outside of it to make the color more obvious, that's six. So you might be saying, I could save myself, instead of six clicks how about just one? If we had some way to have that macro be available to us at all times.
We can do that by the way, with a special keystroke shortcut that we could set up. Another option could be, we could have a new button in our quick-access toolbar, typically located at the top of the screen. Some people have moved this below the Ribbon menu system if they preferred it. But either of those two techniques could work. We could eventually have lots of macros. You might categorize those as utility macros or little macros, and from time to time those could be really handy. And the impetus behind all of this is the idea that for whatever reason you have decided that you've used that sequence of actions so much, you need a shortcut for it.
You just want to eliminate the annoyance of having to go through those six clicks. Now, let's not limit ourselves to that idea though. In a different kind of worksheet we might want to do more things than just a simple application of color as we did here. Let's go back to this worksheet here called CurrentStaff. Imagine you're in charge of this, and what you do twice a week is you sort this by Employee Name, and there are a number of people in the organization who need that so let's say you sort it by Employee Name and you print five copies of it.
Then you sort it by Department and print a couple of copies. A few people need that as well. And then maybe you apply a filter and show only the Hourly people here, and you need to send a copy of that to the manager of hourly employees. And you can imagine also possibly sorting it and inserting subtotals to come up with a summary report. You need to do that every so often. You might even create a pivot table off of it. But if you've identified that as a series of steps that takes five, 10 minutes maybe, why not automate them? We can do that as well.
The process of creating a macro, for many people and for most people most of the time means the recording process. In other words, you will have identified the sequence of actions that you want to have automated, and you either will write them down or you remember them well. You'll turn on a feature called a macro recorder and then go through those very steps. The actions you take will actually be converted into a programming language called VBA, meaning Visual Basic for Applications.
And we'll learn more about that in later movies. The process ultimately is very powerful. And there are many, many things we can do with macros. You can almost make the statement that anything I can do in my regular use of Excel I could have done in a macro, and furthermore, there are additional things we can do in macros that we cannot do in our standard use of Excel. For example, with the if function in Excel, a powerful function, you can test the content of a cell but the result typically is another number or another formula.
You cannot, for example, say if a cell contains a certain amount make the row be green or anything like that. We could do that in a macro more easily. In standard Excel you'd have to use a combination of the if function and possibly conditional formatting. And so the entire process of macro begins with your recognition that you're repeating actions, you'd like to automate them, you'd like to make the process faster. You'll also recognize the idea that some kinds of macros that you need can be used or are likely to be used in any workbook, any worksheet.
At other times you're thinking only of the current worksheet. The macro that I suggested here about sorting and printing would be applicable only to this particular worksheet right here. But the other macro, for example, finding formulas and highlighting them, we could use that on any workbook. And so at different times sometimes the macros we record are specifically designed for one worksheet or one workbook, at other times utility type macros, let's say, we could be using in any number of different worksheets.
- Understanding macros security
- Running macros
- Using Visual Basic for Applications (VBA) to program macros
- Recording macros
- Expanding macros with the if statement
- Using For…Next, Do While, and Do Until statements to repeat action
- Joining two macros
- Streamlining macros