How to record a macro in excel

You can save above 95% of your working time with excel, if your doing highly repetitive tasks in excel. This can be done easily with the help of record macro function in the Microsoft Excel, even if you don’t have any coding skill . And this article on ‘How to record a macro in excel’ will help you to learn how to do that.

Ok lets start.

If your data processing using excel have following characteristics, record macro function will be highly beneficial to you and you can start using this record a macro function of Microsoft excel.

• have well defined steps
• highly repetitive (that means the same data processing is done in every day or hour, with different data with same data format)
• same data format

Don’t worry if you don’t understand the above characteristics, it can be understood by following example.

I will be explaining this with an example

Example

Here I have three columns in excel

First column is ‘product code’, second column is ‘No of products to be produced’ and third column is ‘month’ as shown in the image below.

Now I need to process this above data to get an output as shown below.

As you can see the ‘No of products to be produced’ is organised Monthly.

You can see the below video for understanding steps I followed processing steps for getting this output from the initial data.

You can notice that I took 1 minute and 53 seconds to complete this task.

How to record a macro

Now I am going to record what I done manually in data processing as macros. Please watch the following video for knowing how to do that. Start and end of the video are very important.

Before recording a macro please save the excel file as macro enabled workbook.

How to save an excel file as Macro enabled work book

Follow following steps,

1. Click the ‘file’ menu in the top left corner of excel
2. Click ‘save as’
3. Click on ‘browse’
4. Select the location where do we want to save the file. Usually we click ‘desktop’
5. Rename the file name if you want
6. Select save as type as ‘Excel Macro-Enabled Workbook’
7. Click save

Benefit of recording a macro

You can see total time taken for manual processing of data was 1 minute and 53 seconds and by using macro it took only below 5 seconds to complete the task.

So the total time saved for doing it for one time is, 113-5, which is equal to 108 seconds.

So the percentage of time savings for our example is 108/113

which is equal to 95.57%

How this Macro enabled work book can be used for repetitive tasks

We will keep this macro enabled work book as master file and we will copy and paste the require data in to the specified columns in the master file. And we will click the ‘run macro’ button for processing the data. This is demonstrated in the following video.

Don’t forget to verify the output manually after running macro for first time, because there may be manual errors in recording the macro.

Its recommended to save this master file as read only, so that the macro and cells will not be changed in future while using it. This can be done by clicking ‘Read-only recommended’ box while saving an excel file, as shown in the picture below. This general option window can be accessed by clicking ‘tools’ button and then ‘General options’. ‘Tools’ button can be seen to the left of the save button, when we save as an excel file.

An advice while recording a Macro

Data format should be exactly same with the data format what we have used for recording a macro, when we are using new data over and over again. If anything is changed other than data values, the desired output will be completely wrong and we may not be able to recognize that error.

Also I recommend you to do some verification of the output data after running macro every time. For example, you may have noticed in the above videos, that I am checking whether the sum of output data is same or not with the input data. If those are not matching, there will be some error.

So, like this way you may also need to check some points, after running macro every time.

Thank you for reading. You may also check our article on Password Protect an Excel File

If you know any subject that can be related to manufacturing industry or industrial engineering, you can earn some income by becoming article contributor of this website. For knowing more about it, please visit Join us page.

You don’t need to have any experience in article writing, just knowledge on the subject is needed.

Also you can know more about our team of article contributors by visiting the about us page.