What is Mixed Cell Reference in Excel?

A mixture of Relative Cell Reference and Absolute Cell Reference is known as the Mixed Cell Reference in Excel. A dollar sign ($) is used in either the row number or the column number in a cell reference to create a Mixed Cell Reference.

Table of Contents

Variants of Mixed Cell Reference

Depending on the position of the dollar sign ($), two variants of the Mixed Cell Reference are possible. In the following sections, I will discuss both of them one by one.

Mixed Cell Reference: Type-1

In the first type, the dollar sign ($) is used before the column number. So the column number of a cell reference becomes fixed. Only the row number is available to change. Some of the examples of this type of Mixed Cell Reference are $A1, $D3, $G8, $K14, etc.

Mixed Cell Reference: Type-2

In the second type of Mixed Cell Reference, the dollar sign ($) is used before the row number. As a result, the row number becomes locked up in a cell reference. But the column number is available to change. Some of the examples of this type of Mixed Cell Reference are A$1, D$3, G$8, K$14, etc.

How to Insert a Mixed Cell Reference in Excel

1. Manually

You can manually input the dollar sign ($) before the row or column number to create a Mixed Cell Reference. For that,

Keep the cursor either before the row number or the column number of a Cell Address.

Then press SHIFT + 4 to insert a dollar sign ($).

2. Using F4

Alternatively, you can use the F4 button to convert a Relative Cell Reference into a Mixed Cell Reference. For that,

Click on a cell address in a formula.

Then press the F4 button. Two dollar signs ($) will be assigned both before the row number and the column number. However, that’s an Absolute Cell Reference.
Press the F4 for the 2nd time. This time a dollar sign ($) will be assigned before the row number. Thus, a Type-2 Mixed Cell Reference will be generated.
If you press the F4 button for the 3rd time, a dollar sign ($) will be assigned before the column number. So a Type-1 Mixed Cell Reference will be generated.

Applying Mixed Cell Reference in Excel

A. To Lock Columns

Here is a dataset of some food items. I will multiply the item quantity (column B) with the price (column C). I want to keep the rows moving while copying the two columns.

So I added the $ sign before the column numbers in the formula:

=$B2*$C2

Then I copied the formula down with the Fill Handle.

This way I got the total price of each item.

B. To Lock Rows

To find the total prices with different VATs, first I sum up the total amount in cell D5.

You can see the different VATs are in the same row (row 7) but their column numbers are different cells B7, C7, and D7, respectively.

To multiply the VATs with the total amount I applied the formula:

=$D$5+B$7

So only the column number moved this time, but not the row number.

Conclusion

I hope you got a clear idea about the mixed cell reference from this article. Also now can apply the mixed cell reference in Excel just the way I showed you in the examples. Feel free to leave a comment in the comment section. Also please check out our Blog page for more Excel-related articles.

Rate this post