How to Record a Macro in Excel? In 6 Easy Steps (For Dummies)
Note: This tutorial on how to record a macro in Excel is suitable for all Excel versions including Office 365.
Macros can be very intimidating to both Excel experts and newbies alike. But, that should not prevent you from using them altogether. Let me introduce you to the macro recorder in Excel. The Macro recorder can record a specific set of tasks and convert them into VBA codes. You can then execute these macros to automate tasks or customize them further to suit your needs.
It is also a great tool to explore and learn Excel VBA, especially if you are stuck with a VBA code.
In this tutorial, I will teach you how to record a macro in Excel in just 6 simple steps!
You’ll learn:
- What is an Excel Macro?
- How to Record a Macro in Excel?
- How to Run a Recorded Macro in Excel?
- How to View a Recorded Macro’s VBA Code?
- How to Record a Macro in Excel Using the Relative Reference Mode?
- Important Limitations of the Macro Recorder
Related:
How to Use the Chi Square Test Excel Function? 4 Easy Steps
The Excel SMALL Function – 3 Best Examples
How to Use e in Excel Using the EXP Function? 2 Easy Examples
What is an Excel Macro?
Before we begin, let us first understand what is an Excel macro. A macro is a code written in the Visual Basic programming (VBA) language. And here’s where it gets exciting. You can write any set of valid instructions in a macro and run it in Excel’s VBA editor to execute them.
The possibilities are endless with macros. For example, you can write a macro to delete all hyperlinks in a workbook, merge multiple sheets, and so on.
One might assume that these macro codes are very difficult to write manually. But don’t worry, Excel has an inbuilt macro recorder. It will record and automatically convert your activities into executable macros.
You can then use these macros to automate repetitive tasks or modify them to create even more powerful macros. Isn’t this exciting?
In the next section, we will cover the step-by-step method to use Macros efficiently!
How to Record a Macro in Excel?
- As the first step, activate the Developer tab in Excel, if you haven’t already. You can find the detailed instructions here.
- In the Developer tab, find and use the Record Macro button in the Code group.
- The Record Macro dialog box will now appear. Here name your macro. Keep in mind that, there should be no spaces inside the macro’s name. You can use an underscore ( _ ) instead of a space.
- You can also assign a shortcut key to run this particular macro. This is optional. Please note that this shortcut will replace any other existing shortcut for the same key combinations.
- In the Store Macro In section, specify where this macro needs to be applicable. For example, this workbook, New workbook etc.. This will ensure that the macro is saved as part of the Excel file even after closing and re-opening it.
- Now, click on OK. Excel will immediately start recording your on-screen actions.
In this example, I will find the sum of range A2:A10 in cell A11 and record it using the macro recorder. Click on cell A11 fill the formula SUM(A2:A10) and hit Enter.
Now, hit the Stop Recording button in the Developer tab. It will be at the same place, where you located the Record Macro option.
Congratulations! It is that simple. That’s all it takes to record a macro in Excel.
Also Read:
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Indent in Excel? 3 Easy Methods
How to Use the Format Painter Excel Feature? — 3 Bonus Tips
How to Run a Recorded Macro in Excel?
Even though this macro is simple, you can always build on the basic concept and create complex macros as per your requirement. Now, let us test this macro out, by executing it.
- First, delete the value in cell A11 and clear the formula.
- Now, locate and click on the Macros option in the Code group of the Developer tab.
- Select the macro you just created and click on Run in the Macros dialogue box.
- Alternatively, you can use the keyboard shortcut if you assigned one, to run the macro.
Sit back and watch as the macro does its magic. Your cell A11 will automatically get filled with the formula SUM(A2:A10) and display its value.
How to View a Recorded Macro’s VBA Code?
To view the VBA code version of a recorded macro, hit the keyboard shortcut Alt+F11. This will open Excel’s built-in VBA editor.
Your recorded macros will be listed inside the left-hand pane of the VBA editor, under the Modules section.
Double-click on the module to view the recorded macro’s VBA code.
In our example, the following VBA code was generated for the recorded macro. Keep in mind that, we used it to record finding the sum of the range A2:A10
Note: Any line that starts with an apostrophe (‘) is considered as a comment in VBA. Comments are only for the user’s ease of understanding and will not be executed.
Any VBA code in Excel starts with Sub and ends with End Sub. Here, Sub is the short form for a subroutine.
I recommend you record multiple macros for various simple tasks and see how this VBA code changes for each of them. That way you will rapidly gain an intuitive understanding of VBA codes in Excel.
How to Record a Macro in Excel Using the Relative Reference Mode?
Let me quickly recap absolute vs relative reference in Excel, in case you didn’t know. An absolute reference is a fixed reference that permanently fixes the position of the cell being referred to.
For example, $C$2:$C$4 is an absolute reference that always refers to the range C2:C4, no matter what happens to the formula containing it. If you move or drag the formula SUM($C$2:$C$4)
On the other hand, C2:C4 is a relative reference that temporarily points to the range C2:C4 but moves along with the formula’s location. If you drag the formula SUM(C2:C4) from C5 to D5, it’ll change to SUM(D2:D4).
Now, the same concept applies to recording macros as well.
By default, all recorded macros will be in absolute reference. That is, the cell references will be locked in the VBA code.
But, you can make it a relative reference macro by following these steps.
- Select any cell which can be used as initial reference.
- Click on the Use Relative References option just below the Record Macro button in the Developer tab.
- Record your macro as usual. Please note that, any operations you make will be recorded with reference to your initial reference cell.
- Run the recorded macro after selecting an entirely different cell. Note that, the relative reference macro now calculates the values based on the newly selected cell’s position.
Important Limitations of the Macro Recorder
- The macro recorder cannot be used to create custom functions. Surely, you can modify a recorded macro’s VBA code. But, it is not equalent to the versatility of a custom VBA code.
- It cannot execute a command without the user selecting an object. For example, it cannot switch to another sheet and delete a particular range without the user manually doing so, while recording it.
- It cannot take any external arguments including other events. That is, each recorded macro is independent. For example, you cannot record a macro to execute certain commands when a particular condition is triggered ( Do this task when opening files, double-clicking, etc).
- Similarly, it cannot check for global or external conditions. That is, you cannot use it to run IF & ELSE conditions to run commands based on certain criteria. For example, you cannot record a macro to check if the total sales in year 2020 is more than 200 million USD and run a command if that is true.Instead you can record a macro to add an IF formula in the sheet to check the criteria.
- Recorded macros cannot execute commands in repeating loops like Do While, Do Until, For Next etc.
Suggested Reads:
How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps
How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide
How to Use the NPER Excel Formula: 2 Easy Examples
Closing Thoughts
In this guide, I have shown you how to record a macro in Excel in six simple steps.
The more you use the macro recorder in Excel, you will appreciate the value of VBA codes better. I recommend that you begin by automating simple tasks like filtering a particular column or sorting a particular range.
Macros certainly have a very rewarding learning curve and the more you practise recording macros, the easier it will be to use them.
If you have any questions about this, or any other Excel feature please let us know in the comments. We are always happy to help!
Want more high-quality guides for Excel? Check out our free Excel resources centre.
Click here to access in-depth Excel training courses and master in-demand advanced Excel skills.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
