What is a cell reference? and how do we use in excel?

cell reference in excel

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.

relative cell reference example

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

multiply operation in relative cell reference

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

answer of relative cell reference

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.

dragging operation perform

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

answer required in 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.

edit 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.

example of absolute cell reference

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

formula apply in cell

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

answer of that 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.

dragging operation

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

show error in absolute cell 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.

edit 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.

use dollar sign or f4 key

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

dragging operation
Using Step No 4
show answer
Using Step 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.

edit cell with use of dollar sign

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 =

3d reference

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

3d reference

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

3d reference

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

3d reference example

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.

3d reference

Now you will see the result with using Absolute Reference.

Summary Table

summary of cell reference
Summary Of Referencing

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

shares