Forum Discussion
Copy a formual, locking 1 data cell
Hello
I have this formual to calculate the value in cell F11. I woul like to copy the formula down to F12, 13, 14, 15 and 16. Needing to lock the cell F5 value in the formula. Copy and paste only changes for F% to F6 to F& and so on. Thank you
=(D11-(B11+F5)-(G11)+C11)/D11
2 Replies
You precede the number or letter with a $ if you don't want it to change when you copy and paste or fill. So
=(D11-(B11+F$5)-(G11)+C11)/D11
will keep row 5 as you copy down
=(D11-(B11+$F$5)-(G11)+C11)/D11
will keep both column F and row 5 if you copy down or left or right
You can fix your formula by using an absolute reference for F5:
Code
=(D11-(B11+$F$5)-(G11)+C11)/D11
Some explanation on references below :
Relative reference (F5)
Moves when you copy the formula.
Example: F5 → F6 → F7
Absolute reference ($F$5)
Stays fixed no matter where you copy the formula.
Use this when one value must remain constant.
Mixed reference ($F5 or F$5)
Locks only the column or only the row.
Useful when copying across rows or columns.
In your case, $F$5 keeps the value fixed while the rest of the formula updates correctly as you copy it down.
