Transpose a Column into Multiple Rows in Excel

Add to Favorites

Convert a column of data into multiple rows of data at a given interval. For instance, every 5 rows of data in the column will appear in its own row after being transposed.

To Transpose in Excel means to convert a range of data from a column to a row and vice versa.

The above example is kind of confusing so let's look at an example and then I will show you the macro that is required to do this and what you need to edit to make it work for you.

Sections:

Example

Macro

Notes

Example

Here is a column of data:

I want to transpose this into multiple rows so that each row contains only 5 values.

This is the result:

Macro

Sub transpose_interval()

 

'how many values to put per row

interval = 5

 

'first row of the data that you want to transpose

first_row = 1

'first column of the data that you want to transpose

first_col = 1

 

'first column where you want the data to go

dest_start_col = 3

'first row where you want the data to go

dest_start_row = 1

 

dest_cur_col = dest_start_col

dest_cur_row = dest_start_row

 

last_row = Cells(Rows.Count, first_col).End(xlUp).Row

 

For cur_row = first_row To last_row

 

    Cells(dest_cur_row, dest_cur_col).Value = Cells(cur_row, first_col).Value

 

    dest_cur_col = dest_cur_col + 1

 

    If (cur_row - (first_row - 1)) Mod interval = 0 Then

   

        dest_cur_col = dest_start_col

   

        dest_cur_row = dest_cur_row + 1

   

    End If

 

Next

 

End Sub

What you need to change:

interval = 5 this is the number of values that will be placed on each row when you transpose the column. Change the 5 to whatever interval you want.

first_row = 1 this is the first row of the data that you want to move from a column to a row, transpose. If the data starts in row 1, this should be 1, row 2, this should be 2.

first_col = 1 this is the column that contains the data to transpose. If the data is in column A, this should be 1, column B, this should be 2, etc.

dest_start_col = 3 the column where you want the data to be transposed. 1 is for column A, 2 for column B, etc.

dest_start_row = 1 the row in which you want the transposed data to start filling-in.

This macro will give you the result displayed in the Example section above here.

Notes

This is a simple yet powerful macro. Use it on sample data before you use it on real data so that you fully understand how it will behave and what it will do.

Download the sample file attached to this tutorial to test out the macro.


Downloadable Files: Excel File

Similar Content on TeachExcel
Center Titles Across Multiple Cells in Excel
Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...
Split Text into Multiple Cells in Excel
Tutorial: How to split text from one cell into multiple cells quickly and easily in Excel.  This inc...
How to use Vlookup Across Multiple Worksheets in Excel
Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Combine Values from Multiple Cells into One Cell in Excel
Tutorial: There are two easy ways to combine values from multiple cells in Excel. In order to do thi...
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...