$SIGN IN FRONT OF CELL VALUES TO lOCK THEM

[Tom Woodford]

IDK what you call them but I use the $ sign in formulas all the time to lock the value of cell called out in a formula so that it does not change when I copy and past to a new location, so if you have an entire column or row of values that you want to change the $ signs in all at the same time how do you do that?

I have been guilty of changing these one at a time and it is very time consuming and tedious

 

[joeu2004]

$A$1 is called an absolute reference. A1 is called a relative reference. I'm not sure what to call $A1 and A$1. I call them a mixed reference.

You can remove all "$" symbols from a formula by selecting the cells, press ctrl+H, enter "$" without quotes into the "From what" field, be sure that the "Replace with" field is empty, and click Replace All.

If that does not work for you, click Options and be sure that "Look in" is set to "Formula".

Caveat: That will remove "$" that might be part of something other than an absolute reference.

 

[Special-K99]

The term "Anchoring a cell" is often used.

If you're copying rows or columns just change one and copy the formula down or across depending how you're copying.
e.g
copying down column A

in A1
=B$3
copy paste down column A would anchor the 3, it wouldnt change.

 

[joeu2004]

I'm not sure what to call $A1 and A$1. I call them a mixed reference.

And apparently, so does MSFT, at least when "speaking" briefly. See the title at https://support.office.com/en-us/ar...ferences-dfec08cd-ae65-4f56-839e-5f0d8d0baca9.