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".
Reader Comments