Transpose Multiple Columns into One Column in Excel (3 Handy Methods)

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.

Transpose Multiple Columns into One Column in Excel


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. 👇

Combine INDEX, INT, MOD, ROW, and COLUMNS Functions in Excel to Transpose Multiple Columns into One

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. 👇

Use a Formula Consolidating OFFSET, CEILING, MOD, ROW, and COLUMNS Functions

🔎 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.

Combine OFFSET, ROUNDUP, MOD, and ROWS Functions

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


Similar Readings


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

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

❹ Press the F5 key to run the code.

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

❻ Press OK.

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

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

❽ Press OK.

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

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.

Transpose Data in Multiple Columns into One Column Using Power Query

The Create Table window will appear.

❸ Mark My table has headers checkbox and press OK.

Transpose Data in Multiple Columns into One Column Using Power Query

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.

Transpose Data in Multiple Columns into One Column Using Power Query

You get this. 👇

Transpose Data in Multiple Columns into One Column Using Power Query

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

Transpose Data in Multiple Columns into One Column Using Power Query

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

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo