Excel Tips - Tip 35 (Using Macros)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

Macros provide an ideal way to save time on predictable, repetitive tasks as well as standardize document formats – many times without having to write a single line of code.

What Does It Do ?

Macros are incredibly powerful and can do pretty much anything your imagination can conjure. As a (very) short list of functions you can do with a macro:

·         Apply style and formatting.

·         Manipulate data and text.

·         Communicate with data sources (database, text files, etc.).

·         Create entirely new documents.

·         Any combination, in any order, of any of the above.

How to use it ?

We have a sample of data as shown in figure below :

Now we need to create a macros for the repetitive command that we need to use.

To create a macro, go to View > Macros > Record Macro. As shown in the below image. From there we need to select Record Macros.

As we click on record macros a new dialog box will appear as shown in images.

As soon as you click OK, our Macros command start working.

Now we have to give command that we need to record it.

Examples of Command that we uses :

·         =SUM(B2:F2)

·         =AVERAGE(B2:F2)

·         =MAX(B2:F2)

·         =MIN(B2:F2)

You can also use other command like formatting ( Bold, Italics etc)

Once the above command used in the one cell, now use Drag and Drop option to copy all the formulas to all the desired cell.

Once we complete all the commands then we have to go to Macros again and click on Stop Recording.

Once the macros stop, then Congratulations – you have just created an Excel macro.

Now Select the all by pressing Ctrl+A and then delete the all text. Now save the file as macros enable as shown in image.

Now close the file. Now open the new file that we have just saved in our PC.

Enable the macros as shown in figures

Click on options and select Enable as shown in images

Now Copy paste the data as shown in figures 1 which is our base DATA file.

Now Run macros as shown in figures

The all command will automatically filled as per our command used while Recording Macros.

That’s how we can use Macros.

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456