# 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

##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

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...
Transpose Every X Number of Rows in Excel - Advanced Transpose Technique
Tutorial: How to Transpose groups of rows from a large column of data; this allows you to do things...
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...
Tutorial Details