Mixed Cell reference in Excel
Sometimes we need Cell reference in Excel formulas, where either Column is fixed or Row is fixed, not both. These type of Cell reference is called as mixed reference.
For example, refer below image.
Here in this table, price of some commodity at different cities is entered in Row number 2 and its purchased quantity is entered in Column B. We need to write a formula to multiply price with quantity of the commodity.
If you use Excel absolute Cell reference or relative Cell reference, the formula will not work properly, if you copy and paste the formula in multiple Cells. In this case we need to use mixed Cell reference.
We know that the price of the commodity is entered at Row number 2. So when we make the Cell reference for price, we need to anchor the row number using $ (Dollar) character.
Similarly, the quantity of the commodity is entered in Column B. So when we make the Cell reference for quantity, we need to anchor the Column letter using $ (Dollar) character.
Refer below image.
You can see from the below image that when we refer the Cell with price, the Row number (2 in this case) is fixed and Column letter is relative. Similarly, when we refer the Cell with quantity, the Column letter is fixed and Row number is relavive.
To understand the concept of mixed cell reference completly, let us try to copy and paste the formula in required Cells.
Refer below images.
Finally, let us check the formula in any random Cell to understand how mixed cell references in Excel formulas work. Refer below image.
To understand the concept of mixed cell reference completly, let us try to copy and paste the formula in required Cells. When we check the copied formula at Cell F6, we can see that the fixed Row number 2 and fixed Column number B are not changed
Written by Jajish Thomas.
Last updated on 27th January, 2022.