Forum Discussion
Lock a value In a formula
How do I freeze a value in a formula? For example: Cell A1 contains the formula =B1+C1. Cell A2 contains the formula =B2+C1. Cell A3 contains the formula =B3+C1.
Now I would like to pull this formula down to cell A100 which should then read =B100+C1. So, how do I lock in the value "B1" as I drag the formula down? I believe it is a symbol of some sort but trial and error has not revealed an answer. Thank you so much
It's actually the C1 that you are "locking in," to use your terminology. Excel refers to "absolute" and "relative" references.
If you wrote the formula in cell A1 as =B1+$C$1
then you could copy it down to cell A100 where it would read =B100+$C$1
Here's a reference you might find useful: https://exceljet.net/glossary/absolute-reference
2 Replies
It's actually the C1 that you are "locking in," to use your terminology. Excel refers to "absolute" and "relative" references.
If you wrote the formula in cell A1 as =B1+$C$1
then you could copy it down to cell A100 where it would read =B100+$C$1
Here's a reference you might find useful: https://exceljet.net/glossary/absolute-reference
Thank you! That is exactly what I wanted to know. I thought it was $ and had actually put that in front of C1 ($C1) but did not think to put it in front of the numeral 1 as well. Not knowing the terminology of "absolute" and using words like "locking" led me on quite a few wild goose chases. I've got that now and have applied your fix and it worked perfectly.
Again, thank you and you guys are great.
