Recording Macros

In my previous article I described the process I go through to prepare an Excel file for viewing. At the end of that article I laid down the challenge to record a macro for the steps that would allow you to perform all of the described actions with the click of a button. In this article I am going to show you how to record a macro.

Macros

Macros are a series of actions that have been recorded and stored for later use. They can be run using a keyboard shortcut or tied to a button that the user clicks. Running the macro results in all of the steps that it includes being performed automatically. The intention is to make life easier by reducing the number of keystrokes or actions one must take to complete a given task. This can reduce tedium and seriously speed up your workflow. In the past month that I have been using the macro based on the steps explained in the previous article I would have saved probably one or two hours.

Developer Tab

We will be using MS Excel to record our macro, specifically Office 365. Macros have been around a long time and the process will be fairly similar in earlier versions of Excel. To record macros you need to have the Developer tab available (it is hidden by default). Show the Developer tab by following these steps:

• On the File tab, go to Options and then Customize Ribbon
• Under Customize the Ribbon, select Main Tabs from the drop-down menu and select the Developer checkbox.

You will now see the Developer tab along your top menu. This is where we will record our macro from.

Preparation

Recording a macro involves performing each required action while Excel records those actions. Every click that you make in the file while recording the macro will be saved. It is a good idea to plan exactly the steps you want to record, and to practice these a few times, before actually recording your macro. Consider the following:

• What are you trying to achieve? What is the desired end state of your Excel file once the macro has finished running?
• What actions do you need to take to achieve this desired end state?
• What order do the steps need to be completed in?
• Where do you need to place the pointer for each action?
• Will you use the menu items to complete actions (e.g. setting text to bold) or keyboard shortcuts?

Recording the macro

You have planned your actions and you are now ready to record your macro. Head to the Developer tab and click Record Macro.

Provide a name for you macro (that makes it clear what the macro does). Under Store macro in: select Personal Macro Workbook. Storing the macro here will make it available in any Excel file you open (on this device) rather than just the file you are currently working in. Provide a description so that you know what the macro will do. You can add a Shortcut key if you like. Click OK when you are finished.

If you look at the bottom left of your spreadsheet you will see a small square. Hovering over the box will tell you that a macro is currently being recorded. Every action you take now will be recorded. Repeat the actions you practiced to record the steps you want your macro to complete. Once you have finished you can stop the recording by clicking that square at the bottom left of the screen or the Stop Recording button in the Developer tab.

Making the macro accessible

Congratulations, you have recorded your macro! You may be a bit underwhelmed though, as once you click the stop button not much happens other than the stop buttons reverting back to record buttons. But don’t worry, your macro is now sitting in your Personal Macro Workbook waiting to be used. If you assigned a keyboard shortcut to the macro, using that shortcut will now trigger the macro. I find it more useful to add the macro as a button to my Home tab. In the image below you can see a couple of example macros that I have pinned there. Any time I want to use the macro, I just click on its button.

Create a group

The first step is to create a new group to hold your macros, which I have call ‘My Macros’ in the image above. Follow these steps to create the new group and pin it to your Home tab:

• On the File tab, go to Options and then Customize Ribbon
• Click on Home in the right panel to select it and then click New Group at the bottom of the dialogue box
• With the new group selected, click Rename at the bottom of the dialogue box
• Enter a relevant name for the group and add an icon if desired.

Add your macro to your new group

You will now have a new group on your Home tab (My Macros in my example). The next step is to add your new macro to this group. From the Customize Ribbon menu again complete the following steps:

• Under the Choose commands from section select Macros from the drop-down menu

• Click on your new group in the right panel to select it (My Macros in my example) and then click on your new macro to select it from the left panel
• Click Add to add your macro to the group
• Click on Rename and give your macro a more descriptive name. This is what will show on the tab. You can also assign it an icon.
• Click OK to save your macro to the tab.

And that’s it! Your macro is now available on your Home tab and by clicking on it the macro will run and perform all those steps you previously had to perform manually.

Hopefully this article has helped you to record your first macro. You could try recording the steps described in the previous article to make it easier to view data in a spreadsheet. Have a think about the actions you regularly perform when working on a spreadsheet and make this the subject of your next macro. Comment below to tell us how you have used a macro to make your workflow more efficient.

• About
• Latest Posts

Jeff Mitchell

P&C Reporting and Data Analyst at Fisher & Paykel Appliances

Jeff is passionate about the role of learning and development, and has a specific interest in how people and organisations can be developed in order to achieve their potential. Jeff has a keen interest in information technology and specifically data analysis and the e-learning space.

Latest posts by Jeff Mitchell (see all)

• Version Control – Git GUI – 7th March 2023
• Version Control – Git Command Line Tool – 7th February 2023
• Version Control – Creating Repositories – 7th January 2023