Protect formula from changing
If you have a formula in Cell A1 of =sum(B1, B2, B3) and you copy it to Cell A2, Excel automatically will change the formula to =sum(B2, B3, B4).
However if you do not want the formula to change when you paste it into different cells, you can by using the $ sign.
If you put the $ sign before the Column such as =sum($B1,$B2, $B3), the column won't change, but will allow the row to change
If you put the $ sign before the row such as =sum(B$1, B$2, B$e), the row won't change but it will allow the column to change.
If you put the $ sign before the column and the row such as =sum($B$1, $B$2, $B$3), the column and row will not change.
This procedure will be easier to understand if you try it in Excel. Try the following
- In Cell A1 type 5
- In cell A2 type 10
- In cell A3 type 15
- In cell C1 type =sum(a1, A2, A3)
- Press Enter
- You will see the total is 30
- Then highlight cell C1
- Copy the cell C1
- Highlight cell C2
- Paste C1 into C2. You notice that the total changed to 25. That is because the formula got changed to =sum(A2, A3, A4)
- Highlight cell D1
- Type =sum(A$1, A$2, A$3)
- Press Enter
- You will see a total of 30
- Highlight D1
- Copy the cell D1
- Paste the contents into cell D2
- Notice that the total is still 30 and the formula is still =sum(A$1, A$2,$A$3)
- Now highlight cell D1 and copy it
- Paste it into cell E1