Relative and Absolute Referencing in Excel

Relative Referencing

Suppose we are writing a formula in cell D3 to total numbers of Bananas and Oranges as shown in picture below.

We would start by putting (=) sign and then using cell references we will end up by putting formula as B3+C3. Now if same formula is to be performed in cell D4 and cells below it.

This can be simply done by copying formula from cell D3 to cell below it. When you copy formula from D3 to D4, the cell references from B3+C3 automatically change to B4+C4. This is a default behavior of Excel and is called as Relative Referencing.

Absolute Referencing

Sometimes, we don’t want Excel to change the reference and keep one or more cell/s references as fixed. This way we override the default behavior of Excel and want part of formula to refer to a single cell.

Cell references are controlled by using $(dollar) sign in front of cell address to freeze it. By putting $ sign in front of row number freezes the row and same way by putting the $ in front of column alphabet freezes the column reference.

Below example will help us understand it better.

In pic below, 10% VAT is to be calculated on total products sold. VAT % is stored in cell B1 and value of VAT to be calculated on cell D3 is to be stored in cell E3.

Formula in cell E3 will be D3*$B$1. By putting $ sign on cell B1 as $B$1, the cell reference has been fixed. If same formula is copied down to cells E4 and E5, cell reference of cell D3 will change to D4 but cell reference on B1 is fixed by putting a $ sign and hence cell reference to cell B1 will be constant and will always be referring to cell B1 value.

Please go through the working excercises file attached with article for practising.

Entering Dollar ($) sign by using F4 function Key

After selecting cell reference, press F4 function key to toggle from Relative to Absolute reference & vice-versa.

F4 function key works in 4 following ways-

  1. When a cell(say A1) is selected in a formula, it is without $ sign and is in state of relative reference. Pressing F4 for first time will change reference to $A$1 which is an absolute reference with 2 dollar ($) signs.

  2. Pressing F4 again will change $A$1 to A$1 to freeze only the row number

  3. Pressing F4 again will change A$1 to $A1 to freeze only the column

  4. Pressing F4 again will change $A1 to A1 which is the normal relative reference state.