Relative Reference Vs Absolute Reference In MS Excel

Use Dollar Symbol..
Use Dollar Symbol..
You may not know about Relative Reference and Absolute Reference in Excel but you might heard that "using dollar ($) symbol in excel..". The name of using dollar ($) symbol in reference is nothing but Relative Reference and Absolute Reference.

What is Cell Reference ?

A Cell Reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Excel can find the values or data that you want that formula to calculate.

In one or several formulas, we can use a cell reference to refer to :

Data from one cell on the other worksheet
Data that is contained in different areas of a worksheet
Data inc cells on other worksheets in the same workbook

For Example : =A1 refers to Cell A1 and returns the value in Cell A1

=Reference!A1 refers to Cell A1 on Reference worksheet and returns the value in Cell A1 on Reference worksheet.

There are four types of Cell References in Excel

Relative Row and Relative Column Reference
Relative Row and Absolute Column Reference
Absolute Row and Relative Column Reference
Absolute Row and Absolute Column Reference

1. Relative Row and Relative Column Reference (=A1)

Neither the Column nor the Row are fixed. Both the Column and Row are relatively changes when changing the reference from one cell to other cell.

I have given the reference A2 in the Cell B2 and it results the value in A2 ie., John.
Once we do auto fill till A5 then the reference also changes relatively. The reference A3 appears in B3, A4 appears in B4 and A5 appears in B5 Cell and the respective cell reference values display as results.

2. Relative Row and Absolute Column Reference (=$A1)

Only Column is fixed and Row is relatively changes when changing reference from one cell to other cell.

Relative means in a formula, the address of a cell based on the relative position of the cell that contains the formula and cell referred to. When you copy the formula, the reference automatically adjusts.

Absolute means in a formula, the exact address of cell, regardless of the position of the cell that contains the formula.

To make column as fixed we need to use $ (Dollar) symbol before the Column Reference.

In this example, I have entered the reference $B1 in B2 cell and the result should be the value of B1 Cell. Since I have fixed Column B by using $ symbol, even if I used auto fill either across the columns or rows, I may not find any change in column but row changes relatively.
3. Absolute Row and Relative Column Reference (=A$1)

Only Row is fixed and Column is relatively changes when changing reference from one cell to other cell.

Absolute means in a formula, the exact address of cell, regardless of the position of the cell that contains the formula.

Relative means in a formula, the address of a cell based on the relative position of the cell that contains the formula and cell referred to. When you copy the formula, the reference automatically adjusts.

To make Row as fixed we need to use $ (Dollar) symbol before the Row reference.

In this example, I have entered the reference B$1 in B2 cell and the result should be the value of B1 Cell. Since I have fixed Row 1 by using $ symbol, even if I used auto fill either across the columns or rows, I may not find any change in Row but Column changes relatively.

4. Absolute Row and Absolute Column Reference (=$A$1)

Both the Column and Row are fixed. Neither will be changed nor incremented during using auto-fill option.

Absolute means in a formula, the exact address of cell, regardless of the position of the cell that contains the formula.

I have given the reference A2 in the Cell B2 and it results the value in A2 ie., Saran. Since I have used $ symbol before both column and row, even I use auto-fill either across the column or across the row, excel takes only $A$2 as cell reference. It gives the value of A2 at any time.

Shortcut to switch between Cell References?

Once enter cell reference in any of the formula, you can use Function Key F4 to switch between one cell reference to the other.