How to Move Every Other Row to Column in Excel (6 Ways)

Sometimes we need to move every odd or even row to other cells in the worksheets. Obviously, it can be done manually. But if the worksheet size is too big, then this manual process can put a heavy toll. If you are curious to know how you can move every other row to column in Excel, then this article may come in handy for you. In this article, we discuss how you move every other row to column in Excel with elaborate explanations.

How to Move Every Other Row to Column in Excel: 6 Easy Ways

The dataset presented below will be used for the demonstration. In this dataset, we have product information with product id and their corresponding profits.

1. Using IF, ISEVEN and ROW Functions

We will use ISEVEN, ROW, and IF functions to move every other row to another column in Excel.

Steps

• We have the dataset, in which the profit column value we want to move every one-row interval to column D.

• Then select cell D5 and enter the following formula:

• Drag the Fill Handle to cell D16.
• Doing this will fill the range of cell D5:D16 with the alternate value after every 1 row.

• Then select cell E5 and enter the following formula:

• Drag the Fill Handle to cell E16.
• Doing this will fill the range of cell E5:E16 with the alternate value after every one row.

Read More: How to Move Rows to Columns in Excel

2. Combining IF, ISODD and ROW Functions

We will use ISODD, IF, and ROW functions to move every other row to another column in Excel.

Steps

• Select cell D5 and enter the following formula:

• Drag the Fill Handle to cell D16.
• Doing this will fill the range of cell D5:D16 with the alternate value after every row.

• Then select cell E5 and enter the following formula:

• Drag the Fill Handle to cell E16.
• Doing this will fill the range of cell E5:E16 with the alternate value after every row.

3. Combination of INDEX, ROW and COLUMN Functions

Using the combination of INDEX, ROW, and COLUMN functions, we can move other rows and columns in Excel.

Steps

• Then select cell D5 and enter the following formula:

• Drag the Fill Handle in the corner of cell D5 to cell E5.
• Doing this will input the alternate value of cell D5 into cell E5.

• Then drag the Fill Handle in the corner of cell E5 to cell E16.
• Doing this will fill the range of cell D5:D10 with the interval of 2 rows of the Profit column.
• Subsequently, it will also fill the range of cell E5:E10 with the alternate values of the D5:D16.

4. Applying IF Function

In this process, we are going to use the IF function in order to move every other row to a column.

Steps

• To begin with, select cell D5 and type x.
• This x will work as a marker for which row values we want to move.

• Next, keep the next cell D6 below the D5 blank, as we do not want to move this row value.
• At the same time, select cells D5 and D6 by dragging the Fill Handle.

• Then while selecting the D5 and D6 altogether, drag the Fill Handle again to cell D16.

• Doing this will fill the range of cells D5:D16 with the x alphabet with a one-row interval.

• After inputting the x values, select cell E5 and enter the following formula:

This formula put the first value of the Profit column.

• Drag the Fill Handle to cell E16.
• Doing this will fill the range of cell E5:E16 with the alternate value after every row.

• To enter the alternate values of the range of cells E5:E16.
• Select cell F5 and enter the following formula:

• Drag the Fill Handle to cell F16.
• Doing this will fill the range of cell F5:F16 with the alternate value after every 1 row, starting with the first row as blank.

Read More: Move Row to Bottom in Excel If Cell Contains a Value

5. Utilizing Go To Special Feature

Go to Special is a useful feature that enables the user to do multiple different tasks at the same time. Here we will benefit from the feature by using it to move every other row to a column.

Steps

• Like the previous method, we need to put x in cell B5 and then keep the immediate below cell B6 blank.
• This x will work as a marker for which row values we want to move.
• Next, keep the next cell B6 below the B5 blank, as we do not want to move this row value.
• And at the same time, select cells B5 and B6 by dragging the Fill Handle.

• Doing this will fill the range of cells B5:B18 with the x alphabet with a one-row interval.
• After this step, go to the Home tab > Editing group.
• Then from this group, go to Find & Select > Go to Special.

• In the dialog box, select Blanks.
• Click OK after this.

• Next, all the Blank cells in the range of cell B5:B18.
• Then select any of those selected Blank cells, and then right-click on the mouse.
• From the context menu, click on Delete.

• Then in the Delete dialog box, select Entire row, and click OK.

• Then all the rows in the range of cells B5:B18, containing blank cells, will be removed.
• And as we put x in alternate cells, only the alternate values are now showing.

• Repeating the same previous process, again input x in the original dataset.
• But this time keep blank in the first cell, and input x in immediately below the cell B14.
• At the same time, select cells B14 and B15 by dragging the Fill Handle.
• Then while selecting the B14 and B15 altogether, drag the Fill Handle again to cell D16.
• Doing this will fill the range of cells D14:D25 with the x alphabet with a one-row interval.

• And in a similar process, delete those blank cells
• It can be done by selecting those blank cells manually.
• Or select them using the Go To Special Feature mentioned before.
• After deleting the blank cells, we got the alternate value of the profit column.

Read More: How to Move Rows Down in Excel

6. Embedding VBA Code

Using a VBA formula could expedite the process of moving every other cell to another column in Excel.

Steps

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.

• Next, in the Module editor window, type the following formula:

Sub Move_every_other_row_to_column()
Dim x As Range
Dim y As Range, z As Range
xTitleId = "Exceldemy"
Set y = Application.Selection
Set y = Application.InputBox("Select Range of Cells as Input:", xTitleId, y.Address, Type:=8)
Set z = Application.InputBox("Output Cell :", xTitleId, Type:=8)
Set y = y.Columns(1)
For i = 1 To y.Rows.Count Step 2
z.Resize(1, 2).Value = Array(y.Cells(i, 1).Value, y.Cells(i + 1, 1).Value)
Set z = z.Offset(1, 0)
Next
End Sub

• Then close the Module window.
• After that, go to the View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the macros that you created just now. The name here is Move_every_other_row_to_column. Then click Run.

• After pressing Run, you will notice that a range box will appear.
• In that range box, you need to select the range of cells that are going to be moved alternately to another cell. We select $C$5:$C$16 and click OK after that.

• After clicking OK, there will be another range box asking for the output cell address. In this case, it is $D$5.
• Click OK after this.

• Then we will have the alternate value of the values in the range of cells D5:E10.

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the issue of how we can move every other row to a column is answered here by 6 different methods. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Move Rows Up in Excel
• How to Rearrange Rows in Excel
• How to Move Rows in Excel Without Replacing
• How to Move Row to Another Sheet Based on Cell Value in Excel

<< Go Back to Move Rows | Rows in Excel | Learn Excel