Search

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.

mixed-cell-reference-01.jpg

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.

mixed-cell-reference-02.jpg

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.

mixed-cell-reference-03.jpg

mixed-cell-reference-04.jpg

Finally, let us check the formula in any random Cell to understand how mixed cell references in Excel formulas work. Refer below image.

mixed-cell-reference-05.jpg

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

Related Tutorials
Cell reference (Cell address)
Range reference
What are Excel formulas
How to write formulas in Excel
How to see all formulas in Excel sheet
Mathematical operators in Excel
Comparison operators in Excel
Concatenate (&) text operator in Excel
Cell or range reference operators in Excel
Union and Intersection of Excel ranges
Operator Precedence in Excel
How to change the operator precedence in Excel
Error messages in Excel Formulas
Hash error in Excel (#### in excel)
Multiple parenthesis in Excel formulas
Trace error in Excel formulas
How to remove trace error arrows in Excel
Relative and absolute reference in Excel
F4 key in Excel formula
Mixed Cell reference in Excel