A mixed reference in Excel locks either the column or the row, but not both. It is helpful when copying formulas across cells. Use it when you need to keep one part of the reference fixed, like creating multiplication tables or calculations with consistent rows or columns.
How to Create a Mixed Reference in Excel
When you want to lock a cell’s references, you press F4. Automatically, two dollars appear around the column and row references. But do you know what these dollars mean? 🤔
In fact it’s very easy to understand. The dollar is before what is locked
$F1means the column is lockedF$1means the row is locked.
And with the shortcut F4, you can manage all the cases 😀
- First press of F4:
$F$1 - Second press of F4:
F$1 - Third press of F4:
$F1 - Forth press of F4:
F1
Tips for Using $ in Mixed References
If mixed references confuse you, use this trick to know where to put the $.
- Write your formula without the dollar signs first.
- Create three or four formulas in different cells.
- Use the menu Formulas > Show Formulas to see your formulas.
Your spreadsheet should now show all the formulas clearly. Now, it’s easy to understand what must be locked. All the formulas point to column B and row 3. So obviously, you must lock column B and row 3 💡
When the first formula is created, you copy it to the 99 other cells, and your multiplication is done.
No Mixed References in a Table ⛔
If you use a table, you cannot use mixed references. You can only lock the column header.
