Cell reference is an important thing in Excel. It is easier to call cell using references than using values. There are three types of cell references available in Excel. Mixed cell reference is one of them. This article will explain 3 examples of mixed cell references along with a brief description of absolute and relative ones.
Download Practice Workbook
From here, you can download the practice worksheet.
What is a Mixed Cell Reference?
A Mixed cell reference is a combination of Absolute and Relative cell references which is used for locking a row or a column while using the reference of a particular cell. So, we will explain first what Absolute and Relative cell references are.
Absolute Cell Reference:
The Dollar ($) sign has been used before both row number and column number to lock both row and column references throughout the whole column. This is called Absolute cell reference.
Relative Cell Reference:
A Relative cell reference is to address the reference of a cell to another cell. For example, you can observe the pictures below.
Now, 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 three examples.
3 Examples of Mixed Cell Reference
The dataset contains the calculations of current and power utilizing the given values of voltages and resistances. The dataset is shown below.
Assuming that we need to lock either row or column, or both, the mixed cell reference is a way of doing this.
Let’s show the examples one by one.
1. Mixed Cell Reference for Locking by Row
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.
The result is shown below.
Here, the voltage and current values are taken from the Dataset worksheet, from cells B4 and D4. We can see that the row value is constant, that is the row (4) is locked from column B. Using Fill Handle the rest of the values of the column can be easily found without changing the row throughout the column.
Here, the row is locked from column B of the worksheet Dataset. Notice how the rows of column D are incrementing as per usual since it is not locked.
2. Mixed Cell Reference for Locking by Column
Besides, we might need to do calculations where the column should be locked. This can be done by using the dollar ($) sign before the column number. Let’s observe the pictures below.
Here, using relative cell reference, the auto-filled cell is showing a different value since the column is not locked. This is not desired since the calculation should give the same result for both columns.
Here, using the dollar sign ($) before columns C and B, the columns are locked and so giving the same result. So, you can see the importance of the dollar sign ($) in doing works related to the cell references.
3. Mixed Cell Reference for Locking Both Column and Row
In some situations, you might need to lock both rows and columns for a different set of data in the same formula. This can be done by combining both the above examples. This will lock both the row and column but not for the same data. Let’s have a look into it.
Suppose you need to calculate the power rate from given values of voltage and current. The Currency is kept fixed at 300. Then, the formula will multiply voltage and current and finally be multiplied by a currency which is fixed at 300.
Here we can see that the column has been fixed for referencing current and voltage columns specifically. The currency is kept fixed by locking the row for that particular value of the currency.
You can notice that the rows and columns are locked as needed in the next row also and so forth.
Things to Remember
i. The dollar ($) sign is used before the row or column number, and even both, to lock them according to requirements in Excel.
ii. Keyboard shortcuts are also available for changing one cell reference type to another one. “F4” is the key which-
- If pressed once, converts 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.
The article explains example of mixed cell references and describes the need of combining absolute and relative cell references to form mixed cell references. I hope the article has helped you to get an idea about the mixed cell references with the examples shown. Besides, to get related topics you can see the Related Articles section. Lastly, if you have any further query you can write in the comment section.
- Different Types of Cell References in Excel (With Examples)
- What is and How to Do Absolute Cell Reference in Excel?
- How to reference cell in another Excel sheet based on cell value!
- How to Lock a Cell in Excel Formula (2 Ways)
- How to apply same formula to multiple cells in Excel (8 easy ways)