Transpose a Column into Multiple Rows in Excel

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

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.