First time here? Check out the FAQ!

1 | initial version |

You need to convert your formulas from relative reference to absolute reference.

Example:

If you have a formula in cell B3 which is =A1, copying this formula to any other place, sheet or spreadsheet will paste the formula as relative, i.e. it will get the value from the cell that is one column to the left and two rows above.

However if you convert the formula to absolute (by editing the formula, placing the cursor near A1 and pressing Shift+F4) it will be converted to =$A$1 (obviously you can also type the dollar sign(s) manually)

This formula always refers to cell A1 if you copy or move it to another cell. Similarly moving it anywhere on another sheet will show the formula as =Sheet1.$A$1

Note that first dollar locks the Column (in this case A) and the second locks the row (in this case Row 1). Pressing consecutively Shift+F4 while editing the formula will cycle through several combinations of locking column and/or row.

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.