Example of Mixed Cell Reference in Excel (3 Ideal Types)

Get FREE Advanced Excel Exercises with Solutions!

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.

=B5/C5

Example of Mixed Cell Reference

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


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.
=B$5*D5

Lock Row to Calculate with Mixed Cell Reference

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


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.

=C5*B5

Apply Mixed Cell Reference by Locking Column

  • 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


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.
=($B5*$D5)*B$11

Lock Both Row & Column to Insert Mixed Cell Reference

Here we fixed the column for referencing 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


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.


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.


Related Articles


<< Go Back to Mixed Cell Reference | Cell Reference in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Syeda Fahima Nazreen
Syeda Fahima Nazreen

SYEDA FAHIMA NAZREEN is an electrical & electronics engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Proteus, MATLAB, Multisim, AutoCAD, Jupiter Notebook, and MS Office, going beyond the basics. With a B.Sc in Electrical & Electronic Engineering from American International University, Bangladesh, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo