This guide is also available in PDF format:
- Beginner's Guide to Recording Macros (PDF version)
- Beginner's Guide to Recording Macros (PDF version, with screenshots)
Table of Contents
Introduction – What is a macro?
Why use a macro?
How to record a macro
Additional ways to run a macro
Step 1 – Ensure you have the Developer tab enabled
Step 2 – Click on the "Record Macro" button and fill in the details
Step 3 – Perform the tasks you wish to commit to the macro
Step 4 – Click the "Stop Recording" button
Creating a button within the spreadsheet
Creating a button on the ribbon
Introduction – What is a macro?
Simply put, a macro is a list of actions which Excel carries out automatically when the macro is run.
Macros can be created in two ways:
1) Macros can be "recorded", which is where Excel makes a note of all the actions you perform (whether selecting cells, copying and pasting data, formatting the cells etc. – anything you can do in the normal course of Excel can be done by a macro).
2) For more advanced users, macros can be programmed by hand to perform more complex tasks. This requires knowledge of the VBA (Visual Basic for Applications) programming language.
For the purposes of this tutorial, we will only be focusing on #1 which does not require any programming knowledge.
Why use a macro?
Macros can be as simple or as complex as the user requires, so they can be used for a wide variety of purposes. Let’s say you have a daily task which involves the following:
1) Downloading a text report which you open in Excel
2) Using the "Text to Columns" function to put the data into columns
3) Making the headers bold with a grey background
4) Applying thin black borders to all the data
5) Creating a pivot table to summarise the information
6) Saving the spreadsheet on your desktop so you can email it to a colleague
Now the time it takes to do all of these tasks would of course depend on the individual’s skill with Excel and the size of the data they have to work with. With that in mind, for the purpose of this example, let’s say the above process takes 20 minutes to complete.
A macro could automate steps 2 to 6 of the above, turning a 20 minute task into a 30 second task.
Now a daily saving of 20 minutes might not sound like much, but what if you had a routine Excel task that takes 2 hours to complete? Even this could potentially be turned into a macro which can complete the task within a couple of minutes at most. It is situations like this where recording macros can simplify routine tasks and free up a lot of your time.
Additionally, when these tasks are being performed automatically by a macro, there’s the added element of accuracy. As long as the macro was recorded correctly then there will be much less room for human error – something that even the most experienced users fall prey to occasionally.
How to record a macro
Now that you know what a macro is and why it might be worth using one, let’s get into the details of how to actually record one.
This section will summarise the steps required, with more detailed instructions in the sections below.
1) Ensure you have the Developer tab enabled
2) Click on the "Record Macro" button and fill in the details
3) Perform the tasks you wish to commit to the macro
4) Click the "Stop Recording" button
The macro has now been recorded. Now, in the future, any time you need to repeat the steps that you performed when recording the macro, you can call up the macro list by clicking on the "Macros" button.
Choose the macro you recorded from the list and click the "Run" button. Excel will now perform the precise steps you performed when recording the macro.
Additional ways to run a macro
In addition to the method shown above to run a recorded macro, it is also possible to create a button within a spreadsheet that runs the specified macro when the button was clicked. Alternately, you may even create a button on the ribbon above which runs a macro when clicked.
In order to create a button within the spreadsheet, you need to do the following:
1) Click on the "Developer" tab, click the "Insert" button and choose the Button icon under Form Controls
2) Drag the area on the spreadsheet in which you want the button to appear
3) Choose the macro you want to assign to this button, then click OK
4) The button will be created and clicking on it will run the chosen macro. You may also rename the button if you wish.
In order to create a button on the ribbon at the top of Excel, you need to do the following:
1) Right-click anywhere on the ribbon and choose "Customise the Ribbon..."
2) Click the "New Tab" button
3) Optionally, select "New Tab (Custom)" and click on the "Rename" button to give it a suitable name (e.g. "Macros")
4) Go to the drop-down box labelled "Choose commands from:" on the top-left corner of the screen and choose "Macros"
5) Select the macro you want to create a button for, then click the "Add >>" button
6) Optionally, click the "Rename" button to provide a caption for the button and choose the icon you want for this button
7) The button will be created and clicking on it will run the chosen macro
Step 1 – Ensure you have the Developer tab enabled
In order to record an Excel macro, you need to have the Developer tab enabled.
If this tab does not appear for you, then it can be easily enabled as follows:
1) Right-click on any of the tabs and select "Customise the Ribbon..."
2) Ensure there is a tick in the "Developer" checkbox and click the "OK" button
Step 2 – Click on the "Record Macro" button and fill in the details
1) Click on the "Record Macro" button and choose a name for your macro. It must start with a letter and may contain letters, numbers and underscores but no spaces or special characters.
2) You may optionally assign a shortcut key to the macro. This always starts with the Ctrl key and may optionally include the Shift key also. Finally it must end with a letter. Examples may include Ctrl+O, Ctrl+Shift+W, Ctrl+X or any other combination you see fit.
3) Next you need to specify whether the macro will be stored in the current workbook, a new workbook or the "Personal Macro Workbook". The Personal Macro Workbook is a special spreadsheet that is created when you assign your first macro to this option. It opens in the background and is hidden from view, but it enables you to run your recorded macro in any spreadsheet you have open. I would suggest that you choose the Personal Macro Workbook option unless you have a specific reason for choosing one of the other options.
4) Finally, you may choose to write a description for the new macro. This step is completely optional.
5) Click "OK" to begin recording your macro
Step 3 – Perform the tasks you wish to commit to the macro
At this point, every action you perform in Excel will be recorded and assigned to this macro. If you are recording a macro to automate a routine task, then simply perform that task as you normally would.
The only advice I can offer here is to be careful – Excel records your actions exactly as you perform them. Therefore if you make a mistake during the process, Excel will record the mistake itself and any corrective action you take afterwards. If the worst comes to the worst, it is possible to stop recording, delete the recorded macro and start over.
Step 4 – Click the "Stop Recording" button
When you have finished the task that you wish to record, click on the "Stop Recording" button (this has replaced the "Record Macro" button).
Creating a button within the spreadsheet
1) Click on the "Developer" tab, click the "Insert" button and choose the Button icon under Form Controls
2) Drag the area in which you want the button to appear
3) Choose the macro you want to assign to this button, then click OK
4) The button will be created and clicking on it will run the chosen macro. You may also rename the button if you wish.
Creating a button on the ribbon
1) Right-click anywhere on the ribbon and choose "Customise the Ribbon..."
2) Click the "New Tab" button
3) Optionally, select "New Tab (Custom)" and click on the "Rename" button to give it a suitable name (e.g. "Macros")
4) Go to the drop-down box labelled "Choose commands from:" on the top-left corner of the screen and choose "Macros"
5) Select the macro you want to create a button for, then click the "Add >>" button
6) Optionally, click the "Rename" button to provide a caption for the button and choose the icon you want for this button
7) The button will be created and clicking on it will run the chosen macro
All media hosted on this website is copyright © Ben Lauritson 2006 - 2024 unless explicitly stated otherwise.
Disclaimer: Some sections of this website contain strong language and/or adult themes. By continuing to browse, you are waiving any right to be offended and waiving any liability on my part for the consequences thereof. If you are not old enough to understand this disclaimer then you are not old enough to continue browsing. Have a nice day.