This article will show how to **transpose** multiple columns into one column in Excel using Excel formulas, VBA codes, and the Power Query tool. The following image highlights the purpose of this article.

## Download Practice Workbook

Click on the following button and download the Excel workbook for free for your practice.

## 3 Effective Methods to Transpose Multiple Columns into One Column in Excel

Here we are working with a list of several groups of people. For some reason, we need to turn the columns into one column in a transposed order. We are going to use 3 different Excel formulas, a VBA code, and the power query tool for this. So without any further discussion, let’s dive into the methods.

### 1. Use Excel Formula to Transpose Multiple Columns into One Column

#### 1.1 Combine INDEX, INT, MOD, ROW, and COLUMNS Functions

In our very first method, we will use a formula formed by combining the **INDEX, INT, MOD, ROW,** and **COLUMNS** functions.

**Steps:**

❶ Go to **cell F5** and enter the following formula.

`=INDEX($B$5:$D$8,1+INT((ROW(B5)-5)/COLUMNS($B$5:$D$8)),MOD(ROW(B5)-5+COLUMNS($B$5:$D$8),COLUMNS($B$5:$D$8))+1)`

**Note:**

In the first formula, you have to put **ROW(A1)-1)** instead of **ROW(B5)-5)** if your dataset starts from **cell A1**.

❷ Hit the **ENTER** key and drag the **fill handle** icon until the last entry appears in the one-column list.

Here, the following screenshot describes the full procedure in brief and the result too. 👇

However, the formula looks not easy. So we will explain it below.

**🔎 Formula Explanation:**

- The
**INDEX**function has**3**arguments here. The array, the row number, and the column number. - The
**INDEX**function counts the row number starting from the first row in the given range. So, in our given array, the 5th row will be considered as the 1st row for the**INDEX**function here. **$B$5:$D$8**is the reference array here.**Output:**{“Damien”,”Jackob”,”Nick”;”Chris”,”Tom”,”Rose”;”Martin”,”Harry”,”Peter”;”James”,”Ron”,”George”}.

**1+INT((ROW(B5)-5)/COLUMNS($B$5:$D$8))**This part refers to the row number argument. As we can see from the dataset, we have 4 rows here.

**COLUMNS($B$5:$D$8)****Output:**4.**ROW(B5)-5****Output:**0.**(ROW(B5)-5)/COLUMNS($B$5:$D$8)****Output:**0.**1+INT((ROW(B5)-5)/COLUMNS($B$5:$D$8))****Output:**1.

As we copy the formula down, the output**ROW(B5)-5**part will become 4 at cell F9. So,**(ROW(B5)-5)/COLUMNS($B$5:$D$8)**, this part will return 4/4 = 1 this time, and 1+1= 2 will be returned by**1+INT((ROW(B5)-5)/COLUMNS($B$5:$D$8))**part. So the**INDEX**function will look for value in the second row in its given range.

**MOD(ROW(B5)-5+COLUMNS($B$5:$D$8),COLUMNS($B$5:$D$8))+1**This refers to the column number for the**INDEX**function here. The**MOD**function has two arguments here, number & divisor. The**ROW(B5)-5+COLUMNS($B$5:$D$8)**part supplies the number argument, and the**COLUMNS($B$5:$D$8)**part supplies the divisor argument. The**MOD**function returns the remainder after division.**Output:**{1}.

**INDEX({“Damien”,”Jackob”,”Nick”;”Chris”,”Tom”,”Rose”;”Martin”,”Harry”,”Peter”;”James”,”Ron”,”George”},{1},{1})****Output:**Damien.

#### 1.2 Use a Formula with OFFSET, CEILING, MOD, ROW, and COLUMNS Functions

In the second method, we will use another formula that combines the **OFFSET, CEILING, MOD, ROW,** and **COLUMNS** functions.

**The Generic Formula:**

**=OFFSET(Starting Cell’s absolute reference, CEILING((ROW(Starting cell reference)-m)/Number of columns,1), MOD(ROW(Starting cell reference)+Number of columns-n, Number of columns)**

Where,

**m**= Starting cell’s row number+1; *if your dataset’s column number is even*

**m**= Starting cell’s row number+2; *if your dataset’s column number is odd*

**n**= a number that makes the result of **(ROW(Starting cell ref.)+Number of columns-n)** completely divisible by the number of columns.

Now, execute the following steps.

**Steps:**

❶ Copy the following formula and paste it into **cell F5**.

`=OFFSET($B$4,CEILING((ROW(B5)-4)/COLUMNS($B$5:$D$8),1),MOD(ROW($B5)+COLUMNS($B$5:$D$8)-2,3))`

❷ Press **ENTER.**

❸ Finally pull the **Fill Handle** icon all the way.

The whole procedure is described briefly in the following picture. 👇

**🔎 Formula Explanation:**

- The
**OFFSET**function has**3**compulsory arguments here: reference, rows, and columns. **$B$4**is the reference of the**OFFSET**function from which the function will start offsetting.- The
**CEILING((ROW(B5)-4)/COLUMNS($B$5:$D$8),1)**part refers to the rows argument.**Output:**{1}.

- The
**MOD(ROW($B5)+COLUMNS($B$5:$D$8)-2,3)**part refers to the columns argument.**Output:**{0}.

**OFFSET($B$4,{1},{0})****Output:**{“Damien”}.

#### 1.3 Combine OFFSET, ROUNDUP, MOD, and ROWS Functions

This is the last method in this article that uses a formula. We will combine the **OFFSET, ROUNDUP, MOD,** and **ROWS** functions in this formula.

**The Generic Formula:**

**=OFFSET(Absolute Ref. of the cell situated just above the starting cell, ROUNDUP((ROWS($X:X))/Number of columns, 0), MOD(ROWS($X:X)+n, Number of columns)**

Where,

**X**= The starting row of the dataset (not the heading)

**n**= a number that makes the output of **ROWS($X:X)+n** completely divisible by the number of columns in your dataset.

**Steps:**

❶ Type the formula below in **cell F5** and press **ENTER.**

`=OFFSET($B$4,ROUNDUP(ROWS($5:5)/3,0),MOD(ROWS($5:5)-1,3))`

❷ Drag the **Fill Handle** icon all the way.

Finally, you will see the result.

**Read More:** **Excel VBA: Transpose Multiple Rows in Group to Columns**

**Similar Readings**

**How to Transpose Rows to Columns Based on Criteria in Excel****How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)****How to Transpose Rows to Columns in Excel (5 Useful Methods)****How to Transpose Columns to Rows In Excel (6 Methods)**

### 2. Use an Excel VBA Code to Transpose Multiple Columns into One Column

In the fourth method, we will see a **VBA code** that helps us in transposing several columns into one column. Let’s apply the following steps.

**Steps:**

❶ Press **Alt+F11**.

❷ Go to the **Insert** tab and select **Module** to open a new **VBA module.**

❸ Copy the following VBA code and paste it into the opened module.

```
Option Explicit
Sub ConvertMultColumnsintoOne()
Dim Xrng1 As Range, Xrng2 As Range, Xrng As Range
Dim index_row As Integer
Dim xTitleId As String
xTitleId = "Convert Multiple Columns to One"
Set Xrng1 = Application.Selection
Set Xrng1 = Application.InputBox("Source Columns:", xTitleId, Xrng1.Address, Type:=8)
Set Xrng2 = Application.InputBox("Transpose to (one column):", xTitleId, Type:=8)
index_row = 0
Application.ScreenUpdating = False
For Each Xrng In Xrng1.Rows
Xrng.Copy
Xrng2.Offset(index_row, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
index_row = index_row + Xrng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
```

❹ Press the **F5 key** to run the code.

❺ Select the Source Columns, i.e., **B5:D8**.

❻ Press **OK.**

❼ Now select the cell, i.e., **F5,** where you want to place the **transposed data.**

❽ Press **OK.**

Finally, you will get the results like this. 👇

**Read More:** **VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)**

### 3. Use the Power Query Tool

In this part of the article, we will work on a dataset that is different from the one we have used so far. Here we have product names, sizes, and quantities in different columns. What we need to do is- gather product, size, and quantity data in single columns. To do this, we will use the **Power Query** tool.

**Steps:**

❶ First of all, select the whole dataset or any cell in your dataset.

❷ Go to the **Data** tab and press the **From Table/Range** button from the **Get & Transform Data** group.

The **Create Table** window will appear.

❸ Mark **My table has headers** checkbox and press **OK.**

The **Power Query Editor** window will appear.

❹ Now, go to the **Transform** tab and **select the first column** of our data.

❺ Click on **Unpivot Columns** drop-down menu from **Any Column** group.

❻ Select **Unpivot Other Columns** option from the list.

You get this. 👇

❼ Now, go to the **File** tab and press the **Close & Load To…** button.

The following window will appear.

❽ Select your suitable location, existing or new worksheet.

❾ Finally, press **OK.**

Here is your result. 👇

As I have chosen the New Worksheet option, my results are placed on a new sheet.

**Read More:**** Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)**

## Things to Remember

- You have to modify the formulas according to your dataset size.
- Getting
**#REF!**Error or**0**starting means you have added up all the entries into one column already.

## Conclusion

To conclude, we have discussed 5 effective methods to **transpose** multiple columns into one column in Excel with proper illustrations. If you have any confusion or queries, please ask us in the comment box. Visit our site ExcelDemy to read more Excel-related articles.

**Related Articles**

**How to Transpose Duplicate Rows to Columns in Excel (4 Ways)****How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)****Excel VBA: Transpose Multiple Rows in Group to Columns****Convert Columns to Rows in Excel Using Power Query****How to Convert Columns to Rows in Excel Based On Cell Value****VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)****How to Convert Column to Comma Separated List With Single Quotes**

Hi Mahdy,

Thanks for your tips. However, is it possible to add column G to look up which group these name are from?

Hello

HENRY,I hope you are doing well and thank you for your query. You can use the following formula to see which group these names are from.

=INDEX($B$4:$D$4,MOD(ROW(B5)-5,COLUMNS($B$4:$D$4))+1)The formula uses

INDEX,MOD,ROWandCOLUMNSfunctions to look up the values in cells within column F and returns the corresponding group names in column G.Here is an image displaying the result in an Excel sheet:

Regards

Maruf Niaz || ExcelDemy