How to Convert Multiple Columns into a Single Row in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, converting multiple columns to a single row is one of the familiar tasks. You will find yourself in a lot of situations where you will have to convert rows columns. You can also call it to transpose columns rows in Excel. There are many instances that you can find on the internet. In this tutorial, you will learn to convert multiple columns to a single row in Excel. This tutorial will be on point with suitable examples and proper illustrations. So, stay with us.


Download Practice Workbook

Download this practice workbook.


2 Ways to Convert Multiple Columns into a Single Row in Excel

In the following sections, I am going to show you two methods to convert multiple columns to a single row in Excel. I recommend you learn and apply all these methods to your dataset. It will surely develop your Excel knowledge.

To demonstrate this, we are going to use this dataset:

After converting them to a single row, our output will be like the following:

Ways to Convert Multiple Columns into a Single Row in Excel

Now, let’s dive into it.


1. Convert Multiple Columns into a Single Row with Formula

Now, the first method we are showing you is using the formula. This formula will combine multiple functions of Excel in it. The functions are:

 

If you are unfamiliar with these functions, I recommend you learn these functions first. It will give you a better understanding of our formula to convert multiple columns to a single row in Excel.

Here, you will find the dataset in the sheet named “Dataset”.

Convert Multiple Columns into a Single Row with Formula

Now, we will convert these columns to a single row in another sheet “SingleRow

📌 Steps

  • First, go to the “SingleRow” sheet and click on Cell A1.

  • Now, type the following formula:

=OFFSET(Dataset!$B$4,((ROW()-1)*3)+(FLOOR(COLUMN()-1,3)/3),(COLUMN()-1)-(FLOOR(COLUMN()-1,3)))

  • After that, press Enter.

  • As you can see, our first data is pasted here.

Convert Multiple Columns into a Single Row with Formula

  • After that, drag the Fill Handle icon over to the right until you get the 0. Remember, 0 means the end of the dataset. You don’t have to include it.

Ways to Convert Multiple Columns into a Single Row in Excel

As you can see, we are successful in converting multiple columns to a single row in Excel.

🔎 Breakdown of the Formula

Dataset!$B$4:  It specifies the sheet name and the range reference that you want to use.

(ROW()-1)*3: Here, 3 stands for the row number.

(COLUMN()-1,3)/3:  Like the row, 3 also indicates the column number here.

Now, you can change these values according to your needs. Based on your rows and columns.

Read More: Convert Columns to Rows in Excel Using Power Query


2. VBA Codes to Convert Multiple Columns into a Single Row

Now, if you love to solve Excel problems using Microsoft Visual Basic for Applications, you will like this one. This method will effectively convert multiple columns to a single row in Excel. Let’s do the following steps.

📌 Steps

  • First, press Alt+F11 on your keyboard to open the Visual Basic editor.
  • Then, click on the Insert > Module option.

  • After that, type the following code:
Sub convert_to_a_single_row()

Dim input_data As Range, output_range As Range

xTitleId = "Convert Multiple Rows to a Single Column"
Set input_data = Application.Selection
Set input_data = Application.InputBox("Select Your Range :", xTitleId, input_data.Address, Type:=8)

Set output_range = Application.InputBox("Enter the Cell where you want to paste:", xTitleId, Type:=8)

Application.ScreenUpdating = False

num_of_rows = input_data.Rows.Count
num_of_columns = input_data.Columns.Count

For i = 1 To num_of_rows
    input_data.Rows(i).Copy output_range
    Set output_range = output_range.Offset(0, num_of_columns + 0)
Next

Application.ScreenUpdating = True

End Sub
  • Then, save the file.
  • After that, press Alt+F8 on your keyboard to open the Macro dialog box.
  • Next, select convert_to_a_single_row.
  • Then, click on Run.
  • Now, select the range of cells from the dataset (your multiple columns).

VBA Codes to Convert Multiple Columns into a Single Row in Excel

  • After that, click on OK.

VBA Codes to Convert Multiple Columns into a Single Row in Excel

  • Now, go to the “VBA” sheet and select any cell to start with.
  • Finally, click on OK.

VBA Codes to Convert Multiple Columns into a Single Row in Excel

As you can see, we are successful to convert multiple columns to a single row in Excel

🔎 Breakdown of the VBA Code

Set input_data = Application.Selection

Set input_data = Application.InputBox("Select Your Range :", xTitleId, input_data.Address, Type:=8)

This chunk of code takes your input of multiple columns in your dataset.

Set output_range = Application.InputBox("Enter the Cell where you want to paste:", xTitleId, Type:=8)

It takes the input of the cell where you want to convert your multiple columns to a single row.

num_of_rows = input_data.Rows.Count

num_of_columns = input_data.Columns.Count

These codes count the number of rows and columns of your input dataset.

For i = 1 To num_of_rows

    input_data.Rows(i).Copy output_range

    Set output_range = output_range.Offset(0, num_of_columns + 0)

Next

Finally, we are running a loop that will take each value from your dataset and paste them into a cell. After that, it takes the next value and paste that in the same row but in the next column.

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


Similar Readings


How to Convert Multiple Columns into Single Column in Excel

Similar to the previous methods, you can also combine multiple columns into a single column in Excel. Our output will be in a single column.

We are using the previous dataset to demonstrate these methods:


1. Convert Multiple Columns into Single Column Using Formula

Now, the first method we are showing you is using the formula. This formula will combine multiple functions of Excel in it. The functions are:

If you are unfamiliar with these functions, I recommend you learn these functions first. It will give you a better understanding of our formula to convert multiple columns to a single column in Excel.

📌 Steps

  • First, create a new column where you want to paste your multiple columns.
  • After that, select Cell F5 and type the following formula:

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

  • Now, press Enter.

How to Convert Multiple Columns into Single Column in Excel

As you can see, we got our first value.

  • Now, drag the Fill Handle down until you get #REF!.

How to Convert Multiple Columns into Single Column in Excel

  • Here, you don’t have to include the #REF!. It means the end of the dataset.

How to Convert Multiple Columns into Single Column in Excel

As you can see, we are successful to combine multiple columns into a single column in Excel.

🔎 Breakdown of the Formula

  • COLUMNS($B$4:$D$6): The COLUMNS function inside the MOD function here returns the total number of columns available in the range ($B$4:$D$6).
  • ROW(A1)-1+COLUMNS($B$4:$D$6): The mixture of ROW and COLUMNS functions here represents the dividend of the MOD
  • MOD(ROW(A1)-1+COLUMNS($B$4:$D$6), COLUMNS($B$4:$D$6))+1: This part represents the column number of the INDEX For the result, the function returns ‘1’.
  • 1+INT((ROW(A1)-1)/COLUMNS($B$4:$D$6)): This is the row number of the INDEX We specify this in the part where the INT function rounds up the resulting value to the integer form.

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


2. CONCATENATE Function to Convert Multiple Columns into Single Column

Now, you want to combine multiple columns into a single column but want to intact the data of the rows. That means you will convert to a single column, but the cells will contain the exact data.

To solve this, we will use the CONCATENATE function to convert multiple columns to a single column in Excel.

To demonstrate this, we are using the following dataset:

Here, you can see we have commas(,) after each data. We did it to make our result understandable. It is not mandatory.

📌 Steps

  • First, select Cell E5 and type the following formula:

=CONCATENATE(B5,C5,D5)

How to Convert Multiple Columns into Single Column in Excel

  • After that, press Enter.

How to Convert Multiple Columns into Single Column in Excel

  • Now, drag the Fill handle icon over the range of cells E6:E9.

How to Convert Multiple Columns into Single Column in Excel

As you can see, we are successful to convert multiple columns to a single column in Excel.

Now, there are other ways to combine multiple columns into a single column. To know all of these, read: Combine Multiple Columns into One Column in Excel

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


How to Transpose Multiple Rows in Excel

Now, you can transpose multiple rows of your dataset. That means your rows will be columns and columns will be the rows in Excel.

Take a look at the dataset:

As you can see from the dataset, we have multiple rows here. Now, we are going to transpose them using the following steps:

📌 Steps

  • First, select and copy the whole dataset.

How to Transpose Multiple Rows in Excel

  • After that, click on any cell.

How to Transpose Multiple Rows in Excel

  • Now, from the Home tab, you will find the Paste Click on it and select the Transpose (T) command.

How to Transpose Multiple Rows in Excel

After that, it will transpose the multiple rows of your dataset. Now, there are other ways that you perform the same thing. To know more about this, read: Transpose Multiple Rows in Group to Columns in Excel.

Read More: How to Transpose Rows to Columns in Excel (5 Useful Methods)


💬 Things to Remember

The Visual Basic code will convert multiple columns with their formatting also.

Make sure to check those functions we used in the formula. Otherwise, it will be difficult to understand if you are unknown to them.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to convert multiple columns into a single row in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo