Are you looking for relative cell reference example in Excel? Then you are in the right place. 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. Relative references are cell addresses that change based on their positions when copied.

The above picture shows different types of cell references in Excel. In this article, I will discuss everything you need to know about the relative cell reference with examples. Also, we will discuss absolute and mixed references briefly.

**Table of Contents**hide

## What Is Cell Reference in Excel?

In Microsoft Excel, a cell reference is the address or location of a specific cell within the worksheet. It is a way to identify a specified cell by its row and column indexes.

It provides information to Excel on where to find the value that we need. For example, if you type a simple formula ‘**=B4**’ in cell** D4** and press **Enter**, Excel will extract the value of cell** B4** into cell** D4**.

There are mainly 3 types of cell references in Excel. They are:

- Relative Cell Reference
- Absolute Cell Reference
- Mixed Cell Reference

If you look at the above picture, you can see different types of cell references use the **dollar ($) **sign differently. In Excel, we need to use the cell references correctly while doing calculations because using the wrong cell reference type will get you wrong results.

## What Is Relative Cell Reference in Excel?

In Excel, a relative reference is a type of cell reference that changes when you copy the formula to a different cell or worksheet. It is mainly used for repeating calculations.

When you type a formula in any cell, you can use another cell as a reference. Then you can drag the cell to apply the same formula to 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 the relative reference.

For example, type “**=B4**” in cell** C4**. Then, copy the formula down to cell** C6**. In cell** C5**, the formula becomes “**=B5**” and in cell** C6**, it becomes “**=B6**”. This happens due to relative reference.

Relative cell reference allows you to use the same formula over different columns or rows without typing the formula in different cells. 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.

## Relative Cell Reference Example in Excel: 3 Criteria

### 1. Relative Cell Reference in a Formula in Excel

In this example, we will use relative cell reference in a formula. Suppose, we have a dataset for different products that contain **Unit Prices **and **Units**. We need to calculate the **Total Price **using relative cell reference.

- First of all, type the formula in
**Cell E5**:

`=C5*D5`

**E5**, cell

**C5**and

**D5**will change inside the formula.

- After that, press
**Enter**and drag the**Fill Handle**down to copy the formula.

- Now, if you click on any of the cells of range
**E5:E10**except**E5**, you will see that the relatively referred cells have been changed.

**Related Content:** How to Use Relative Cell Reference in Excel

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

You can also use relative references for a range of cells. Consider the following example, you want to find out the total price for pens, pencils, and notebooks for different customers. The **unit price** for those items is given in cell **C5:C7**.

Follow the steps below to see how the example works.

- Type the following formula in cell
**E9**:

`=D10:D12*$C$5:$C$7`

**D10:D12**is the selected range of cells with relative reference and

**$C$5:$C$7**is the selected range of cells with absolute reference. Here, we used relative cell reference because we want the quantity range to change in the formula. And used the absolute cell reference because the unit prices of different products are the same for all customers. In the

**Item**column, you must maintain the series {

**Pen, Pencil, Notebook**} for each customer.

- Press
**Enter**to see the price of items for Harold. - After that, copy the formula and paste it into cells
**E13**and**E16**. - As a result, you will get the prices of the items for all of the customers.

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

### 3. Reference Across Different Sheets in Excel

You can also refer to a cell relatively or absolutely in a formula from a different worksheet of the same workbook. Suppose, you have two sheets: **Product Cost **and **Total Cost**. Here, we will import the **Total Product Cost **(stored in **Cell E11**) from the **Product Cost **sheet and store it in the **Total Cost **sheet using relative cell reference.

- Type the following formula in cell
**C5**of the**Total Cost**sheet:

`=’Product Cost’!E11`

- Press
**Enter**to see the**Total Product Cost**in the**Total Cost**sheet.

Now, if the **Total Product Cost **changes, then it will automatically be updated in the **Total Cost **sheet. When you are writing a formula to refer cells across different sheets, you need to write the sheet name inside the apostrophe symbols.

**Related Content: **Excel VBA: Insert Formula with Relative Reference

## How Does Relative Cell Reference Change in Excel?

In Excel, cell reference changes when we move a cell left, right, up, or down. Every cell in Excel has a relative reference by default. The cell reference is automatically adjusted when you move or copy the formula to another location in the worksheet.

For example, when you refer to a cell, such as** B8**, the cell reference will change relative to its original location.

- If you move downward from
**B8**, the reference will change to**B9**because the row number has increased by 1. - If you move upward from
**B8**, the reference will change to**B7**because the row number has decreased by 1. - If you move leftward from
**B8**, the reference will change to**A8**because the column index has decreased by 1. - If you move rightward from
**B8**, the reference will change to**C8**because the column index has increased by 1.

**Related Content:** [Fixed!] Relative Cell Reference Not Working in Excel

## Absolute Cell Reference in Excel

In absolute cell reference, the cell address or reference stays the same when the formula is copied. You can use absolute references to keep both row and column constant by putting a **dollar **(**$**) symbol before them in the formula.

To explain absolute cell references, we will use the following dataset. It stores the **Yearly Interest Rate **(which is fixed), **Principal**, and **Years**. We will find out how much **Interest **people will get for a specific interest rate.

- Type the formula in
**Cell E7**:

`=C7*D7*$C$4`

- Press
**Enter**and drag the**Fill Handle**down to copy the formula.

**Cell C4**contains the fixed interest rate. So, we need to lock this cell for calculation using absolute cell reference. To apply absolute cell reference, we inserted the

**dollar**(

**$**) symbol before the row and column indexes of

**Cell C4**.

- Now, if you check any cell in the range
**E8:E12**, for example,**Cell E10**, you will see that absolute cell reference “**$C$4**” has not changed.

## Difference Between Relative and Absolute Cell Reference with Example

In this section, we will show the difference between relative and absolute referencing with examples in Excel. The following dataset contains the **Principal**, **Years**, and **Interest Rate**. We will try to find the **Interest **using both relative and absolute referencing.

- Type the formula in
**Cell F5**:

`=C5*D5*E5`

- Press
**Enter**and drag the**Fill Handle**down.

**F6:F10**. Because the cells inside the formula change while dragging down. For example, in cell

**F6**, the formula becomes “

**=C6*D6*E6**”. Interestingly, cell

**E6**contains no value. That is why we are getting

**0**in the

**Interest**column. So, how can we overcome this? The answer is: by using absolute cell referencing.

- Now, type the following formula in cell
**F5**:

`=C5*D5*$E$5`

- Hit the
**Enter**key on the keyboard. - Then, drag the
**Fill Handle**down.

**E5**using the

**dollar**(

**$**) symbol. So, cell

**E5**remains constant inside the formula. This is how absolute cell referencing works. You just need to insert

**dollar**(

**$**) symbol in front of the row and column indexes of a cell to use absolute referencing.

## Mixed Cell Reference in Excel

Mixed cell reference in Excel is a type of cell reference that contains both relative and absolute referencing. In mixed cell references, either column or row index is absolute and the other one is relative.

In the following dataset, we have calculated tiered commission using the formula below:

`=$C6*$D6*F$5`

Here, we have used mixed cell references. We can divide mixed cell reference into two types.

**$C6 (and $D6):**This is called**Relative Row Reference with Absolute Column**. You can see the**dollar**(**$**) sign is used only before the column index which makes it a mixed cell reference. So, the value of the column will be kept constant in this type of reference. It is generally used when you copy your data vertically.**F$5**: This is called**Relative Column Reference with Absolute Row**. Here, the row will be constant but the column will change. When you want to change the column but need to keep the row constant you can use a relative column reference with the absolute row. It is generally used when you copy your data horizontally.

## How to Switch Between Different Reference Types Using F4 Key in Excel

By using the **F4 **key on the keyboard we can place a **dollar **(**$**) sign in front of rows and columns.

- One press of
**F4**on the keyboard will make the cell absolutely absolute**$C$4**. - Two presses of
**F4**on the keyboard will make the cell absolute row**C$4**. - Three presses of
**F4**on the keyboard will make the cell absolute column**$C4**.

By removing the **dollar **(**$**) sign from an absolute reference we can make it a relative reference.

## Things to Remember

- Excel uses relative references as default for every cell. It changes when we copy the formula.
- Absolute references are the cell addresses that remain constant when we copy the formula.
- The
**dollar**(**$**) sign makes the rows and columns constant in the case of absolute cell references.

**Download Practice Workbook**

You can download the practice book from here.

## Conclusion

In conclusion, relative cell reference is an essential aspect of Excel formulas. 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. We have also discussed absolute and mixed cell references to show the differences and their uses here. If you have any kind of confusion, please leave a comment.