Excel macros let you perform complicated tasks, such as manipulating PivotTables quickly by recording the steps and playing them back when needed. In this video, we’ll show you several ways to run your macros, so you can pick the one that works best for you.
- [Instructor] Excel macros let you perform complicated tasks such as manipulating pivot tables quickly by recording the steps and playing them back when needed. In this movie I will show you a good way to run your pivot table macros quickly. My sample file is RunMacro_08_02. And that is a macro-enabled Excel workbook that you can find in the chapter eight folder of the exercise files collection. I just opened this file, and you can see that there is a security warning dialog box indicating that macros have been disabled.
If I want to continue in the workbook without enabling macros, then I can click the close button here, the close this message button at the right side of the message bar. However, what I really want to do is to enable the contents so I can work with the macros as I go along. So I will click the Enable Content button. If for some reason the Enable Content button doesn't appear for you then your macro settings might be at fault. If you're sure you want to run macros and it's okay with your company's information security policy, go to the file tab of the ribbon, and then click options to open the Excel options dialog box.
At the very bottom of the category list click Trust Center. And then on the next page click Trust Center Settings. And here you have different elements that you can change. We're looking for macro settings, so I'll click that. As you can see there are different levels of macro settings that you can have for your workbook. The default, which is the setting when you first install Excel, is to disable all macros with notification. That's why we saw the message bar.
The first level is disable all macros without notification. That doesn't display a message bar. The second is the one you saw. Third is disable all macros except digitally signed macros. And that would require a digital signature and a higher level of security. And then finally you have enable all macros. And when this dialog box says it's not recommended, they're not kidding. Any programming language can be used to create problems for computer users in the forms of viruses, Trojan horses, and so on.
So unless you are absolutely certain that you want to work with macros, don't enable them. But in this case we did expect the workbook to contain a macro so we can leave everything as it is. I'll hit cancel once, cancel again. And we're back in the workbook. Now that macros are enabled there are a couple of ways that we can run them. Before I run it I will change the organization of my pivot table. And I'll do that just by taking the SKU category out.
So now we're just down to wind harvesters. To run the macro from the macros dialog box I'll go to the view tab of the ribbon. And then click the macros button at the far right. And that displays the macro dialog box. And I have my existing macro in there called wind harvester. If I want to run the macro I can just make sure it's selected and click run, and when I do Excel creates the pivot table position that I recorded. However, going through the macros dialog box is a pain. It takes extra effort.
So what you can do instead is to create a button, simply a shape with some text in it, and then assign a macro to it. To start that process I will go to the insert tab of the ribbon and then I'll click the illustrations button. You might see this as a separate group depending on your screen resolution. Then I will point to shapes, or rather click shapes. And I'll select a shape. In this case I'll just make it a rounded rectangle. So I'll click there and I will draw the rounded rectangle in cell C1.
One reason that I like creating shapes for this type of application in the first few rows, is that Excel will use row 1 cells A1 and A2 in this case, for filter fields in your pivot table. And you never want to put any of these shapes to the right of the pivot table because you never know when by adding subcategories or multiple value fields if the data will extend over a cell where you've created your shape. So I have my shape positioned over cell C1 and with it selected I can just type in some text.
I will call it wind harvester. And I'll resize the shape for the moment. So there we go. There's that. That's a little larger than I want. So I'll go to the home tab of the ribbon and I will decrease the size of the text until it looks about right, resize the button, take it to the edge of C1, and that's good enough for now. All right, now I can assign the macro to the button.
So I'll right click the shape that I just created. And then about three quarters of the way down the button list or the shortcut menu list I'll click assign macro. That displays the assign macro dialog box. And I don't want to create an event, which would be the click macro that we see there. Instead I want to assign it wind harvester. And click OK. And my macro is now assigned to the shape. So I will click inside the pivot table.
And I will put category below SKU just to change the configuration of the pivot table. And now, to run the macro by clicking the shape, I'll go up to the shape and you can see that my mouse pointer changes to a pointing hand when it's in position. And I'll click the shape and Excel runs the macro. One final point, if you want to edit a shape that has a macro assigned to it, hold down the control key, click the shape, and you're able to edit it. So for example, I could go to the format contextual tab and change any element of the shape that I wanted.
Pivot table macros help you recall positions quickly. Adding shapes to your worksheet is one great way to recall your pivot table positions during your presentation.
Note: This course was recorded in Office 365. However, anyone using Excel 2019, Excel 2016, and even prior editions of Excel should be able to follow along with the course contents.
- Determine when it is necessary to click Defer Layout Update in the fields area.
- Recall the easiest way to create a PivotTable from external data that cannot be imported directly as an Excel table.
- Explain the configuration that takes up the least width on the screen while keeping the Fields List area visible.
- Identify the mistake users often make when creating filters.
- Explore the function of slicers.
- Review the use of data bars with a PivotTable.
- Name the first step in viewing all the conditional rules applied to a PivotTable.
- Recognize the appropriate way to save a macro.