What is Absolute & Relative cell Reference?

Contact Us

Bhandup (W): Dreams Mall, Opp BMC Market, Nr Bhandup Station, Mumabi 400078.
Thane (W): G-03, SAI Sagar App, Beside Sai Babab Mandir, Kalher Thane.

+91 9870419968

info@advancedexcelcourse.com





What is Absolute & Relative cell Reference?


Relative Cell Refrence

Cell that changes his reference when we copy and paste, or drag a formula.

A B C D
  1   Relative Refrence
2 2 1 2 =A2*B2
3 2 2 4 =A3*B3
4 2 3 6 =A4*B4
5 2 4 8 =A5*B5
6 2 5 10 =A6*B6

Absolute Cell Refrence

Cell that remains constant when we copy and paste, or drag a formula.

A B C D
  1   Absolute Refrence
2 2 1 2 =$A$2*B2
3 2 4 =$A$2*B3
4 3 6 =$A$2*B4
5 4 8 =$A$2*B5
6 5 10 =$A$2*B6


Lets follow the bellow example to further understand the Relative reference in a better way:

  1. In Range A2:A9 write A, B, C, D, E, F, G, H
  2. In Range B1:J1 write 1, 2, 3, 4, 5, 6, 7, 8, 9

  3. Note: Using formula in B2 we want the output as: A1, A2, A3, A4, A5, A6, A7, A8, A9 (Horizontally) and A1, B1, C1, D1, E1, F1, G1, H1 (Vertically)


  4. Now in cell B2 write the formula as =A2&B1 and then drag the same till B2:J9. You will see that, apart from cell B2 we didn’t get the desired result in the rest of the cells.
  5. Now in cell B2 let’s put the formula as: =$A2&B1 and then drag the same till B2:J9. You will see that in a first vertical row we got the desire result but we didn’t get the desired result in the rest of the cells.
  6. Now in cell B2 let’s put the formula as: =$A2&B$1 and then drag the same till B2:J9. You will see that we have got the desired result in all the cells.

A B C D E F G H I J
  1   1 2 3 4 5 6 7 8 9
2 A =$A2&B$1 A2 A3 A4 A5 A6 A7 A8 A9
3 B B1 B2 B3 B4 B5 B6 B7 B8 B9
4 C C1 C2 C3 C4 C5 C6 C7 C8 C9
5 D D1 D2 D3 D4 D5 D6 D7 D8 D9
6 E E1 E2 E3 E4 E5 E6 E7 E8 E9
7 F F1 F2 F3 F4 F5 F6 F7 F8 F9
8 G G1 G2 G3 G4 G5 G6 G7 G8 G9
9 H H1 H2 H3 H4 H5 H6 H7 H8 H9

Cell Refrence Description
$A1 Dollar sign at the beginning will change the row reference and the column reference will be constant (Press function key F4 thrice to get the $ sign at the beginning)
A$1 Dollar sign in between will change the column reference and the row reference will be constant (Press function key F4 twice to get the $ sign sign in between)
$A$1 Dollar sign at both the end will keep row reference and the column reference constant (Press function key F4 to get the doller sign at both the end)




  • facebook
  • google+
  • Youtube
  • skype
  • whatsapp
  • mobile
  • mail