# Excel Autofill with defined steps

Disclaimer: I think similar questions have been asked before, but the answers were all quite complicated, involving special cases or index functions etc.

Is it possible to autofill a formula were the references are not just plus one? Consider this example: I want the column filled by these formulas:

``````=Sum(A1:A3)
=Sum(A4:A6)
=Sum(A7:A9)
etc.
``````

Is there a simple way of defining a formula to tell Excel to increment the number by three. Something like: `=Sum(A1 "+3":A3 "+3")`

• Use INDIRECT() based on ROW(). Oct 5 '18 at 12:11
• To Auto fill the Formula you need VBA code like this,, Range("C2").Formula = "=Sum(A1:A3)" . Just try this in Immediate Window. Oct 8 '18 at 11:46

Since 1,4,7.. and 3,6,9.. is on its own a repeatable numbers.. my (favorite) solution is to use 2 extra column to generate the series.. and one to combine them.

using Column C & D as extra column, put :

``````C1  ---> 1
C2  ---> 4
C3  ---> 7
D1  ---> 3
D2  ---> 6
D3  ---> 9
``````

and in E1 put :

``````E1  ---> =SUM(INDIRECT("A"&C1,TRUE):INDIRECT("A"&D1,TRUE))
``````

then drag all 3 columns downwards..

Hope it helps. ( :

• Nicely composed Answer,, perfect 10 ☺ Oct 8 '18 at 11:31
• the trick : `=SUBSTITUTE(CELL("address",C2),"\$","")&" ----> "&FORMULATEXT(C2)` ( : hope it'll be useful. Oct 10 '18 at 3:32