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!
