Excel - Cell References

Excel - Cell References

In excel, Cell references are really significant. Be careful while copying your formula there are  three types of cell references Relative, Absolute and Mixed cell reference. Let’s look at details of each type.

 Relative cell reference:

  

This is most widely practiced type of cell reference. By default excel uses Relative cell reference (cell reference that adjust and switch when you copy or Autofill formula)

 

Select D2 cell and use autofill function (move cursor to the lower right corner and double click on + sign) or copy same formula till D7 it will automatically change cell references as indicated in below table and estimate amount of each product by multiplying quantity and Unite price.

  

  

 Absolute Cell Reference:

In this type references will remain the same when a formula is copied to a new location. No matter where the formula or the values in the original cell are moved, the formula will continue to refer to the same cell.

Look at below example for Absolute cell reference.

To calculate the discount amount write a formula in E2 “=D2*$B$10”, $ sign will indicate that cell will remain same even if we copy that formula wherever in a worksheet.

Now copy formula till E7 it will calculate the discount amount for each product by using single value that is 4% (B10).

Mixed Cell Reference:

Sometimes we require a combination of relative and absolute reference (mixed reference).

Example, if you have to calculate the amount after discount for two different months at different rate of discount. In below example, select H3 and write a formula “=D3-(D3*B$12)” if you have noticed that we used only one $ sign before the numeral. Which indicates you have fixed the row not a column.

Now copy H3 formula and paste in H3:I8 range and see how excel refereeing discount rate for each product by month.

 

Summary of Cell reference:

I hope this post has made these cell reference types clear!