Example of Mixed Cell Reference in Excel (3 Types)

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.

Reference cell for showing absolute reference

Example of 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.

Example of Mixed Cell Reference: reference cell for showing relative cell reference

Example of relative cell reference from a reference cell

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.

Sample dataset to explain the examples of mixed cell references

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.

 

Reference cell for showing the row lock in mixed cell reference

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.

Example of row lock 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.

Reference cell to show column lock

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.

Result of Column lock

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.

Reference cell for showing combined locking of row and column for different data in a single formula

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.

Combined mixed cell reference example

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.

Conclusion

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.

Related Articles

 

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo