How to Create Macro in Excel [Tutorial with Example]

A macro is a mini-Program that you create within an Excel worksheet. It is nothing more than a series of commands given in a certain order than Excel remembers.

For example:

Imagine a worker on an assembly line given 5 components to assemble in a certain way. Each component has a certain weight, size, colour and so on.

Then you give that same assembler a different set or kit of components that may have different properties.

It’s still 5 components put together in the same way but each component maybe different from the first kit or set of components in terms of colour, weight, size and so on.

The Worker on the Assembly line is still performing the same number of operations with the same number of components but the result may be different.

STEPS TO CREATING MACROS IN EXCEL

• First, turn on the Developer tab.
• Developer Tab

To turn on the Developer tab, execute the following steps.

1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs.
3. Check the Developer check box.

4. Click OK.
5. You can find the Developer tab next to the View tab.

Creating Marcos in Simple Manner, This is an Example.

For the sum and Product of values.

Step 1

Enter the required Values.

The example here is the sum and the product in the cell.

Step 2

Go on to the View tab or go to the excel options to add the developer option to view on the window.

Step3

Use the Macro tab to explore the other features and the consequences of it.

Start the recording of macro, a window will appear.

Name the macro, there is already a default name as Macro 1. But name as you want to.

Step 3

Auto sum the values.

Step 4

Find the product by clicking on the cell of answer(Product)

Step 5

Right click on the Fx key and click the cells that have value and click on ok to get the product.

Step6

You get the product as 10000, now copy the value on the copy red cell. And right click on the cell linear to the product and then special paste it.

Click on the values button and click OK.

Step 7: Stop the recording of Macros in the Macros Tab.

Testing the Macros

Step 8

Test the Macro by using the keystrokes you assigned to it.

Step 9

Edit the Macro if necessary, look for the options and see if there are any changes to be made or re-record the Macro.

Step 10

Save the Macro. Now to use the function in different values, recorded macros are useful.

TIPS

• If the code is known to you, use Microsoft Visual Basic to enter.
• Macro can also be used as Excel’s formula inside the spreadsheet to calculate things.
• Interact with the Information in your worksheets.
• You can edit the recorded Macro in programming in the visual Basic.

The basic benefits include that at a workplace to reduce the repetitive tasks Macro’s are used. As they are recorded and further are used to reduce the time and load.

Benefits of Using Macros

• Easy to create, store and use.
• It records your key strokes while you work.

Edit in Macros

• Macros are not static.
• Once recorded, easy to alter for effective use.

Features that come in use

• Mouse clicks can be recorded to make the macro.
• Actions saved as Visual Basic Code
• Macros can be saved for future use.

Problems in Macros

• Mistake made while in the recording will be repeated every time.
• Macro code can be difficult to understand.
• Macros need debugging.
• Macros code can be used to spread viruses.
• If something is to be changed in the Macro, then the whole Macro must be re-recorded.
• A wrong macro might be chosen if there is large number of Macros.

Examples of Macros in Use

• Graph can be produced from a table of data.
• Sorting data in table.
• Used in printing a workbook.

Recommendation –

I recommend you to join Udemy,  1 hour excel for absolute beginners course.

CODE OF MACRO

Save