Any **Excel **worksheet is made of numerous cells. These cells are referenced based on their row or column position for calculation. There are **3** types of **cell reference** among which **Mixed Cell Reference** is the most versatile one. In this article, we will see an example of **mixed cell reference** in **Excel**. We will discuss it based on **3** ideal types.

**Table of Contents**hide

## Download Practice Workbook

Download this sample file that we prepared in this article.

## What Is Mixed Cell Reference in Excel?

A **Mixed Cell Reference** is a combination of **Absolute** and **Relative **cell references which is used for locking a row or a column or both while using the reference of a particular cell. So, we will explain first what **Absolute **and **Relative **cell references are.

**Relative Cell Reference:**

A **Relative Cell Reference** addresses a cellâ€™s reference to another cell and adjusts the cells when copied. For example, you can observe the pictures below.

Here, we got the value of **Current **(**Amp**) with this formula in **Cell D5**.

`=B5/C5`

Now, copy this formula up to **Cell D8** and you will see that each output has automatically referred to their adjacent rowâ€™s cells for calculation.

**Absolute Cell Reference:**

When the cell reference is locked and therefore becomes constant when copied, it creates an **Absolute Cell Reference**. A **Dollar **sign (**$**) is used to lock it.

In the following example, we have used this formula to get the Current value where **V=40V**.

`=$B$5/C5`

Now, when we copy the formula, **Cell B5** remains constant as it holds the value **40V**. Therefore, it acts as an **Absolute** cell reference.

So far, we have pictured a brief idea of relative and absolute cell references. In this stage, we will focus on our main topic, the** Mixed cell reference**. We will describe the idea with **3** ideal types

**Read More:** **Absolute Cell Reference Shortcut in Excel (4 Useful Examples)**

## 3 Ideal Types of Mixed Cell Reference in Excel

Now, let us discuss mixed cell reference with an example. For this, we have prepared a sample dataset. The dataset contains the calculations of **Current **and **Power **utilizing the given values of **Voltages **and **Resistances **as shown below.

Assuming that we need to find the **Power **value for a specific Voltage. We will do this by locking row, column and both. Letâ€™s show the processes one by one.

### 1. Lock Row to Calculate with Mixed Cell Reference

Let us consider that from the given dataset we want to calculate **Power **values for the same **Voltage **value. In that case, we need to lock the row for a specific column. This can be done by using the **Dollar **(**$**) sign before the row number.

- For this, insert this formula and get the following output.

`=B$5*D5`

Here, the **Voltage **and **Current **values are taken from **Cells B5** and **D5**. We can see that the row value is constant. This is why **Row 4** is locked from **Column B**.

- Next, use
**Fill Handle**to get the rest of the values without changing the row throughout the column.

Here, we locked the row from **Column B **of the worksheet. Notice the incrementing rows of** Column D** are as per usual.

**Read More:** **How to Reference a Cell from a Different Worksheet in Excel**

### 2. Apply Mixed Cell Reference by Locking Column

Besides, we might need to do calculations where we need to lock the column. We can do this by using the **Dollar **(**$**) sign before the column number.

- First, insert this formula in
**Cell D5**and then**AutoFill**to copy it in the**Cell range D6:D8**.

`=C5*B5`

- Then, select the cells and drag the bottom corner to right for copying them in
**Column E**. - As we did not lock
**Columns B**and**C**, the output columns are showing different results.

- Therefore, use the
**Dollar**sign (**$**) before**Columns C**and**B**to lock them and so they are giving the same result.

**Read More:** **Excel VBA Examples with Cell Reference by Row and Column Number**

**Similar Readings**

**Reference Another Sheet in Excel (3 Methods)****How to Use Sheet Name in Dynamic Formula in Excel****Absolute Reference in Excel (With Examples)****Difference Between Absolute and Relative Reference in Excel****Different Types of Cell References in Excel (With Examples)**

### 3. Lock Both Row & Column to Insert Mixed Cell Reference

In some situations, you might need to lock both rows and columns for a different set of data in the same formula. We can do this by combining both of the above examples. This will lock both the row and column but not for the same data. Letâ€™s have a look at it.

Suppose you need to calculate the power rate from given values of **Voltage **and **Current**. Fix the **Currency**Â at **300**.

- Now, insert this formula in
**Cell C11**to calculate the**Power Rate**.

`=($B5*$D5)*B$11`

**Current**and

**Voltage**columns specifically. Fix the Currency by locking the row for that particular value of the currency.

- Then, hit
**Enter**to get the first value.

- Lastly, use the
**Autofill**tool to get the**Power Rate**in the**Cell range C12:C14**.

You can notice that we have locked the rows and columns as we need this reference for the next rows as well.

**Read More: ****How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)**

## How to Change Cell Reference in Excel

If you need to change the cell reference from one type to another, use the **F4 **button on your keyboard by following the instructions below.

- If pressed
**once**, convert**Relative**(**A1**) to**Absolute**cell reference (**$A$1**). - If pressed
**twice**, converts**Relative**(**A1**) to**Mixed**cell reference locked by**Row**(**A$1**). - If pressed
**thrice**, converts**Relative**(**A1**) to**Mixed**cell reference locked by**Column**(**$A1**). - If pressed
**four**times, it becomes a**Relative**cell reference (**A1**) again.

## Things to Remember

- Use the
**Dollar**(**$**) sign before the row or column number, and even both, to lock them according to requirements in**Excel**. - As
**Mixed**cell reference is a combination of both, we may apply an absolute reference to individual column index and row index.

## Conclusion

The article explains an **example of mixed cell reference** in **Excel **with **3** ideal types. It also describes the need of combining absolute and relative cell references to form mixed cell references. I hope the article will help you to get an idea about the mixed cell reference. Lastly, if you have any further queries you can write in the comment section. Follow **ExcelDemy **for more tutorials.

## Related Articles

**How to Use Reference of Worksheets in Excel (5 Examples)****Cell Reference in Excel VBA (8 Examples)****How to Find and Replace Cell Reference in Excel Formula****Excel VBA: Cell Reference in Another Sheet (4 Methods)****[Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)****How to Use Variable Row Number as Cell Reference in Excel****Excel VBA: Get Cell Value from Another Workbook without Opening**