How to transpose / convert columns and rows into single column?

When you use Excel worksheet, sometimes, you will meet this problem: how could you convert or transpose a range of data into a single column? (See the following screenshots:) Now, I introduce three quick tricks for you to solve this problem.

Transpose/Convert columns and rows into single column with formula

Transpose/Convert columns and rows into single column with Kutools for Excel

Transpose/Convert columns and rows into single column with VBA code

Transpose/Convert columns and rows into single column with formula

The following long formula can help you quickly transpose a range of data into a column, please do as this:

1. First, define a range name for your range of data, select the range data that you want to convert, right click and choose Define Name form the context menu. In the New Name dialog box, enter the range name you want. Then click OK. See screenshot:

2. After specify the range name, then click a blank cell, in this example, I will click cell E1, and then input this formula: =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1).

Note: MyData is the range name of the selected data, you can change it as you need.

3. Then drag the formula down to the cell until the error information is displayed. All the data in the range has been transposed into a single column. See screenshot:

Transpose/Convert columns and rows into single column with Kutools for Excel

Maybe the formula is too long to remember and the VBA code has some limitation for you, in this situation, please don’t worry, here I will introduce you an easier and more multifunctional tool-Kutools for Excel, with its Transform Range utility, and you can solve this problem quickly and conveniently.

After free installing Kutools for Excel, please do as below:

1. Select the range that you want to transpose.

2. Click Kutools > Transform Range, see screenshot:

3. In the Transform Range dialog box, select Range to single column option, see screenshot:

4. Then click OK, and specify a cell to put the result from the pop out box.

5. Click OK, and the multiple columns and rows data has been transposed into a one column.

If you want to convert a column to a range with fixed rows, you also can use the Transform Range function to quickly handle it.

Transpose/Convert columns and rows into single column with VBA code

With the following VBA code, you can also join the multiple columns and rows into a single column.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

Sub ConvertRangeToColumn()
'Updateby20131126
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Press F5 key to run the code, and a dialog is displayed for you to select a range to convert. See screenshot:

4. Then click Ok, and another dialog is displayed to select a singel cell to put out the result, see screenshot:

5. And click Ok, then the cell contents of the range are converted to a list of a column, see screenshot:

Related articles:

How to change row to column in Excel?

How to transpose / convert a single column to multiple columns in Excel?

How to transpose / convert columns and rows into single row?