Relative and Absolute Cell References

by on September 8, 2017

Relative and Absolute Cell References

Having knowledge about what Relative and Absolute Cell References means and how to use it will save you lots of time. In excel by default, all cell references are Relative. It means, cells changed when you copy the formula to the rows or columns. Absolute cell references do not change its position when you copy the formula to the rows or columns.

Let’s look at some examples to explain the concept.

First Example:  A1=B1+C1. Copy the formula to the rows, the cell references will change, so A2=B2+C2 and so on and so forth. 

Second Example:  A1=(B1+C1)*$D$1. Copy the formula to the next row, the cell references of A1=(B1+C1) will change to A2=B2+C2 but $D$1 will remain the same. In this case, $D$1 is an absolute cell reference while the others are relative cell references.

Third Example: There can be a mix of relative and absolute situation. For example in D$1, if you move to other rows the cell reference will not change but if you move to other columns the cell reference will definitely change. Similarly, in $D1, the rows are not locked means it will change but columns are locked, so copying it to other columns will have no effect.

Benefits of Relative and Absolute cell references

As said earlier that knowing how to use Relative and absolute cell references will save you lots of time thus improving efficiency to the next level. We have 12 invoices and each invoice will be paid in 3 installments (Image provided below).

Relative and Absolute Cell References

The first step is to apply a single formula to the first cell by correctly using the cells references and then will copy it to other cells. In Cell C5, enter the following formula=C$4*$C13. The strong point of the formula is that if we copy it the next column the first part will change but the second one will remain the same. If we copy it the next rows to calculate the second and third installment, the first part will remain the same but the second will change. This is exactly what we wanted.

Relative and Absolute Cell References

Now copy the formula to rows and columns and your calculations are ready. For verification purposes, go to 12th invoice 3rd installment and check if it picking up data from the right place.

Relative and Absolute Cell References

 

Please follow and like us:
0

Leave a Reply

Your email address will not be published. Required fields are marked *