Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

I am trying to teach some excel basics to someone so I might as well blog about the subjects which come up.

Excel rewrites formulas when you copy/paste

When you reference a cell for example cell A1 in a formula you can simply use the value "A1". However if you subsequently move the cell containing the formula anywhere else (for example one cell to the right) excel will automatically change the formula references relative to the new cell location of the formula.

So if you have a formula in cell "F1" referencing cell "A1" and you subsequently copy/paste the formula into a different cell for example "G1" the formula will now reference cell "B1" to account for the shift.

Lock the cell reference

I find this behavior annoying since 99% of the time I don't want it to happen. So to prevent this behavior we change the format of the cell reference from "A1" to "$A$1". This locks the cell reference so that if we copy paste the formula later it will still reference "A1".

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub