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.

Absolute Reference Excel 2016

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
Copy and Paste Absolute reference formula
Drag and Fill handle - Absolute reference Excel 2016
Drag and Fill handle – Absolute reference