To learn reference in excel is very important. Sometime Interviewer asks some questions like what is cell reference and how to use it and he can also ask you that what is the difference between relative or absolute cell reference? There are four types of **references** relative reference, absolute reference or fixed reference, mixed reference and 3D reference.

## What is a **Relative cell Reference**

By default, we use relative reference in excel sheet. We probably using most of this reference. When you copy the formula from one cell to another cell the address of a cell is relatively change. When you write the formula in C1 cell is (=A1*B1) and copy this formula and paste into C2 cell then you find that the address of cell will change automatically (=A2*B2).

**Relative Reference Example**

In the following example, we have a table of peripheral devices of the computer. The table has some code, name, and quantity of each item. We want to create a single formula instead of multiple formulae to evaluate total price. To get the total price of each item, we will **multiply** each **itemâ€™s price** by the **quantity**.

**1.** At first, you create the following table for using this formula and select **E2** cell.

**2.** Type the formula to evaluate: **=C2*D2**

3. Press **Enter **on your keyboard. The formula will be evaluated, and the result will be displayed in the cell.

**4.** Select **E2** cell and keep pressing **small plus **sign **+ **in the bottom-right corner of **E2** cell and **drag downside** cell you want to fill. In an example, we will drag **E2 to E11**.

**5.** When you release the mouse left key you will find that the formula will be copied to the desired cell with **Relative Reference.**

**6.** When you edit the fill cell by using **F2** key or **double click**. You will find that the reference has changed according to the row number of cell.

## What is an **Absolute cell Reference**

An absolute reference is just opposite to relative reference. In absolute reference, the address of a cell remains constant. When you copy and paste the formula from one cell to another cell, the address of cell will never change. You can use the dollar sign ($) or F4 key for using the absolute reference. When you put the formula in C1 cell is (=$A$1*$B$1) and copy this formula and paste into C2 cell then you find that the address of cell will not change (=$A$1*$B$1).

**Absolute Reference Example**

In the following example, we have some major countries population table. We want to create a single formula instead of multiple formulae to evaluate the country wise population ratio. To get country wise population ratio we will use **Absolute Reference**.

**1.** At first you create a following table for using formula and select **E2** cell.

**2.** Type the formula to evaluate: **=D2/C13*100**

3. Press **Enter **on your keyboard. The formula will be evaluated, and the result will be displayed in the cell.

**4.** Select **E2** cell and keep pressing small plus sign + in the bottom-right corner of E2 cell and drag the downside cell as you want to fill. In an example, we will drag **E2 to E12**.

**5.** When you release the mouse left key you will find the **error #DIV/0** because you are using **relative reference**.

**6.** When you edit and check the error of cell **E5 **by using **F2** key or **double click,** the reference of cell **C13** has changed according to the row number of cell.

**7**. To solve this **error #DIV/0** you have to fix the cell **C13**.we will use **dollar $ **sign to fix **C13** cell and change into **$C$13**. Type the formula in **E2** cell once again and formula will be:** D2/$C$13*100.**

**8.** Do the same step as **No.4 & No.5**.

**9.** Now you edit the fill cell by using **F2** key or **double click**. You will find that the reference of C13 cell has not changed according to the row number of the cell because of the **Absolute Reference**.

## What is a **Mixed cell Reference**

Mixed Reference is a very complex reference. Mixed reference is a combination of relative reference or absolute reference. We know that every cell address has two component that is row and column. In the mixed reference, one component remains relative while another component remains absolute by using a **dollar sign ($)**. When you drag your formula, the reference of a cell will change. When you type the formula in C1 cell is (=$A1*B$1) and copy this formula and paste into C2 cell then you find that the address of cell will change automatically (=$A2*B$1).

**Mixed Reference Example**

In the following example, we have some major countries population table. We want to create a single formula instead of multiple formulae to evaluate the country wise population ratio for a different year. To get the country wise population ratio for a different year, we will use **Mixed Reference**.

**1.** At first you create a following table for using formula and select **F2** cell.

**2.** Type the formula to evaluate: **=D2/D$13*100**

**And you do the same step as previous example.**

**Note: In this Example, we will fix a row of cell D13 by using dollar $ sign and D13 change into D$13.**

## what is a **3D cell Reference**

A 3D reference is a very simple and easy reference. It is referred to any cell address or range use in multiple sheets. For using 3D reference you can use other sheet cell or range address in an active sheet. We can use 3D reference from one sheet to another sheet and also from one workbook to another workbook. You can use aggregate multiple sheets into one sheet with 3D reference.

**3D Reference Example**

In this example, we have 5 sheets of excel with the same template data as below in the picture. We want to evaluate **yearly total sale** for 4 quarter in **yearly sale sheet**. To evaluate **yearly total sale, **we will use 3D Reference.

1.On the yearly sell sheet, select cell B2 and type an equal sign =

2.Go to the Qtr1 sheet, select cell B2 and type a + operation sign.

3.Repeat step 2 for the Qtr2, Qtr3 and Qtr4 sheet.

5. Press **Enter **on your keyboard. The formula will be evaluated, and the result will be displayed in the cell.

6. Select** B2** cell and keep pressing small **plus sign +** in the bottom-right corner of **B2** cell and drag down and right side cell as you want to fill. In an example**,** we will drag **B2 to B5**.

Now you will see the result with using Absolute Reference.

grt job mukul sir