The use ofÂ cell references in an Excel formula is very important when formulas are copied and pasted to other cells. Excel worksheet is made of cells and we refer to these cells by specifying row number and column number. We can use these cell references of the same worksheet or from a different worksheet while creating formulas. Basically, there are three types of cell references we can use in excel,

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

**Uses of Different Cell References in Excel Formula**

Here weâ€™ll work on a dataset of a list of fruit items in a super shop in Australia. The dataset has a price column that shows the product of** Unit Price** and **Quantity** of the fruit items. Weâ€™ll first find out the **Price **as a product of Unit Price and Quantity and show the use of relative cell reference. One fixed tax rate will be used to demonstrate the use of fixed cell reference and variable tax rate to demonstrate the mixed cell reference**.**

** **

**1. ****Use of Relative Cell Reference in Formula in ExcelÂ Â **

**What is Relative Cell Reference in Excel?**

In Excel, all cell references are relative, by default. Relative cell reference changes according to the relative position of rows and columns while copied and then pasted through a range of cells. In the following example, weâ€™ll see how the formula** D5*E5** changes like** D6*E6, D7*D7**, and **D9*E9** at last.

**How to Use Relative Cell References in Formula in Excel**

In this example, weâ€™ll copy and paste the formula which calculates the product of **Unit Price** and** Quantity** through the other column cells to show the use of relative cell reference. Letâ€™s follow the example:

**Steps: **

- In cell
**F5**, write the following formula to calculate the**Price**as a**product**of the**Unit Price**and**Quantity**

`=D5*E5`

- Then hit
**Enter**and the result is**21**(=10.5*2). - Now pick the fill handler for cell
**F5**at the bottom right corner of the cell, hold and drag it down to the cells**F6:F9**of column**F**.

- The above steps
**copied**the formula of cell**F5**and then pasted it to**F6:F9**with relative references.

Now to check, click on any of the cells from **F6:F9**. Here we clicked on cell **F8 **which shows the formula as a product of cells **D8 **and **E8**, relative to the row number of cell** F8.**

**Read More: **How to Keep a Cell Fixed in Excel Formula

**2. ****Absolute Cell Reference in Excel Formula**

**What is Absolute Cell Reference in Excel?**

In different calculations, we use a constant value in the formula that doesnâ€™t change relatively with the rows and columns positions. Thatâ€™s how we can use an absolute cell reference in such a formula. In the following illustration, weâ€™ll see how to get different** Price After Tax** values while keeping the tax rate value constant using absolute cell reference **$C$11.**

**How to Use Absolute Cell References in Excel Formula**

In this example, we calculated the **Price After Tax** value on a fixed **tax rate **of **7.5%**, stored in cell **C11.** To get this we used the **Total Price** values that were calculated in the previous example. Letâ€™s dive into the example:

**Steps:**

- To calculate the
**Price After Tax,**write the following formula in cell**G5**–

`=F5+(F5*$C$11)`

To make cell **C11 **an absolute reference we put the **$ **sign before the column value C and also before the row value **11**, which is **$C$11.** Here **F5 **is a relative reference that will change relative to the row number downwards.

- After this hit
**Enter**and the result is**22.58**(=21+21*.075). - To copy and paste the formula to cells
**G6:G9**locate the fill handler, drag it down to the cells and finally, release the cursor.

- The above steps copied the formula of cell
**F5**and then pasted it to**F6:F9**with absolute references.

In the final result, we see each of the values of the **Price After Tax **column used the absolute cell reference** $C$11.**Â Letâ€™s click on cell G8. It shows the formula which contains the **relative cell reference F8 **according to the row number of **G8 **but the absolute cell reference **$C$11** remains constant**.**

**Read More:** How to Hard Code in Excel

**3. ****Write Formula Using Mixed Cell Reference in Excel**

**What is Mixed Cell Reference in Excel?**

The mixed cell reference is a combination of relative and absolute cell references together. In this case, we put one part of the cell reference relative and the other part absolute vice-versa.

**How to Write a Formula Using Mixed Cell References in Excel**

In this illustration, we want to calculate** Price After Tax** for three different** Tax Rates** that are located in cells **C13, D13**, and **E13.** When we are applying the formula for a product like Banana, we want three **Price After Tax** values for three different tax rates. That means tax rate cell reference has to be relative row-wise. Again for a specific duration like **Jan-Apr**, we should have a **fixed tax rate** to get the **Price After Tax** values which require absolute tax rate column-wise**.** Letâ€™s follow the example below to get a clear understanding.

**Steps**:

- In cell
**G6,**write the following formula-

`=$F6+($F6*C$13)`

Here we put the tax rate cell reference as **C$13** which is mixed. We put the **$** sign before the column value **13** to make it absolute column-wise and left the row value** C** without the **$** sign as itâ€™ll be relative row-wise.

There is another way to look at the formula. **$F6 **is also a mixed cell reference. It is relative through the column cells and absolute through the row cells while calculating the **Price After Tax** values.

- Letâ€™s hit
**Enter**and the output is**58**(=21+21*0.075). - Now locate the fill handler and drag it to the right to calculate values for different tax rates of different time durations.

- The output shows three
**Price After Tax**values for three different tax values.

To check double click on cell** I6.** In the formula, we can see the tax rate cell reference is **E$13 (C$13 **in cell **G6 **formula) which is relative row-wise. On the other hand, the total price cell reference **$F6** didnâ€™t change as it was in the formula for** G6** which is absolute row-wise**.**

- This time locate the fill handler at the
**r**ight bottom corner of cell**G6**and drag it down to**G6:G10.**

- From the results, letâ€™s double click cell
**G9**to explain what happens here. In the formula, we can see the tax rate cell reference is**C$13 (**also**C$13**in cell**G6**formula) which is absolute column-wise**.**On the other hand, the total price cell reference**$F6**did change to**$F9**which is relative column-wise**.**

- Finally, we get all the values for
**Price After Tax**following the previous steps.

**Read More:** How to Reference Cell in Another Sheet Dynamically in Excel

**Notes**

We can also use a cell reference from one worksheet in a formula that is in another worksheet i.e. across multiple worksheets. In the following illustration, we used the mixed tax rate cell reference from the mixed worksheet. Although the formula is in the worksheet which is named as ‘different worksheet’.Â For this, we need to put the worksheet name in a quote and an exclamation (!) sign after that. And then put the cell reference**.** See the example below:

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

Now, we know how to use different types of cell references in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions donâ€™t forget to put them in the comment box below.

**Related Articles**

- How to Make Different Types of Cell Reference in Excel
- Cell Reference in Excel VBA
- Excel VBA: R1C1 Formula with Variable

**<< Go Back to Cell Reference in Excel |Â Excel Formulas | Learn Excel**