Hello friends its Sachin here with another blog on Absolute and relative reference in excel. In this blog we are going to learn the following.
1.) What are cell references
1.) What are Relative references with examples
2.) What are Absolute reference with examples
3.) What is Absolute Row with examples
4.) What is Absolute Column with examples
What is Cell reference
Cell reference is nothing but just the location or reference of that cell. every reference has two components. One is column and another is row as shown in the image below
The written in alphabet indicate the column number and another in number indicate the row number
What are Relative references with examples
Relative references as the name suggest that its something which will change.
Example – 1 Calculating the Table of 5 with relative reference
Explanation – We can see the 2 main things from the below example
1.) In first table – As we move down in formula the row part of reference also moves down but column part is static because we are in same column.
2.) In second horizontal table – As we are moving right the column part of cell reference is changing but the row number is not because we are in the same row.
When Insert any kind of cell reference it can be one of the following – Relative reference or Absolute reference. In the above example to calculate the table of 5 we have used relative references.
What is Absolute cell reference with examples.
Absolute as the name suggest its something that does not change. It means it remain static weather we drag the formula Up , Down or Right Left.
Example -1 Table of 5 with one absolute reference
How to apply Absolute reference – to convert cell reference in absolute cell reference click over the reference and tap F4 – key it will place 2 $ dollar sign one to lock row part of Cell reference and another to lock the column part of cell reference.
Explanation – in the below example we have gave absolute reference to cell C$4$. Here both the column as well as row part is given absolute reference. and that is why as we drag down the formula the C$4$ reference of formula does not change but the other number B5 changes as its presented as relative reference.
What is Absolute Row and Absolute column with example
In the above examples we have discussed the Absolute reference there we also discussed that in there are two $ dollar sign one to lock the column and another to lock row.
Absolute row-is case where we only put one $ dollar sign in front of row only to lock row only.
Absolute column – is case where we put $ dollar sign in front of column only to lock column only.
Now lets see the uses of it with examples
Example-1 Calculate many tables with mix of relative and absolute references.
While selecting 5-in reference B$4 the dollar sign is only in front of row number 4 which means only row is locked and column is relative which also indicates that when we will copy or drag the formula down the row 4 will not change and column will also not change as while copying the formula downside we are still in the same column.
But when we will copy in right side B Column will shift to C and so on and row number will as-usual not change because we are in same row.
While selecting 1 – in reference $A5 the $ dollar sign is only in front of Column number A which means that only column number is locked which further indicates that if we will move right it will not change and if we move down it will change.
Yes you might think that its so confusing but trust me with little practice you can easily master it.
Once again Thank You for reading the blog
in case of any query or suggestion feel free to comment.