Creating 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 reduce a lot of activities to a single command.

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 ten seconds. These tools no doubt reduce 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.

(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-

(Link to download the Excel file used for creating Macro:

watch the short video and learn Macros

To convert this the activities done are-

  1. Merging of cells in first row

  2. Putting background colour as blue

  3. Changing Font colour as white

  4. Making it bold

  5. Increasing the font

  6. Converting Order date from number format to date format

  7. Background colour of second row as yellow

  8. Creating borders for all the cells

  9. Creating bold borders for outer boundaries

  10. Putting a sum formula for revenues

  11. Calculating percentage of revenues

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.

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 the 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.

Happy Learning!!

6 views0 comments

Recent Posts

See All