**Relative Cell Reference in Excel** is one kind of cell reference where the referred cell changes when it is copied, moved or used in an array. It is a very useful feature of Excel and provides great help when you need to apply the same formula over a range of columns or rows. In this article, I will discuss everything you need to know about relative cell reference with example.

## Download Practice Workbook

## Function of Relative Cell Reference

When you type a formula in any cell, you can use another cell as a reference. Then You can drag the cell in which the formula is applied to apply the same formula over other cells in the column or row. While doing so, the cell you used in the formula automatically changes and that’s why it is called relative reference. It allows you to use the same formula over different columns or rows without typing the formula in different cells. This is the beauty of relative reference. It saves a lot of time because instead of inserting a formula every time, you can simply drag a cell to apply a formula over your dataset.

## How to Make a Cell Reference Relative

When you select a cell or insert a cell in a formula it is by default relatively referred. Relatively referred cells show only the column and row number in the formula bar such as **B4**. If a cell has any **dollar sign ($)** such as **$B4**, **B$4 **or **$B$4, **the cell has absolute reference. **Click here** to know more about absolute reference. In this case, just simply delete the **dollar sign ($)**, the cell will be relatively referred.

### 1. Relative Row Reference with Absolute Column

When you put the** Dollar sign** only before the column number you will get relative row reference with the absolute column. It means when you drag the cell, only the row of the selected cell will change, the *column will remain unchanged*. It will look like **$B4. **When you want to change the row but want to keep the column constant you can use relative row reference with absolute column. It is generally used when you copy your data vertically.

**Related Content:** **Relative and Absolute Cell Address in the Spreadsheet**

### 2. Relative Column Reference with Absolute Row

When you put the **Dollar sign** only before the row number you will get relative column reference with absolute row. It means when you drag the formula cell, only the column of the selected cell will change, the *row will remain unchanged*. It will look like **B$4. **When you want to change the column but want to keep the row constant you can use relative column reference with absolute row. It is generally used when you copy your data horizontally.

**Related Content:** **Absolute Cell Reference Shortcut in Excel (4 Useful Examples)**

### 3. Switching Reference of a Cell

- First, select the cell in the formula bar and press
**F4,**it will make it absolutely referred. - Then Press
**F4**, the cell will have an absolute row with a relative column. - Press
**F4**again, the cell will have an absolute column with relative row. - Press
**F4**again, the cell will be relatively referred.

For example, let’s use the **B4** cell to demonstrate the switch operation.

**B4>**press F4**> $B$4>**press F4**>B$4>**press F4**>$B4>**press F4**>B4**

## 3 Suitable Examples of Relative Cell Reference in Excel

### 1. Relative Cell Reference in a Formula

Now, let’s see how we can use relative cell reference in a formula. Consider the following examples. Here we have different principal amounts and years of deposit for different people. We want to find out how much Interest they will get for a specific interest rate.

➤ Type the following formula in cell **E8**,

`=C8*D8*$C$4`

In the formula, cells **C8 **and **D8** are relatively referred. So, if we drag cell **E8**, cell **C8 **and **D8 **will change in the formula. But cell **C4** won’t change because it has absolute reference.

➤ Press **ENTER**

As a result, we will get the *Interest *for the first entry.

Now,

➤ Drag cell **E8** to the end of your dataset.

As a result, we will get the *Interest *for all of the entries.

Now, if you click on any of the cells of column **E **except **E8**, you will see that the relatively referred cells have been changed in the formula bar but the absolutely referred cell has remained the same.

**Related Content:** **How to Use Cell References in Excel Formula (All Possible Ways)**

### 2. Relative Reference for a Range of Cells

You can also use relative references for a range of cells. Consider the following example, you want to find out the total price for pen, pencil and notebook for different customers. The unit price for those items is given in cell **C4:C6**.

➤ Type the following formula in cell **E9**,

`=D9:D11*$C$4:$C$6`

Here,** D9:D11 **is the selected range of cells with relative reference and** $C$4:$C$6 **is the selected range of cells with absolute reference.

➤ Press **ENTER**.

You will automatically get the value in the **E10 **and** E11 **cells along with cell **E9**.

Now,

➤ Copy the cell **E9 **and paste in cell **E12 **and **E15**.

As a result, you will get the prices of the items for all of the customers.

Now, if you click on any cell from **E12 to E17** you will see that the range of relatively referred cells has been changed but the range of absolutely referred cells has remained constant.

**Read More:** **Excel VBA: Insert Formula with Relative Reference (All Possible Ways)**

**Similar Readings**

**Absolute Cell Reference in Excel (4 Examples)****Difference Between Absolute and Relative Reference in Excel****Excel Sheet Name in Formula Dynamic (3 Approaches)****How to Reference Cell by Row and Column Number in Excel (4 Methods)****Cell Reference in Excel VBA (8 Examples)**

### 3. Reference across Different Sheets

You can **refer to a cell relatively or absolutely in a formula** from a different worksheet of the same workbook. Suppose you have data of some deposits in the sheet named* Deposit* and want to use these data to calculate interest for different deposits in the sheet named *Interest*.

Now,

➤ Type the following formula in cell **C5 **of the *Interest *sheet,

`=Deposit!C8*Deposit!D8*Deposit!$C$4`

Here, **Deposit!C8 **and **Deposit!D8 **are used to refer the cells **C8 **and **D8 **of the *Deposit *sheet relatively and **Deposit!$C$4 **are used to refer to the cell **C4 **of the *Deposit *sheet absolutely.

➤ Press **ENTER**.

As a result, you will get the *Interest *for the first deposit.

After that

➤ Drag cell **C5 **to the end of your dataset.

So, you will get the *Interest *for all of the deposits.

Now, if you click on any cells of column **C** except **C5**, you will see that the relatively referred cells of the other sheet have been changed but the absolutely referred cell has remained the same.

**Related Content:** **Reference Another Sheet in Excel (3 Methods)**

## Conclusion

Relative cell reference becomes very handy when you have to work with a large dataset and have to apply the same formula over multiple cells. I hope you have found this article useful, understand the relative cell reference example and are ready to utilize it. If you have any kind of confusion, please leave a comment.