This article is not about Excel Macro but this is about Absolute and Relative References in Excel formula. In Excel Formulas, as you have seen, for referring any cell, we use the Address (Reference) of that Cell like A1, B2 etc. This is called Cell Reference in Excel. In other words you can call it as Cell Address. This Cell reference is categorized in two category called : Absolute Reference and Relative Reference
1. Relative Reference:
While referring any Cell in Excel if you give the reference as A1, B50 etc. This is called Relative Reference. In this case suppose you have used formula as =A1 somewhere and drag down in the same column then “1” of the formula =A1 will be incrementing automatically and for example at 50th Row the Formula will turn to “=A50”. Similarly if you drag the same in same row then A of the formula =A1 will be changing as per the Column name like B, C, D etc. For example formula =A1 will be turned to =E1 in the E column.
2. Absolute Reference:
If you refer the same Formula =A1 as =$A$1. This is called Absolute Reference. while dragging in the Column or row Cell Reference remains same before which $ sign is placed. You can also refer a cell like =A$1 or =$A1
In below example we will see what is the difference between all the above mentioned 3 ways of referring a cell A1.
Reference Explanation =A1 Both Column Name and Row Number are changeable. On dragging in same column Number will increase and on dragging in same row, column Name will be increasing as explained Above. This is called Relative Reference =$A$1 Both Column Name and Row Number are fixed. On dragging formula anywhere Column Name and Row number, none of them will vary. It will remain fixed. Note:This gets changed only when you delete any row or column. At the end of this Article, we have discussed how to handle this as well. =$A1 Column Name will remain always fixed but Row Number will be changing. Here Column Name is Absolute but Row Number is Relative. =A$1 Row Number will remain always FIXED but Column Name will be changing. Here Row Number is Absolute but Column Name is Relative.
How to Toggle between all these above References:
In Excel Formula just by pressing “F4” key you can toggle between all the above style of References. So if you want to Change the formula in a specific Style then keep pressing F4 and stop when it is as per your expected style.
Even with Absolute Referencing, if you delete any row or column then formula gets changed. If you want to overcome that also, then you can use INDIRECT function. For example: =INDIRECT(“A1”).
What is =INDIRECT():
This function does not consider the reference as “Address”. It accepts it as a normal Text rather than Address. and that is the reason it never change the Value “A1”. So if you want a Truly Absolute formula then use this INDIRECT function.