Join Dennis Taylor for an in-depth discussion in this video Exploring the need for macros, part of Excel 2016 Essential Training.
- One of Excel's most powerful features is called a macro. A macro is a way to automate your work. Let's start with a simple idea. Here's a worksheet called HR List. It's in the file called 15 - Macros. From time to time, you flag certain cells here. You're an HR Manager. You've got some other people who work for you who will do the editing and the adjustment of these, and you highlight a cell, and you make it yellow maybe. Maybe add a red Font, and, admittedly, maybe this is overkill, you make it bold, you make it italic.
You might even want to add a border. That's five or six steps. I wasn't really counting. For whatever reason, you like that. You'd like to use it again down here. Well, we could copy the format, but you might want to use it tomorrow or the next day or at different times. Wouldn't it be great if we could simply click somewhere, press a keystroke shortcut, have this happen all over again? Now, admittedly, a small series of actions, but the idea behind a macro is you've identified a sequence of steps, you'd like those to be faster.
Now, ultimately, a macro isn't just about short sequences. It can be about a sequence that might involve 20 steps or 40 steps, and it certainly could involve major commands. Consider in this worksheet, for example, different people need to see this at different times, and three or four times a week maybe, you go through the process of updating this list, maybe sorting it by Status, saving a copy of that, maybe printing, maybe not, sending it to a few other people, and then, immediately there after that, perhaps you run a filter to expose only the Hourly Names, and you print that or save that, send it to somebody else, and so on.
You might have identified a 25-step sequence. Now, after doing that for a few weeks or so, you say to yourself, "If I take exactly the same steps, "there ought to be a way to say, 'Let's do this all at once.'" In Excel, the macro concept begins, first of all, with the idea that you've identified a sequence of steps that you would like to automate. If we could press a keystroke shortcut and have all that sorting and filtering and printing and saving be done within a few seconds, that's going to save us some real time.
Instead of 10 or 15 minutes three or four times a week, how about a few seconds? In Excel, macros are typically created after you've identified the sequence, you turn on a feature called a macro recorder. You go through the actual steps. All those are being stored and saved, and then, at a later time, you can press a keystroke shortcut or maybe by way of a new button up in the Quick Access toolbar, you'll press that button, cause the macro to happen, maybe even an icon on the worksheet, a symbol, an icon, a picture even.
Here's a different worksheet here called PricingSheet. It's a bit of a mess, but it's effective. It also reminds you that you open other worksheets from time to time. You're the worksheet troubleshooter, perhaps, in your organization. It sure would be handy if we could identify which cells have formulas, and you can. If, on the Home tab, you go to the Find & Select button on the far right side, there's an option called Formulas. It highlights all the formula cells. If you're going to be working with a worksheet like this, it sure would be handy if we had color applied to these or some other visual feature, so after highlighting these cells, maybe go to the Home tab on the left side here, use the Fill Color bucket, apply background color, possibly even a font or a border feature, if you wanted to.
If you find yourself performing these actions, this, too, could be turned into a macro. It's only five or six steps, but if you use it frequently, and particularly, if you find it annoying, why not make it happen fast by way of a keystroke shortcut, possibly? As we look at certain buttons in the Ribbon menu system, we might even begin to say, "These are like little macros." Now, I can't imagine anybody using Excel who doesn't know what B means, but if you didn't know, you might go to Format Cells, maybe by way of right-click Format Cells, or you could press ctrl + 1.
If you wanted to make a cell be bold, you could go to the Font tab and click Bold and click OK, but who really does that? Instead of those five or six steps, why not use B? Maybe I want this to be Bold. Click B. We don't call that a macro, but in a certain sense, it is. In another example, back on the HR List here. When someone leaves this organization, let's say we leave the Name in place for a while, but we do make a visual change. We highlight the cells in question, and then we apply Strikethrough.
How do we get to that? Well, it's by way of Format Cells. We could right-click and choose Format Cells, control one, or this button up in the menu system, Font right here, the dialogue box launcher. Click that. In any of those three ways, we'll come to Format Cells. We might or might not see the Font tab, but if we do, we can apply Strikethrough. Click OK. Now, a bit later, we might find out there's a keystroke shortcut for Strikethrough.
Highlight these cells, press ctrl + 5. That removes Strikethrough or it applies it. Now, we don't call these macros, but in a certain sense, they are. We're taking a single action instead of multiple actions, and ultimately, macros, a really powerful tool, can be sophisticated, and they might encompass maybe 50 steps, 100 steps. It's all about the idea. We want a series of actions to be, essentially, turned into one action. It doesn't mean that whatever the actions are are going to be instantaneous, although in some of the short macros I've suggested, they will be, practically.
At certain times, some macros might take two or three minutes, particularly if they involve a lot of manipulation of data, but the key idea is you've identified certain actions that you perform frequently, you'd like to automate them. That's what we're going to be doing by way of macros.
- 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