Mixed cell references are more-tricky than the absolute cell references if we compare. There can be two types of mixed cell references:
1 The row is locked while the column changes when the formula is copied or dragged.
2 The column is locked while the row changes when the formula is copied or dragged.
Below is a excel sheet where you need to calculate the three tiers of commission based on the percentage value in cell E2, F2, and G2.
Now I want to calculate all these commissions with just one formula by helping mix reference in excel.
Enter the below formula in cell E4 and copy / drag for all cells.
=$B4*$C4*E$2
The above formula uses both kinds of mixed cell references (one where the row is locked and one where the column is locked).
How to Change the Reference from Relative to Absolute (or Mixed)?
To change the cell reference from relative to absolute, mix reference you need to add the dollar sign before the column notation and the row number. faster way to do this is by using the keyboard shortcut – F4.
For example, A1 is a relative cell reference, and it would become absolute when you make it $A$1.
Here is what happens when you select the reference and press the F4 key.
Press F4 key once: The cell reference changes from A1 to $A$1 (becomes ‘absolute’).
Press F4 key two times: The cell reference changes from A1 to A$1 (changes to mixed reference where the row is locked).
Press F4 key three times: The cell reference changes from A1 to $A1 (changes to mixed reference where the column is locked).
Press F4 key four times: The cell reference becomes A1 again.