How to Record Macro and Use Relative Reference in Excel

Welcome to Excel Avon

Record Macro in Excel

Macro is the most useful and essential feature of MS Excel. In this chapter, we will to summarize about the macro and how to use it. We will describe each step for recording and executing a macro in Excel. A macro is a developer feature of Excel that is used to automate repetitive tasks to save time. used to record and perform repetitive tasks. Thus, it saves the time of the users to complete a task that requires repeated operations. You can record the same task in a macro and have it implemented automatically. 

In Excel, when you record a macro and save the macro then run it, Excel goes to the generated VBA code for the respective task and starts executing the same steps. It allows to execute the code created for the recorded macro in the background and perform operations whenever required.

• • For macro record you have to bring developer tab in excel which I have explained to you in previous post you can see post.
  • By default, Excel records macros in absolute mode.

How to Record Macro in Excel

DOWNLOAD USED EXCEL FILE FROM HERE>>

Now we will record the macro. If Excel already has developer tab in menu bar then we can go directly to record macro. So, let’s start with how to record a macro in Excel. 

As you can see, we have written the data in the excel sheet and will total it but before that we will start the record macro(2) button.

This dialog box will open after clicking on record macro, in this dialog box, provide all the required details.

1. 1. Enter the name of macro.
   2. You can also provide a shortcut key here to create the macro.
   3. Now, in the next Store Macro field, always select This Workbook (currently opened workbook). 
   4. Provide a description of the macro in the Description field. The Description field is optional.

Click the OK button after providing all information. 

Now we will write the Total in B13 below and use the sum formula in C13 which is =Sum(C2:C12)

Click the Stop Recording button and stop the macro recording task. Your first macro (named myMacro) is recorded and saved successfully. After completing the recording, the stop recording button will be hidden and the option of record macro will appear, so you will understand that the recording has stopped.

Now we’ll go to the Macros and see, Now we’ll go to the macros and see what we’ve recorded. 

Now we will go to Macros and see what we have recorded then click on Macro, 

You can see in full reference because here cell B3 is selected then it is simple written in VBA, As you can see here cell B3 is selected so it is simple written in VBA and there is not much explanation.

To go back to the excel sheet, click on the button in the corner of the VBA sheet.

Record Macro with Relative Reference

After coming back to excel sheet we will use Relative Reference.

Relative reference is a type of cell reference in Excel. This reference changes when the formula is copied to any other cell or any other worksheet. Relative cell references are used whenever calculations need to be repeated. Relative reference macros record the offset from the active cell. Such macros will be useful if you have to repeat the steps at different places in the worksheet. 

To tell the macro recorder that it has to use relative references,

Follow Step-

Similarly last time we have data in which we have to find Total, so now

1. 1. Click On Use Relative Reference

2. Click Record Macro

This dialog box will open after clicking on record macro, in this dialog box, provide all the required details.

1. 1. Enter the name of macro.
   2. You can also provide a shortcut key here to create the macro.
   3. Now, in the next Store Macro field, always select This Workbook (currently opened workbook). 
   4. Provide a description of the macro in the Description field. The Description field is optional.

Write the total as recorded in the default and use the sum formula to find the total.

Click the Stop Recording button and stop the macro recording task. Your first macro (named myMacro2) is recorded and saved successfully.

Now we’ll go to the Macros and see, Now we’ll go to the macros and see what we’ve recorded. 

Relative reference macros record the offset from the active cell. Such macros will be useful if you have to repeat the steps at different places in the worksheet. As you can see the image below-

Back to excel sheet and We come back to the excel sheet and if we delete the cell with total then we can bring it back with the short cut key,                              our shortcut was Crtl+Shift+F.

Learn More Topic in VBA  here

DOWNLOAD USED EXCEL FILE FROM HERE>>

So I hope you have understood How to Record Macro and Use Relative Reference in Excel and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

You can also see well-explained video here