How to Shift Alternate Rows to Columns in Excel3 Easy Methods

Written By:
Adiste Mae

If you’ve ever been required to move odd rows into column/s and even rows into another in Excel, or any other spreadsheet program, you know it can be difficult.

Yet there are various methods that can make this procedure easier.

See the below illustration:

Using Formulas to Change Alternating Rows to Columns

The easiest way to handle issues in Excel, according to the majority of users, is by using formulae.

Here’s how to use a formula to change every other row into a column:

  1.  Insert the formula by inputting =IF(ISEVEN(ROW(B2)),B2,””) in the cell next to your data, press Enter, and then pull the fill handle (at the corner of the cell) to complete the range in which you wish to utilize this formula in. B2 stands for the data you want to acquire. The data for this column has now been collected for each alternate row.
  2. Next, choose the next cell below the column above, enter the formula by inputting =IF(ISODD(ROW(B3)),B3,””) there, and hit Enter. Then pull the fill handle (at the corner of the cell) to complete the range in which you wish to utilize this formula in. The data for this column has now been collected for every third row.

Using VBA, Transform Alternating Rows to Columns

Moreover, you can transfer each alternate row to a column using VBA (Visual Basic for Apps).

This is how:

  1. To view the Windows VBA, press Alt and F11 on your keyboard.
  2. To add another module window, select Insert, and then, Module.
  3. To the window, paste the following VBA code:

 VBA: Shift each alternate row to one column.

Sub MoveRange()

'Updateby20140730A

Dim rng As Range

Dim InputRng As Range, OutRng As Range

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 InputRng = InputRng.Columns(1)

For i = 1 To InputRng.Rows.Count Step 2

    OutRng.Resize(1, 2).Value = Array(InputRng.Cells(i, 1).Value, InputRng.Cells(i + 1, 1).Value)

    Set OutRng = OutRng.Offset(1, 0)

Next

End Sub

4. You can choose the range you wish to move by clicking the Run key or pressing F5 on your keyboard, which will bring up a dialog box.

5. To display the next dialog box and choose one cell to provide the result, click OK. View the image.

Once you click OK, the outcome will appear as seen below.

Using Kutools for Microsoft Excel, Transform Alternating Rows into Columns

When you have Kutools for Microsoft Excel installed, you can easily swiftly convert alternating rows to a column by using the option called “Convert Range.”

Around 300 useful Excel tools are available in Kutools for Excel, which you can test without any restrictions for 30 days. This is how to apply it:

  1. Select Kutools. Then, Range Converter. Lastly, Transform Range after choosing the range you wish to relocate alternating rows in. View the image.
  2. Choose or type 2 from the categories in the Columns per record portion of the Dialog Box under Transform Range by checking One range from the column in the section under Transform Type and Fixed value under Column per record. See illustration:
  3. Clicking OK button will display a window where you may choose just one cell to display the outcome. View the image:
  4. When prompted to choose a single cell to output the result, click OK to activate a dialog box. View the image.