Macros in Excel For Beginners
Macros are very interesting feature in Excel. Macros are super useful for any repeat activity. By creating Macros, we can complete a lot of activities to a simple single command activity.
We will see here an example where we will use Macros to cut short the time required to create a report from ten minutes to a matter of seconds. Macros no doubt reduces time, but at the same time increase efficiency by reducing any chance of errors.
First of all we will see how to get Macros in our Excel workbook. The Macro command is present in Code group in the Developer Tab as shown in image below.
(To download the Excel file click on link https://bit.ly/3HbTnnU)
(In case Developer Tab is not present in the menu bar of your Excel workbook, please read section at the end of this article to get Developer Tab.)
Let’s see an example and understand how Macros can be used to make our jobs super quick and efficient. In the image below we see some raw data on the left side and it has to be formatted and converted into report as shown in the right-hand side
To watch the short video on Macros
To convert this data into this report the activities to be completed are-
To accomplish these activities, it will require few minutes, but with help of Macros we will be able to do all this by clicking one button.
There can be any number of activities, here we are taking 11 activities to keep it simple and short as we just want to explain how to achieve this.
To create a Macro for creating this report, we first open the Excel worksheet where we have the raw data and we click on developer Tab.
In Code group we see Record Macro button.
We have to click on record Macro.
As soon as we click on Record Macro a new window opens up.
Recommended by LinkedIn
In this new window, we can give a name to this Macro. We give our Macro name as Macro3.
We can also assign a shortcut to this Macro. To our Macro we assign shortcut as Ctrl F.
Here we have option to store this Macro in Personal Macro workbook or a New Workbook or in this workbook only.
There is a box for description where we can briefly describe this Macro, so that in future we can easily remember the functionality of this particular Macro. We type in description as “Format Table”.
After this we click on OK, and we can see that in place of Record Macro Button we now get the Stop Recording Button. Now we can carry out all the activities we have to do to format the table. Here in our case, we have eleven activities. Once we have completed the formatting, we have to click on Stop Recording button and our Macro is created.
Now if we have to prepare this report again next day, we just have to run this macro and all the eleven activities will be completed in few seconds.
Let’s see how we can run a recorded Macro. To run a recorded Macro we click on Developer Tab. In the Developer Tab ribbon, we click on Macros and a new window opens up. In this window we have all the recorded macros. We click on our Macro 3, we can see at the bottom we have the description as Format Table, which we mentioned in description while recording our Macro 3.
We select Macro 3 and as soon as we click on Run Button, our data gets formatted in a beautiful table as shown in image below.
We can also run our Macro using our shortcut CTRL F.
How to Get Developer Tab in Menu Bar
We can see here that Developer Tab is not present in the menu bar. Right click anywhere on the menu bar and you will get a window which has an option to Customize the Ribbon.
We click on this option and the window as shown in image below opens up. Here we can see under Main Tabs the Developer and Add ins options as unchecked. We have to check these options and click on OK.
As soon as we click on the OK button, we see Developer Tab in the menu bar.