Transpose a Column into Multiple Rows in Excel

Author:

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.

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.

Question? Ask it in our Excel Forum

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 in...
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...
Increment a Value Every X Number of Rows in Excel
Tutorial: How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial...
Tutorial Details