A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are 3 type of reference we are discussed below:

Relative Reference:
            In relative reference when we copy formula from one cell to another cell, value will not copy rather formula will be copied and the co-ordination of cell will be changed according to the location of target Cell.


  • Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.
  • Cell D3 references cell B3 and C3. Cell D4 references cell B4 and C4. Cell D5 references cell B5 and Cell C5.
  • In other words each cell references its two neighbour on the left.



Absolute Reference:
            In absolute reference, the Co-ordination of cell will not be changed according to the location of target cell. For avoiding this problem we have to put $ symbol before name of cell. which is common or Constant.


To create an Absolute Reference to cell C2, place a $ symbol in front of the column letter and row number of cell C2 ($C$2) in the formula of cell C3. Now drag this formula to the other cells.

Here $B3 means: Column B Constant and Row 3 change the location

$C$2 means: Column C and Row 2 (C2) is fixed.


Mixed Reference:
            In mixed reference, the Co-ordination of cell will not be changed according to the location of target cell. For avoiding this problem we have to put $ symbol before name of cell. which is common or Constant. The combination of relative and absolute reference is called Mixed Reference.


$B3 means: The reference to the 'Basic' should be a fixed reference to column B. So place a $ symbol in front of the column letter of cell B ($B3).

C$2 means: we place a $ symbol in front of the row number of cell C2 (C$2) in the formula of cell C3. So that row 2 is fixed.


Note:
  • We don’t place a $ symbol in front of the row number of B3. This way we allow the reference to change from B3-B4- B5 when we drag the formula down.
  • In a similar way, we don’t place a $ symbol in front of the column letter of C2. This way we allow the reference to change from C2-D2-E2 when we drag the formula across.
  • Here The references to column B and row 2 fixed.