In this article, we will let you know what **mixed cell reference** is in Excel and describe it with 4 examples.

**Table of Contents**hide

**Download Workbook**

You can download the free practice Excel workbook from here.

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

**Mixed cell reference in Excel** is used when we want to lock

- either row while changing the columns
- or the column while changing the rows – when the formula is copied.

This can be done by simply putting the **dollar ($) sign** before the cell references.

Let’s understand the use of mixed cell reference with a simple multiplication table.

In the following example, we wrote the same numbers in columns and rows which we will be multiplying by mixed cell reference.

- To multiply each row with all the column values, we should lock the row by putting the
**dollar sign ($)**in front of the row indexes and to multiply each column with all the row values, the**dollar sign ($)**should be in front of the column number to lock the column.

According to this, to calculate the multiplication table, the formula for the first cell will be,

`=$B3*C$2`

- Press
**Enter**and you will get the accurate result.

- To copy this formula in all the cells just drag the
**Fill Handle**over the table and you will get the multiplication result using mixed cell references.

For your better understanding, we have provided formulas for the whole table for each cell.

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

**4 Examples of Mixed Cell Reference in Excel**

This section describes 4 different examples of how to use the mixed cell reference in Excel.

**1. Calculate Discount Based on Percentages with Mixed Cell Reference in Excel**

If you want to **calculate discounts or commissions based on different percentage rates** for specific products, then using the mixed cell reference is a very effective method in Excel.

Look at the following example. Here we have to **calculate the total discount** amount for given products on **10%, 15%** and **20% discount rates**.

Let’s see how to do that with the mixed cell reference in Excel.

**Steps:**

- In the Cell
**G6**(in our case, which is the cell to store the result), write the formula,

`=$C6*$D6*G$5`

Here,

**-> $C6** = the locked ($) **Column C **(column ** Price**) with changing row number so that only the prices of the products will be inserted while calculating.

**-> $D6** = the locked ($) **Column D** (column ** Quantity**) with changing row number so that only the quantities of the products will be inserted while calculating.

**-> G$5** = the locked ($) **Row 5** (** Discount Rate 10%**) with changing column so that only the discount rates will be applicable while calculating the discount amount of different products.

- Press
**Enter**. You will get the result according to your formula.

- Now drag the
**Fill Handle**to apply the formula to the rest of the cells.

You can check the formulas for each cell from the picture below to understand the calculation with the mixed cell reference.

**Read More: ****How to Use Cell References in Excel Formula (All Possible Ways)**

**2. Compute Annual Income Tax Based on Tax Rates with Mixed Cell Reference**

Here we will learn how to **calculate annual income tax based on different tax rates** on different years through mixed cell reference in Excel.

Let’s learn how to do that with the mixed cell reference in Excel.

**Steps:**

- In the Cell
**H6**(in our case, which is the cell to store the result), write the formula,

`=C6*$F6`

Here,

**-> C6** = value to calculate.

**-> $F6** = the locked ($) **Column F** (column ** Tax Rate**) with changing row number so that only the tax rates will be inserted while calculating.

- Press
**Enter**. You will get the result according to your formula.

- Now drag the
**Fill Handle**to apply the formula to the rest of the cells.

You can check the formulas for each cell from the picture below to understand the calculation with the mixed cell reference.

**Read More:** **Cell Reference in Excel VBA (8 Examples)**

**Similar Readings**

**Difference Between Absolute and Relative Reference in Excel****Absolute Reference in Excel (With Examples)****Excel Sheet Name in Formula Dynamic (3 Approaches)****Excel VBA: R1C1 Formula with Variable (3 Examples)**

**3. Determine the True Rating of Cables with Mixed Cell Reference in Excel**

With Excel’s mixed cell reference you can also execute complicated calculations such as **measuring the True Rating of cables** in an Electrical Power System.

Suppose we have data as given below,

- Types of Cables
- Calculated Current in Ampere
- The details of the types of cables as
- Rating in Ampere
- Ambient temperature
- Thermal insulation

- Number of cable circuits running together
- Depth of the burial of the cable
- The moisture of the soil

With the given data, we will find out the true rating of cables using Excel’s mixed cell reference.

**Steps:**

- In the Cell
**D5**(in our case, which is the cell to store the result), write the formula,

`=$F5*($G5/10)*$H5*F$12*G$12*H$12`

- Press
**Enter**. You will get the result according to your formula.

- Now drag the
**Fill Handle**through Cell**D9**to apply the formula. - You need to write the formula independently for the new type of cable in Cell
**D10**. Here the formula is,

`=$F5*($G5/10)*$H5*F$12*G$12*H$12*1.2`

- Press
**Enter**. You will get the result according to your formula. - Now drag the
**Fill Handle**through Cell**D14**to apply the formula.

We need to understand that to calculate the ** True Rating in Ampere** from the given coefficient of

**and**

*Rating, Ambient Temp***, we must use the mixed cell reference because if we use**

*Thermal Insulation***Relative**or

**Absolute Cell Reference**it could lead to miscalculations due to non-uniform data distribution. To avoid this, we need to lock specific rows and columns according to our needs, hence using mixed cell reference to determine the

**Derating of Cables**is a much more powerful method to utilize.

To understand the calculation for each cell with the mixed cell reference, you can check the formulas from the picture below.

**Read More:** **Different Types of Cell References in Excel (With Examples)**

**4. Measure the Percentage of Earnings, Expenses and Cash Left with Mixed Cell Reference**

In this section, we will learn how to measure the percentage of **Earnings, Expenses** and **Cash Left after Expenses against the Total **of a company with the mixed cell references in Excel.

Let’s see how to do that with the mixed cell reference in Excel.

**Steps:**

- In the Cell
**C10**(in our case, which is the cell to store the result), write the formula,

`=C5/$I5`

Here,

**-> C5** = value to calculate.

**-> $I5** = the locked ($) **Column I** (column ** Total**) with changing row number so that only the total amount will be calculated against

*and*

**Earnings, Expenses***.*

**Cash Left**- Press
**Enter**. You will get the result according to your formula.

- Now drag the
**Fill Handle**to apply the formula to the rest of the cells.

You can check the formulas for each cell from the picture below to understand the calculation with the mixed cell reference.

**Conclusion**

This article showed you 4 useful examples of **mixed cell reference** in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.