Excel Sheet Copy/Insert but Keep cells

Copper Contributor

I am trying to copy rows with one column that has a formula which multiplies by another cell on another column. When I copy and insert the grouping of rows, the formula is not moving to the new section of rows. It is hard to explain... The current formula in Column X is =$A$63*R64 and it works for X64 to X79



BUT when I copy row 62 to 80 and insert them anywhere, the formula keeps the $A$63 formula, instead of automatically changing to the new cell (for example $A$83). I have tried putting the $ in multiple places ($A63, or A$63) and it is not working. Ultimately I want to type in a number on the A63 line and have it update the rate on Column X.  Does anyone have any ideas?


1 Reply



Without more details, it's hard to know exactly what's happening. But it almost certainly involves that relative vs absolute referencing, which is what the $ sign deals with. Here's a reference that might help you diagnose; that link goes to a description of "relative reference," and there's one on that page regarding "absolute." You'll want to read both.


That the $A$63 stays unchanged when that formula is copied is exactly what the absolute reference is supposed to do. If it's A$63 and you copy that from column B to column C, then the new formula will read B$63, because what the "relative A" was really saying. while in column B, was "that cell to the immediate left" and that's why in column C it turns into B$63. The $63 will stay 63 no matter where you go, but the A will turn into the column immediately to the left (again, assuming a starting point of column B)