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

Moving every odd or even row to other locations can obviously be done manually by cutting/copying and pasting. But for large worksheets, the process can become prohibitively cumbersome. In this article, we’ll discuss some more efficient ways of moving every other row to a different column in Excel.

The dataset presented below, containing the product id and corresponding profits of certain products, will be used to demonstrate our methods.

How to Move Every Other Row to Column in Excel


Method 1 – Using IF, ISEVEN and ROW Functions

Our goal here is to copy the value of every other row in the Profit column to column D.

Steps:

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

  • Select cell D5 and enter the following formula:

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

  • Drag the Fill Handle to cell D16 to fill the range with every alternating value.

first set of alternate row moved to another column

  • Select cell E5 and enter the following formula:

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

  • Drag the Fill Handle to cell E16.

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

Breakdown of the Formula

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

  • ROW(C5): Returns the row value of the C5 cell address.
  • ISEVEN(ROW(C5)): Returns logical TRUE if the output of the ROW function is even. Otherwise, it will return FALSE.
  • IF(ISEVEN(ROW(C5)),C5,””): Checks 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 return a blank cell.

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

  • ROW(C5): Returns the row value of the C5 cell address.
  • ISEVEN(ROW(C5)): Returns logical TRUE if the output of the ROW function is even. Otherwise, it will return FALSE.
  • IF(ISEVEN(ROW(C5)),C5,””): Checks 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 a blank cell, otherwise, it will return the value of C5.

Read More: How to Move Rows to Columns in Excel


Method 2 – Combining IF, ISODD and ROW Functions

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.

Alternate rows are moved in a separate column

  • Select cell E5 and enter the following formula:

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

  • Drag the Fill Handle to cell E16.

all of the column are alternately moved to a new column

Breakdown of the Formula

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

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

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

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

Method 3 – Combining INDEX, ROW and COLUMN Functions

Steps

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

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.

It will also fill the range of cell E5:E10 with the alternate values to 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): Returns the row value of the C2 cell address.
  • COLUMN(C3): Returns the column address value of the C3 cell address.
  • INDEX($C:$C,ROW(C2)*2-1.5+COLUMN(C3)): The INDEX function takes column C as the range argument. Then it multiplies the output of the ROW function by 2 and adds the return of the COLUMN function and subtracts 1.5 from it, ensuring the alternate output value.

Method 4 – Using the IF Function

Steps

  • Select cell D5 and type “x“, which will work as a marker for the row values we want to move.

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

  • Keep the next cell D6 below the D5 blank, as we do not want to move this row value.
  • Select cells D5 and D6.

select 2 row that are going to drag.

  • With both D5 and D6 selected, drag the Fill Handle to cell D16.

drag the fill handle to the cell D16

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

  • Select cell E5 and enter the following formula:

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

This formula returns the first value of the Profit column.

  • Drag the Fill Handle to cell E16, which will fill the range of cell E5:E16 with the alternate value after every 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, which 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


Method 5 – Using the Go To Special Feature

Go to Special is a useful feature that enables the user to do multiple different tasks at the same time.

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 the row values we want to move.
  • Select cells B5 and B6.

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

  • Drag the Fill Handle to cell B18, which will fill the range of cells B5:B18 with the x with a one-row interval.
  • Go to the Home tab > Editing group.
  • Go to Find & Select > Go to Special.

  • Select Blanks.
  • Click OK.

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

deleting the blank cells.

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

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 process, input “x” in the original dataset, but this time keep the first cell B14 blank, and input the “x” in cell B15 immediately below.
  • Select cells B14 and B15.
  • While both B14 and B15 are selected, drag the Fill Handle again to cell D16, which this will fill the range of cells D14:D25 with the x with a one-row interval.

Now, in a similar process, to delete those blank cells:

  • Either select them manually, or use the Go To Special Feature discussed above.

After deleting the blank cells, we get 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


Method 6 – Embedding VBA Code

Steps

  • Go to the Developer tab and click on Visual Basic.
  • Alternatively, press ‘Alt+F11’.
  • If you don’t see the Developer tab, you’ll have to enable it.

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

A new dialog box opens.

  • In that dialog box, click on Insert > Module.

Inserting the module

  • In the Module editor window, enter 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

  • Close the Module window.
  • Go to the View tab > Macros.
  • Click on View Macros.

opening macro window

  • Select the macros that you just created. The name here is Move_every_other_row_to_column.
  • Click Run.

A range box will appear.

  • Select the range of cells that are going to be moved alternately to another cell. We select $C$5:$C$16.
  • Click OK.

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

Another range box opens, asking for the output cell address.

  • Enter $D$5.
  • Click OK.

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

Voila, we 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


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo