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.


Download Practice Workbook

Download this practice workbook below.


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

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

How to Move Every Other Row to Column in Excel


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.

Using IF, ISEVEN and ROW Functions to Move Every Other Row to Column in Excel

  • Then select cell D5 and enter the following formula:

=IF(ISEVEN(ROW(C5)),C5,"")

  • 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.

first set of alternate row moved to another column

  • Then select cell E5 and enter the following formula:

=IF(ISEVEN(ROW(C5)),"",C5)

  • 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.

Alternate rows moved to column by applying IF, ISEVEN, AND ROW functions

🔎 Breakdown of the Formula

IF(ISEVEN(ROW(C5)),C5,””)

  • ROW(C5): This formula will return the row value of the C5 cell address.
  • ISEVEN(ROW(C5)): This formula will return logical TRUW if the output of the ROW function is even. Otherwise, it will return FALSE.
  • IF(ISEVEN(ROW(C5)),C5,””): This function will check whether the first argument ISEVEN function return is logical TRUE or FALSE. If the first argument is TRUE, then it will execute the second argument, which is cell C5, otherwise, it will input blank.

✎ IF(ISEVEN(ROW(C5)),””,C5)

  • ROW(C5): This formula will return the row value of the C5 cell address.
  • ISEVEN(ROW(C5)): This formula will return logical TRUE if the output of the ROW function is even. Otherwise, it will return FALSE.
  • IF(ISEVEN(ROW(C5)),C5,””): This function will check whether the first argument ISEVEN function return is logical TRUE and FALSE. If the first argument is TRUE, then it will execute the second argument, which is a blank cell, otherwise, it will input C5.

Read More: How to Move Rows to Columns in Excel (4 Effective Ways)


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:

=IF(ISODD(ROW(C5)),C5,"")

Combining IF, ISODD and ROW Functions to Move Every Other Row to Column in Excel

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

Alternate rows are moved in a separate column

  • Then select cell E5 and enter the following formula:

=IF(ISODD(ROW(C5)),"",C5)

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

all of the column are alternately moved to a new column

🔎 Breakdown of the Formula

✎ IF(ISODD(ROW(C5)),C5,””)

  • ROW(C5): This formula will return the row value of the C5 cell address.
  • ISODD(ROW(C5)): This formula will return logical TRUE if the output of the ROW function is odd. Otherwise, it will return FALSE.
  • IF(ISODD(ROW(C5)),C5,””): This function will check whether the first argument ISODD function return is logically TRUE and  FALSE. If the first argument is TRUE, then it will execute the second argument, which is cell C5, otherwise, it will input blank.

✎ IF(ISODD(ROW(C5)),””,C5)

  • ROW(C5): This formula will return the row value of the C5 cell address.
  • ISODD(ROW(C5)): This formula will return logical TRUE if the output of the ROW function is odd. Otherwise, it will return FALSE.
  • IF(ISODD(ROW(C5)),””, C5): This function will check whether the first argument ISODD function return is logically TRUE and FALSE. If the first argument is TRUE, then it will execute the second argument, which is a blank cell, otherwise, it will input C5.

Read More: How to Move Multiple Rows in Excel (3 Quick Methods)


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:

=INDEX($C:$C,ROW(C2)*2-1.5+COLUMN(C3))

Combination of INDEX, ROW and COLUMN Functions to Move Every Other Row to Column in Excel

  • 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.

drag the fill handle to neighbour cell

  • 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.
  • And subsequently, it will also fill the range of cell E5:E10 with the alternate values of the D5:D16.

Using INDEX, ROW AND COLUMN functions to move every row to column

🔎 Breakdown of the Formula

 INDEX($C:$C,ROW(C2)*2-1.5+COLUMN(C3))

  • ROW(C2): This formula will return the row value of the C2 cell address.
  • COLUMN(C3): This formula will return the column address value of the C3 cell address.
  • INDEX($C:$C,ROW(C2)*2-1.5+COLUMN(C3)): The INDEX function takes The column C as the range argument. Then it will multiply the output of the ROW function by 2 and add the return of the COLUMN function. And subtract 1.5 from it. This way it will ensure the alternate output value.

Read More: How to Move Rows in Excel (4 Simple & Quick Methods)


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.

Applying IF Function to to Move Every Other Row to Column in Excel

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

select 2 row that are going to drag.

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

drag the fill handle to the 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:

=IF(D5="x",C5,"")

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 one row.

move alternate row to a new column.

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

=IF(D5="x","",C5)

  • 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.

first set of alternate row moved to another column

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 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.

Utilizing Go To Special Feature to Move Every Other Row to Column in Excel

  • 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.

deleting the blank cells.

  • 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.
  • And 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.

using go to feature, we can move every other row to column

Read More: How to Move Rows Down in Excel (6 Ways)


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. Or You can also press ‘Alt+F11’ to open the Visual Basic Editor.

Embedding VBA Code to Move Every Other Row to Column in Excel

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

Inserting the 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 View tab > Macros.
  • Then click on View Macros.

opening macro window

  • 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 then.

Selecting range of cells in the range box to move every other row to column in Excel

  • 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.

enter the output cell location to move the every other row .

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

the output of the VBA code showing that the every other row are now moved to column


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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo