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

**Read More**: **What is a cell reference in Excel**

**Download Practice Workbook**

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

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

**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:** **Absolute Cell Reference in Excel (4 Examples)**

**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**right 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:** **Mixed Cell Reference in Excel (4 Examples)**

**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:

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