How to split a column every other row in Excel?

For example, I have a long list of data, and now, I want to split the column into two lists equally by every other row as following screenshot shown. Are there any good ways to deal with this task in Excel?

Split a column every other row with Formulas

Split a column every other row with VBA code

Split a column every other row with Kutools for Excel

Split a column every other row with Formulas

The following formulas may help you to quickly split a column into two columns by every other row, please do as follows:

1. Enter this formula into a blank cell, C2, for example, =INDEX($A$2:$A$13,ROWS(C$1:C1)*2-1), see screenshot：

2. Then drag the fill handle down until the errors are displayed in the cells, see screenshot:

3. Then enter another formula into cell D2, =INDEX($A$2:$A$13,ROWS(D$1:D1)*2), and drag the fill handle down to the cells until error values appears, and the column values have been split into two columns every other row, see screenshot:

Split a column every other row with VBA code

If you are interested in VBA code, here, I can talk about a code for you to solve this problem.

1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code:Split a column into two columns every other row

Sub SplitEveryOther()
'Updateby Extendoffice
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Dim index As Integer
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
num1 = 1
num2 = 1
For index = 1 To InputRng.Rows.Count
    If index Mod 2 = 1 Then
        OutRng.Cells(num1, 1).Value = InputRng.Cells(index, 1)
        num1 = num1 + 1
    Else
        OutRng.Cells(num2, 2).Value = InputRng.Cells(index, 1)
        num2 = num2 + 1
    End If
Next
End Sub	

3. Then press F5 key to run this code, and a prompt box will pop out to remind you select the data range that you want to split, see screenshot:

4. And click OK, another prompt box is popped put to let you select a cell to put the result, see screenshot:

5. Then click OK, and the column has been split into two columns by every other row. See screenshot:

Split a column every other row with Kutools for Excel

If you want to learn more new things, I can recommend a powerful tool -- Kutools for Excel, with its Transform Range utility, you can quickly convert a single row or column to a range of cells and vice versa.

After installing Kutools for Excel, please do as follows:

1. Select the column data that you want to split into two columns by every other row.

2. Then click Kutools > Range > Transform Range, see screenshot:

3. In the Transform Range dialog box, select Single column to range under the Transform type, then choose Fixed value and enter 2 into the box in the Rows per record section, see screenshot:

4. Then click Ok button, and a prompt box will pop out to remind you select a cell where you want to output the result, see screenshot:

5. Click OK, the list data has been split into two columns every other row.

Click to know more about this Transform Range utility.