MS Excel 2016 – Absolute and Relative reference

What is cell reference

Cell reference is generally used in formulas where data in different cells are used for purpose of calculations. Excel has two types of references – absolute and relative reference. Absolute reference remains constant irrespective of change (moved / copied) in cell. Relative reference changes as soon as formula is moved / copied from one cell to another.

Absolute Reference

When requirement while creating formula is that reference of cell should not change while change in cell (where formula is moved or copied to). Dollar sign is used for absolute reference to use a column or row reference remain as constant. Use the function key F4 to change the reference from relative to absolute. Pressing F4 first time changes reference from relative to absolute, when you press it one more time it changes to absolute reference option 2 below and pressing it again will change it to option 3 of absolute reference.

Absolute Reference can be used in following ways

Row reference constant, column reference constant              $C$1

Row reference can change, column reference constant           C$1

Row reference constant, column reference can change           $C1

 Alternatively, if you don’t want to use absolute reference you can use name for cells.

How use Absolute reference to create a formula in Excel 2016

Below example we will calculate sales commission. Here first Column has name of salesperson, Second Column has details of sale made, third column has calculated value of commission. Since Rate of Commission is fixed so Absolute reference is used.

Once formula is created reference can be changed to absolute by pressing F4. After reference is changed to absolute, you can copy formula by simple copy paste operation or dragging fill handle.

Copy and Paste Absolute reference formula
Drag and Fill handle – Absolute reference

What is Relative Reference in MS Excel

Relative reference is used in MS Excel to create formula where data or linked cell also change with change in cell of formula (formula is copied or moved) exists. In Relative reference create a formula as usual, you do not need to use dollar sign. Now when you copy or move your formula rows and column reference used in formula changes.

How use Relative reference to create a formula in Excel 2016

We will use same example as for Absolute reference with only difference that we will use Relative reference here. In Below example we will calculate sales commission. Here first Column has name of salesperson, Second Column has details of sale made, third column has commission% which is variable for all salesman. Fourth column is calculated commission value. Finally, fifth column is Total amount. The commission value is calculated on basis of variable commission and hence need to be changed for every salesman. Once created you can copy formula or fill by dragging fill handle.

Exit mobile version