Relative Cell reference: It is the default cell
reference in excel and it changes when copied or dragged using Excel Autofill
feature. Let’s see the below figure. Here the look up value and table range are
both relative references.
As soon the formula is copied in Cell F6, the cell references also changed. We can observe that the lookup value is automatically changed to cell E6 and the table range is also changed to Range A5:B15, where row 13-15 doesn’t have any data.
Absolute Cell reference: Dollar signs are normally
used to lock a column or row or both as
$A1 = Column “A” is locked, row is not
A$1 = Row 1 is locked, column is not
$A$1 = Cell A1 is locked
Pressing “F4” key
makes the cell reference absolute.
In the below figure we can see the Lookup value has $ sign
prefix before the cell and range references
Now, Lets copy the formula to Range F6. And we can see that
the Lookup value Column reference and the table range remained same.
