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.
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.
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.
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
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.
Example 1: Lock Row to Calculate with Mixed Cell Reference in Excel
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.
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.
Example 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.
- 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.
- 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)
Example 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.
- 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.
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.
Download Practice Workbook
Download this sample file that we prepared in this article.
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.
- 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