How to use macros in Excel: Enable the macro setting
While macros are a standard feature in Excel, you can’t actually see the option to create them until you enable the Developer tab. To do this you’ll need to launch Excel then select the File tab.
In here you’ll find Options near the bottom of the left hand menu. Click this and you’ll see a pop up box appear which includes the selection Customize Ribbon.
Click this, then in the next dialog box click on the Developer tickbox in the Main Tabs section and click OK.
Now you’ll see that the Developer tab is visible at the top of the page.
How to use macros in Excel: Creating a macro
Creating a macro can be done by either writing a Visual Basic script (tricky) or recording yourself working through a task (easy). We’ll be exploring the latter option for this tutorial. First off you’ll need to have your spreadsheet open and know the task that you want to complete. It’s a little like thinking about what you want to ask your smartphone before you engage Siri, Google Now, or Cortana. Just remember that the macro won’t copy the speed at which you work, only the commands, so you can relax and take your time. Before you begin you’ll need to change the security settings for macros. You’ll find these by clicking on the Developer tab then looking in the section on the left hand side. There you’ll find Record Macro, Use Relative References, and beneath these there’s Macro Security. Click this and then in the Macro Settings section select Enable all macros.
This option is accompanied by the warning that this setting isn’t recommended, and this is for good reason. Macros can be used to pilfer information or cause all kinds of havoc by neredowells, so ensure that once you’ve finished working with the macros you’re creating you immediately return to these settings and select the Disable all macros with notification option. Now you’re ready to begin recording.
How to use macros in Excel: Recording a macro
In our spreadsheet we have some figures that are regular expenses. We could copy and paste them by hand, but that would be wasted effort, especially if we were doing that multiple times, so instead we want to create a macro. To do this we go up to the Record Macro option in the Developer tab and click to begin. A dialog box appears in which the macro can be given a name, handy if you want to create several. There is also an option to create a Shortcut key for triggering the macro.
This Shortcut key is a combination of CTRL (already selected by default) and whichever alpha or numeric character is entered into the option box. You’re free to choose any key, but bear in mind that this will replace any existing Shortcut key in Excel when using this particular spreadsheet. Beneath the Shortcut key option is another important setting which determines where the macro is stored. The default is This Workbook, which means it will only work in the one currently open.
If you want to create a macro that will be available on any workbook in Excel then change the setting to Personal Macro Workbook. We’ll leave it on the default and click OK to confirm the choices. Now, from this point on, everything we do will be included in the macro. Initially it seems as if nothing is happening but if you look up in the top left corner you’ll notice that the Record Macro option is now Stop Recording. Excel is watching.
This first thing we do is highlight all of the figures in our column, then copy the values and paste them into the next five columns.
Now we click Stop Recording to end the macro. Of course you can do far more involved commands and routines than this, but for the purposes of demonstration we’re keeping it very simple. Testing the macro is a good idea at this point, so we delete all of the figures that have been copied into the empty column then use the Shortcut key we allocated to the macro, in this case CTRL+M. Hey presto! The figures have all magically reappeared and it only took the press of two keys.
How to use macros in Excel: Editing/Deleting a macro
If the results of creating a macro are not quite what you intended, or you want to remove it entirely, then go to the top left corner of the Developer tab and select the Macros icon. In here you’ll see that various macros available and options to edit or delete them. Clicking the Options button lets you change the Shortcut key, but if you opt for the Edit or Step Into buttons you’ll be taken to the Visual Basic editor where you can directly adjust the code if you wish.
It’s worth trying this out, as many of the commands will be familiar to you if you’ve created Formulae in Excel before. In fact if you’re interested in delving further into this side of things you should check out our guide on How to create basic formulae in Excel. That’s it, you’re now able to create and edit macros in Excel. Remember to experiment on test data, not actual important live information that you rely on, and make sure that you return the security settings to disabled when you’re finished. Martyn has been involved with tech ever since the arrival of his ZX Spectrum back in the early 80s. He covers iOS, Android, Windows and macOS, writing tutorials, buying guides and reviews for Macworld and its sister site Tech Advisor.
