Excel - Formula is guessing wrong in Fill Down command?

Question

I know this should be simple but I can't work it out. I have a column that is calculated with the sum of the 3 columns to the left of it. E.g. SUM(C1-C3). When I fill that down, each cell correctly gets its formula. E.g. C4 = SUM(C1-C3) D4 = SUM(D1-D3) E4 = SUM(E1-E3) etc. I only need to do one more thing. I have a cell containing a number I'd like to remove from each of these cells, so my formula would be: C4 = SUM(C1-C3) - J30 D4 = SUM(D1-D3) - J30 E4 = SUM(E1-E3) - J30 etc. When I fill down from the C4 square's formula though it starts guessing more like this: C4 = SUM(C1-C3) - J30 D4 = SUM(D1-D3) - J31 E4 = SUM(E1-E3) - J32 etc. How can I get it to select J30 each time instead of automatically assuming I want it to increase for each row?

Answer 1

Change J30 to $J$30...that will make that reference the same when you copy/paste or autofill

Answer 2

What your formula is doing, as it typically does, is relative referencing. To make it stay on the cell you want, you'll need to make the cell absolute. You do that by putting the $ sign in from of the cell reference you don't want excel to change. For example, $J30 fixes your formular referenced in the J column. J$30 fixes it in the row 30. And $J$30 fixes it in that particular cell.

How this helps?