Search code examples
excelaccounting

Excel - Formula is guessing wrong in Fill Down command

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?

Solution

  • Use $ in front of the cell names:

    C4 = SUM(C1-C3) - $J$30

    This will fill down as:

    C4 = SUM(C1-C3) - $J$30
D4 = SUM(D1-D3) - $J$30
E4 = SUM(E1-E3) - $J$30
etc.