Anyone here can explain on how "Relative" button works during macro recording in excel?

To be honest I have not recorded a macro in Excel for some years, however let me try and answer your question.  I understood how to record macro's in the Excel2007 version I think it was, where there was a record macro button, now it is a little more complex.

RECORDING THE MACRO

The DEVELOPER Ribbon must be open.

Commence recording by "record macro"

I use short cut key, Control and Shift + an alpha character

USING THE RELATIVE MACRO BUTTON:

If the option was turned off when the macro was recorded, running the macro in say cell H10, would move the cursor to the place in the worksheet identical that the position the cursor was in the worksheet when the macro was recorded. 

If recording started in Cell A1 and at the beginning of the recording, the first key strokes were move two placed to the right to C1; then without "relative button on during recording" would result in the macro performing everything from CELL C1;

With "Relative button on when recording the macro" then I may be in cell A300 and the macro will type in the the result of the macro from CELL C300, not C1

If relative button was on so it will work for other cells and different cells, and if it was off so it will return back to the origin cell u were recording at

Macro Relative Reference

https://www.youtube.com/watch?v=OQk3Qwy5IZI