Always use the "R1C1" reference format.
Under the standard "A1" reference format in Excel, references to cells are by default relative: if B1 is a cell containing a reference to A1 (for example, =A1+1) and you copy it to B2, B2 will become "=B1+1". This is often sensible behavior, but it's ultimately an instance of the program trying to guess the users' intentions. If you do want to keep the reference to A1, you must sigil the column and/or line. A$1 will fix the line, but copying B1 to C1 will yield =B1+1 and B1 to C2 will yield =B1+1. $A1 will similarly fix the column, and $A$1 will hold the reference fixed.
Under the R1C1 format, references to cells are by default absolute, and you get to specify relative references explicitly if you so desire. if R1C2 is =R1C1+1 and you copy it to R2C2, it will still be R1C1. On the other hand, if you did want to create a column that was a function of the other column, you can state R1C2 to be =R1C[-1]+1.
Under A1 format, we must always keep mental operations on the semantics of relative references by subtracting line numbers and what's worse, column letters and combinations thereof. Before I realized A1 was the wrong way to go, I would often make my entire A: row a list of numbers and freeze panes so I could reason.
You can switch to R1C1 reference format in Microsoft Excel clicking on Tools/Options/General.
I just saved you considerable mental anguish.