How to use and create relative references in Excel while recording macros

Who I am
Shane Conder
@shaneconder
Author and references

Don't worry so much if you are not at an advanced level on Microsoft Exel knowledge, as we will explain what a Macro is, how to edit or modify it and what it is for. Macros are nothing more than a series of instructions that sequentially execute an order. And it uses a special language called VBA che sta per Visual Basic for Applications.

Exel macros are often used to perform repetitive tasks and I know these are done automatically. In case we work with the Marcos recorder, we can activate an option that will allow us to work with the relative references. Below we will explain in a very didactic way how to use and make related references in Excel while recording Macro.



How to use and create relative references in Excel while recording macros

We will explain it to you with a very simple example, so that you can understand it if you are not so familiar with the Office application. As we have already said, when we record a macro using relative references we can position automatically i give in any cell.

The example we will use will be that of a table that contains data on the names and surnames of people separately and we want these names and surnames to be merged into one new column. So the first thing we need to do is locate the cell where we want the actions to be performed.

So let's go to the main menu and choose the Programmer tab and then go to the command Record macros. While we are creating a clip and this action will generate the Record Macro dialog.


Here we have to enter some data, such as the Name of the Macro, the Hotkey, where we have to place which other letter will be combined to execute this Macro. We must also enter a short description, with which we will define what this Macro will be for from now on.


Relative References in Excel to Record Macro

And finally we create a clip in the Accept option, once done we will now go to the command Use relative references. The clip in this command will light up and all actions I will perform from now on will use i related references. Now I go to the cell that I have chosen to apply its reference.

I select the cell and then we create a correct clip so that the options appear and we choose Insert. A small dialog box will appear and in it I will select the option insert new column. Then we click on the option OK, this action will allow a new column to be displayed and we will move a cell down in this column.

Here we will write a formula that combines the names and surnames of people, remember that this is an example so as to have an idea of ​​how to use the relative reference. The formula is = C2 & ”” & D2 now if we press the Ctrl and then Enter keys, the combination of the first and last name will appear in the cell.


To save the formula in the other cells of the column, press the Ctrl + C keys then move to the next column where the surnames are found. Then we press the Ctrl key and without releasing we move to the end of this column. Now let's move on to the next cell in the column where we want the combined names to appear.


Now I press i at the same time Ctrl + Shift + arrow keys up and to finish I press the Enter key. And as you can see, all the combined names will appear in this column, now to finish you need to go back to the first cell where you started recording the actions and then stop Macro recording and create a clip in the Relative Reference command.

add a comment of How to use and create relative references in Excel while recording macros
Comment sent successfully! We will review it in the next few hours.