Recording macros in excel is a method where excel records our every step and then it is saved, when required excel automatically runs the same steps performed by the user in the exact same fashion, recording macros can be accessed from the view tab and in the macros section we will find the option for record macros.
How to Record Macro in Excel?
A macro is a recording of our routine steps in Excel that we can replay using a single button, which with time become boring and tedious for us to perform. Examples are
- Preparing a sales report in the required format for management.
- Formatting the data in the required data format so that the same can be uploaded on the required site.
To record a macro in excel, we can use either View Tab or Developer Tab.
Steps to Create Macros in Excel
Let us look at the steps to record macros in excel using an example.
Suppose, we need to format a specific report on a daily basis and formatting takes a lot of time like applying borders, making headlines bold, apply background color to header row and color, etc.
Now before starting our job to format report, we will start recording. To start recording macros in excel the steps, which we will perform,
- Step 1 – we need to go to Developer Tab -> Code Group -> Record Macro Command.
- Step 2 – We will rename the macro with ‘Formatting’. There should not be any space while naming the macro.
- Step 3 – We can assign the shortcut key to Macro so that we can also just press the single shortcut key to run the macro.
- Step 4 – As we want that this macro should work only in this workbook, then we will choose ‘This Workbook’ for Store macro in.
- Step 5 – In ‘Description‘, we need to write properly about what macro does.
Now as the ‘Record Button’ has changed to ‘Stop Recording’ in code group.
It indicates that whatever we will do now, will be created in the macro. We need to activate ‘Use Relative References’ if we want that if we have selected any other cell than a now selected cell, then the macro should work accordingly.
After recording all the steps required for formatting, we will ‘Stop Recording’. Now we can use the shortcut key ‘Ctrl+Shift+F’ to apply recorded formatting.
Suppose, we have some data to be formatted in the same way.
We just need to press ‘Ctrl+Shift+F’ for formatting instead of performing many commands after selecting ‘A1′.
However, there is a limitation of the macro recorder that is the code of macro recorder is full of code that is not really needed.
Things to Remember
- If we download an Excel file with .xlsm extension (Macro enabled Excel sheet), then we must confirm that it came from a trusted source and if we create any file with macros, we should save the file with .xlsm extension.
- While creating a macro in excel, do not use the ‘AutoSum’ function to get the sum, as it will hard code the formula. Instead, type formula with a single dollar sign, such as =SUM(E$2: E10). When this is done, the macro recorder records the first E$2 as a fixed reference and starts the SUM range directly below the Row 1 headings. Provided the active cell is E11, the macro recorder recognizes E10 as a relative reference pointing directly above the current cell.
- We should activate ‘Use Relative Reference in excel‘ before creating the macro in excel
This has been a guide to Record a Macro in Excel. Here we learn how to create a Macro in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –