# Excel - Split one columns values into 2 columns, split on every 2nd row

I have this data set which i want to split into two columns using a formula. I know i can use something like

``````=MOD(ROW(A1),2)
``````

down along the adjacent column, which puts a 1 or 0 in each row which i can then filter.

I wonder if there is a simpler method using a formula?

``````A
B
C
D
E
F
``````

I want to end up with this in another 2 columns.

``````A  A  B
B  C  D
C  E  F
D
E
F
``````

I have tried `=OFFSET(\$A\$2,2,-1)` in Column B1 and `=OFFSET(\$A\$2,4,-1)` in cell B2, dragging down does not increment properly, i need to increment the "2" by 2 on each new row.

One formula.

Put this in B1 copy over and down.

``````=INDEX(\$A:\$A,(ROW(\$A1)-1)*2+COLUMN(A\$1))
``````

Unlike INDIRECT and OFFSET, this is not volatile. • this is a great answer, for simplicity it wins – platinums Mar 23 '18 at 14:43

Use Indirect.

In B1 use this formula:

``````=INDIRECT("A"&(ROW(B1)*2-1))
``````

In C1 use this formula

``````=INDIRECT("A"&(ROW(C1)*2))
``````
• yes perfect, Thanks Phil - I had used indirect in the past, had a sneaking suspicion it would work for this. – platinums Mar 23 '18 at 14:41

You just need to add a row count mulitplied by 2, into your OFFSET,

In B1 you can use:

``````=OFFSET(\$A\$1,(ROWS(\$A\$1:A1)-1)*2,0)
``````

In C1 you can use:

``````=OFFSET(\$A\$2,(ROWS(\$A\$1:A1)-1)*2,0)
``````

Then drag them both down.