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:

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**”.

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.

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

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

- After that, click on
**OK**.

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

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 Reverse Transpose in Excel (3 Simple Methods)****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****How to Convert Columns to Rows in Excel (2 Methods)**

## 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**.

As you can see, we got our first value.

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

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

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)`

- After that, press
**Enter**.

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

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.

- After that, click on any cell.

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

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

**How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)****Excel Paste Transpose Shortcut: 4 Easy Ways to Use****How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)****VBA to Transpose Array in Excel (3 Methods)****How to Convert Column to Comma Separated List With Single Quotes****Convert Single Columns to Rows in Excel with Formulas****How to Transpose in Excel VBA (3 Methods)**