Knowing Relative, Absolute and Mixed cell reference in Excel is one of the basic but most important skills to have in Excel.
If one understood these concepts properly then one can apply formulas in Excel in a better way and in less time.
Relative Cell Reference in Excel
Example A1, B1, A4:C8 (No dollar $ sign)
This is default type of cell reference in Excel while writing formula or function which references a cell or a range of same workbook in which function is written. After copy pasting cell reference of formula automatically changes in pasted range. No dollar signs in cell address indicates cell reference or range is relative.
In the above image you can see for amount (Unit Price * Qty) relative referencing formula is used. In cell C4 formula is =A4*B4 when you copy paste cell C4 formula in lower cells then cell reference will auto change, not need to manually write formula in all cells. Thus, cell C5 formula becomes =A5*B5 and cell C6 formula becomes =A6*B6 and so on after copy pasting.
How Relative cell referencing works in Excel?
In Cell B1 formula is =A1. In another way we can say that formula cell “B1” referencing just left cell or 1 cell left. So, whenever we copy B1 cell and paste in another cell as formula then all formula will be going to refer to just left cell or 1 cell left with respect to formula cells.
Example suppose we copied B1 and pasted in C1 can you guess what will be cell reference in C1.
Ans =B1 (Try it yourself by copy pasting in Excel )
Below image clearly explains working of Relative cell referencing in Excel
If we copy cell C3 and paste in cell H5 then same pattern of reference will be repeated ,2 cell left then 1 cell above from H5. Hence formula in H5 will refer cell F4.
Absolute Cell Reference in Excel
Example $A$1, $B$1, $A$4:$C$8 (Dollar sign $ before row and column)
In Absolute cell reference cell address is locked which means when you copy paste formula in other cells then cell reference address will not change.
One can identify Absolute cell reference by presence of dollar sign $ before row and column. To convert a reference into an Absolute one place cursor after that reference then just press F4 function key.
In the above image for incentive amount (Sale amt * Incentive%) absolute referencing is used. Cell C4 has formula =B4*$C$2 here $C$2 is absolute cell reference and cell C2 contains incentive % value. Now when you copy paste C4 in other cells then $C$2 will remain fixed in all cells formula. Cell C5 formula becomes =B5*$C$2 and cell C6 formula becomes =B6*$C$2. Now you can test various incentive amt scenarios by just changing cell C2 value no need to do any edition in incentive formula since all formulas are referring cell C2 value as Incentive percent.
Mixed Cell Reference in Excel
Mixed cell reference is of two types
Only Row Absolute: Example A$1, A$1:A$3. In this type of reference only row remains same or locked after formula copy pasting in other cells and column remains relative. Dollar sign is applied before row only.
Only Column Absolute: Example $A1, $A4:$B6. In this type of reference only column remains same or locked after formula copy pasting in other cells and row remains relative. Dollar sing is applied before column only.
In the above image cell C6 has formula =$B6*(100%-C$4). You only need to build this formula one time only then just copy paste in other cells (C6:E9), no need to build formula for each rate column.
Here in $B6*(100%-C$4) formula only 4th (C$4) row is absolute since in each % Discount scenario row is going to remain same but column will change after copy pasting hence in C$4 only row is made absolute not column. Same way in each case MRP column is going to remain same but row will change after copy pasting hence in $B6 only column is made absolute not row.
Above example perfectly demonstrates importance and beauty of applying reference type as per once need. Learning and applying cell referencing in Excel formulas will save your time and makes your spreadsheet much more maintainable.
How to change cell reference type in Excel ?
While writing Excel formula you can change cell or range reference type by using F4 function key. Just keep cursor after that range or cell reference or address then press F4 key repeatedly till your desired cell reference type.
Below image shows how F4 function key is used to cycle between various cell reference types. If range is relative then pressing F4 will make it absolute, again pressing F4 will make row absolute only, again F4 pressing will make it column absolute only and press F4 again then it becomes relative.
From relative cell reference to
- Absolute – Press F4 function key one time.
- Row absolute only – Press F4 function key two times.
- Column absolute only – Press F4 function key three times
https://youtu.be/OkhN8CVBIaI