Question

Name the three types of cell references used in Excel formulas and give an example how each is used.

Answer 1

The three types of cell references used in Excel formulas are:

1) Relative reference - This is the standard type of reference, which is default, when a formula is entered in Excel. Using this reference, the position of the cell being referred to, in the formula changes, as the formula is copied in other cells. For example, if formula in cell B1 is A1+5, then if the formula in B1 is copied (CTRL+C) and pasted (CTRL+V) by to cell C2, then the position of reference cell changes from A1 to B2 and the formula in C2 appears as : B2+5. The usage of relative reference is illustrated in Excel as below. This is shown using Formula view (to view formulas, press CTRL+~)

2) Absolute reference - This reference always points to the same place. So, position of the referred cell remains fixed, even if the formula is copied to another cell. Using the same example as used earlier, the formula in B1 is written as $A$1+5

Note that "$" is used before both row and column addresses.

If this formula is copied and pasted to C2, the formula still remains the same and it points to cell A1. The usage is shown below:

3) Mixed reference - Using this reference, one part (either row or column) of the cell position is fixed, while the other part is relative. Using the same example, keeping the row fixed, the formula in B1 is written as : A$1+5 , if this formula is copied to C2, it becomes B$1+5 , as shown below:

and keeping the column position fixed, the formula is written as $A1+5 , if this formula is copied to C2, it becomes $A2+5 , as shown below: