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)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, ROW and COLUMNS functions 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