# Transpose Multiple Columns into One Column

This post will guide you how to **transpose multiple columns into a single columns** with multiple rows in Excel. How do I put data from multiple columns into one column with Excel formula; How to transpose columns into single column with VBA macro in excel.

Assuming that you have a data list in range B1:D4 contain 3 columns and you want to transpose them into a single column F. just following the below two ways.

## Transpose Multiple Columns into One Column with Formula

You can use the following excel formula to transpose multiple columns that contain a range of data into a single column F:

**#1** type the following formula in the formula box of cell F1, then press enter key.

=INDEX($B$1:$D$4,1+INT((ROW(B1)-1)/COLUMNS($B$1:$D$4)),MOD(ROW(B1)-1+COLUMNS($B$1:$D$4),COLUMNS($B$1:$D4))+1)

**#2** select cell F1, then drag the Auto Fill Handler over other cells until all values in range B1:D4 are displayed.

**#3** you will see that all the data in range B1:D4 has been transposed into single column F.

## Transpose Multiple Columns into One Column with VBA Macro

You can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly. Just do the following steps:

**#1** click on “**Visual Basic**” command under **DEVELOPER** Tab.

**#2 **then the “Visual Basic Editor” window will appear.

**#3 **click “**Insert**” ->”**Module**” to create a new module.

**#4** paste the below VBA code into the code window. Then clicking “**Save**” button.

Sub transposeColumns() Dim R1 As Range Dim R2 As Range Dim R3 As Range Dim RowN As Integer wTitle = "transpose multiple Columns" Set R1 = Application.Selection Set R1 = Application.InputBox("please select the Source data of Ranges:", wTitle, R1.Address, Type:=8) Set R2 = Application.InputBox("Select one destination single Cell or column:", wTitle, Type:=8) RowN = 0 Application.ScreenUpdating = False For Each R3 In R1.Rows R3.Copy R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True RowN = RowN + R3.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

**#5** back to the current worksheet, then run the above excel macro. Click **Run** button.

**#6** select the source data of ranges, such as: B1:D4

**#7** select one single cell in the destination Column, such as: F1

**#8** let’s see the last result.

### Related Functions

- Excel INDEX function

The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel INT function

The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer. And the INT function rounds down, so if you provide a negative number, the returned value will become more negative.The syntax of the INT function is as below:= INT (number)… - Excel ROW function

The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])…. - Excel Columns function

The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)…. - Excel MOD function

he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….