How to use Relative and Absolute cell references in MS Excel

When a formula is created in excel a cell reference is created. Relative reference just simply links to cell but Absolute reference  have a $ character before the column reference and the row reference of cell.

Relative References
Auto fill formula by dragging/double clicking is most extensively used feature of Excel. As in example below cost formula =D3*C3 can be copied all across to below cells. The formula cell references changes with cell.

excel1

excel2

Absolute References
At times requirement may come up when the cell reference must remain the same when copied or when using Auto fill to other cell location. Dollar sign is for absolute reference to hold a column ,row reference constant.

In below example we have used above table with tax rates, now since tax rate is constant we don’t want it to change. Here we have used absolute reference.

excel3

excel4


Absolute cell has 3 variant as mentioned below

Row reference constant, column reference can change           $C1
Row reference can change, column reference constant           C$1
Row reference constant,column reference constant              $C$1

Keyboard shortcut

Use the function key F4 to change the reference from relative to absolute and vice versa.

Download above sample workbook click here