We know what rows and columns are in Excel. Basically, data is spread into multiple rows or columns. Now, in this tutorial, we are going to show how you can convert multiple rows to a single column in Excel.

Furthermore, for conducting the session, we will use Microsoft 365 version.

## How to Convert Multiple Rows to a Single Column in Excel: 7 Suitable Ways

First, let us show you a dataset of some people’s names.

Here, you can see, there are already multiple rows in a single column. Actually, we are going to show you how you can convert multiple rows into a single column if there are multiple columns in a single row like in our sample dataset.

Here is our sample dataset. There are multiple rows like** Team Red, Team Blue**, and **Team Green**. Also, rows have multiple columns. So, our goal is to convert it into a single column.

### 1. Use of Excel TOCOL Function for Converting Multiple Rows to a Single Column

Here, you can use the** TOCOL **function to convert multiple rows to a single column in Excel. Actually, this **TOCOL** function is a newly introduced function of the **Microsoft Excel 365** version. Basically, this is the simplest and easiest way to convert multiple rows into a single column. Now, follow the steps.

**Steps:**

- Firstly, you must select a new cell
**G5**where you want to create the single column.*Here, you must keep enough cells in a column to store all the values.* - Secondly, you should use the formula given below in the
**G5**cell.

`=TOCOL(C4:E6)`

Here, in this formula, **C4:E6** is the particular array that will convert into a single column.

- Then, press
**ENTER**.

Finally, you will get the converted column.

### 2. Unite OFFSET, ROUNDUP, ROWS, and MOD Functions in Excel

Here, you can use a combination of some functions to convert multiple rows to a single column in Excel. These functions are **OFFSET**, **ROUNDUP**, **ROWS**, and **MOD** functions.

**Steps:**

- First, write or copy the following formula in cell
**G5**.

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

Here, 3 is the total used column number of the dataset up to which cell the rows will convert into a column.

- Then, press
**ENTER**.

As a result, you will see the result **Jack** which is placed as the first element in our desired column.

**Formula Breakdown**

Eventually, the functions used here might be complex to understand. So, here is a brief of those functions.

**OFFSET**: Actually, it starts from a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.- Lastly, it returns a section from a data set with a specific height and a specific width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.

**ROUNDUP**: Basically, the**ROUNDUP**function rounds up a number to the nearest whole number. Furthermore, it rounds up to a given number of decimal places.- Additionally, it has two arguments. The first one in number that we want to round up. Then, the second one is
**num_digits**, the place in which the position should be rounded up.

- Additionally, it has two arguments. The first one in number that we want to round up. Then, the second one is
**MOD**: Mainly, it gives a reminder after a number is divided by the divisor.- Here,
**MOD**is used to highlight an entire cell.

- Here,
**ROWS**: Actually, it returns the row number of a reference. So, if there are multiple references then it returns all the rows in which those cells are located.

- After that, drag the
**Fill Handle**icon (**+ Sign**) across the column until you get the last value of the last column.

Now, it will look like this.

### 3. Combining INDEX, INT, ROW, COLUMNS, and MOD Functions

Here, you can use another combination of some functions to convert multiple rows to a single column in Excel. These functions are **INDEX**, **INT**, **ROW**, **COLUMNS**, and **MOD** functions.

**Steps:**

- First, write the following formula in cell
**G5**.

`=INDEX($C$4:$E$6,1+INT((ROW(C1)-1)/COLUMNS($C$4:$E$6)),MOD(ROW(C1)-1+COLUMNS($C$4:$E$6),COLUMNS($C$4:$E$6))+1)`

- Consequently, press
**ENTER**.

As a result, you can see the value of cell **C4** in **G5**.

**Formula Breakdown**

**COLUMNS**: Actually, it returns the number of columns in a given range.- Here,
**COLUMNS ($C$4:$E$6)**returns**3**because there are**3**columns in that range.

- Here,
**ROW:**Mainly, this function returns the number of rows from a given reference.- Here,
**ROW(C1)**turns into**1**.

- Here,
**INT**: Actually, it returns the integer portion of a number.- So,
**INT({1}-1)/3)**gives**0**.

- So,
**MOD**: Here, it gives a reminder after a number is divided by the divisor.- Therefore,
**MOD({1}-1+3,3)**turns into**0**.

- Therefore,
**INDEX**: Basically, it returns the value at a given location of an array or range of cells.- Lastly,
**INDEX($C$4:$E$6,1+{0},{0}+1)**gives**Jack**as output.

- Lastly,

- Subsequently, drag the
**Fill Handle**icon across the column until you get the last value of the last column.

Finally, you will get the converted column.

### 4. Merge FILTERXML & TEXTJOIN Functions in Excel

Here, you can unite **FILTERXML** and **TEXTJOIN** functions to change multiple rows to a single column in Excel. So, follow the steps.

**Steps:**

- Firstly, you must select a new cell
**G5**where you want to create the single column.*Here, you must keep enough cells in a column to store all the values.* - Secondly, you should use the formula given below in the
**G5**cell.

`=FILTERXML("<r><n>" & TEXTJOIN("</n><n>",,C4:E6) & "</n></r>","//n")`

**Formula Breakdown**

- Here,
**the TEXTJOIN function**will join some defined text using a delimiter. Where the delimiter is**“</n><n>”**and the text range is**C4:E6**.- So,
**TEXTJOIN(“</n><n>”,,C4:E6)**returns**“Jack</n><n>Rose</n><n>Lily</n><n>Joe</n><n>Bella</n><n>Harry</n><n>Megh</n><n>Peter</n><n>John”**

- So,
- Then,
**the FILTERXML function**gives certain data from**XML**content with the help of a defined path.- Lastly, it converts that row into a column.

- After that, press
**ENTER**to get the result.

**Read More:** Convert Multiple Rows to Single Row in Excel

### 5. Employing TRANSPOSE Function to Convert Multiple Rows to a Single Column

You can use the** TRANSPOSE **function for converting multiple rows into a single column in Excel. However, this is a slow process as you have to convert individual rows into columns. Now, follow the steps.

**Steps:**

- Firstly, you must select a new cell
**G5**where you want to create the single column.*Here, you must keep enough cells in a column to store all the values.* - Secondly, you should use the following formula in the
**G5**cell.

`=TRANSPOSE(C4:E4)`

Here, in this formula, **C4:E4** is the particular row that will convert into a single column.

- Thirdly, press
**ENTER**.

- Then, use the following formula in the
**G8**cell.

`=TRANSPOSE(C5:E5)`

Actually, in this formula, **C5:E5** is the particular row that will convert into a single column.

- Consequently, press
**ENTER**.

- Similarly, write the following formula in the
**G11**cell.

`=TRANSPOSE(C6:E6)`

In the same way, here, **C6:E6** is the particular row that will convert into a single column.

- Then, press
**ENTER**and get the final result.

**Read More:** How to Combine Multiple Rows into One Cell in Excel

### 6. Use of Paste Special Feature in Excel to Change Multiple Rows to a Single Column

Here, you can use the Excel **Paste Special** feature to convert multiple rows into a single column. Additionally, this is a manual process. So, you have to convert individual row by row into columns. For this, follow the steps below.

**Steps:**

- Firstly, select the 1st row (
**C4:E4**). - Secondly, press
**CTRL+C**to copy the values.

- Then, go to the
**G5**cell and**right-click**on that. - After that, from the
**Context Menu Bar**>> choose**Transpose (T)**which is under**Paste Options**.

As a result, you will see the transposed column.

- Similarly, do this for the other rows.

Finally, you will get that converted column.

### 7. Applying Excel VBA Code to Turn Multiple Rows into a Single Column

You can employ a **VBA** code to convert multiple rows into a single column in Excel. The steps are given below.

**Steps:**

- Firstly, you have to choose the
**Developer**tab >> then select**Visual Basic.**

- Now, from the
**Insert**tab >> select**Module**.

- Then, write down the following
**Code**in the Module.

```
Sub Convert_Multi_Rows_To_A_Column()
Dim my_1st_Range As Range, my_2nd_Range As Range, my_Range As Range
Dim my_row_index As Integer
xTitleId = "Range Selection"
Set my_1st_Range = Application.Selection
Set my_1st_Range = Application.InputBox("Select Multiple Rows", xTitleId, my_1st_Range.Address, Type:=8)
Set my_2nd_Range = Application.InputBox("Select a Cell for Output", xTitleId, Type:=8)
my_row_index = 0
Application.ScreenUpdating = False
For Each my_Range In my_1st_Range.Rows
my_Range.Copy
my_2nd_Range.Offset(my_row_index, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
my_row_index = my_row_index + my_Range.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
```

**Code Breakdown**

- Here, we have created a
**Sub Procedure**named*Convert_Multi_Rows_To_A_Column**.* - Next, declare some variables
**my_1st_Range**,**my_2nd_Range**, and**my_Range**as**Range**;**my_row_index**as**Integer**. - Furthermore, I have used an input box named
**Range Selection**. With the help of this box, you can choose your rows and output cell. - After that, I used a
**For Each Loop**to convert all the rows into a single column.

- Now,
**Save**the code then go back to Excel File. - After that, from the
**Developer**tab >> select**Macros**.

- Then, select the
**Macro name (Convert_Multi_Rows_To_A_Column)**and click on**Run**.

As a result, you will see the **Range Selection** box.

- Now, choose your rows in the
**Select Multiple Rows**box. - Then, press
**OK**.

- After that, select a cell for output.
- Then, press
**OK**.

Lastly, you will get your converted column.

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

These formulas are more than enough to give you enough ideas to convert multiple rows to a single column in Excel. Still, if you have queries then please comment below. We will try our best to clear your understanding/ solve your problem.

Just what I needed, thanks so much! I’m still trying to get my head round the ‘Roundup’ and ‘MOD’ element of the offset formula but I managed to manipulate your formula for my data and it worked perfectly.

⇒OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3)) : In the OFFSET formula, you need to put the cell reference, rows and cols number.

⇒OFFSET($B$2………): Here $B$2 is the cell reference of the OFFSET function.

⇒OFFSET($B$2, ROUNDUP(ROWS($1:1)/3,0)-1……): Here, the ROUNDUP function gives the specific number of rows down. ROWS function provides the number of rows in a given array. Here, the cell reference is $1:1. So, the Rows function returns 1. As we have three rows in our dataset. So, divide the return value of the Rows function by 3. It will return 0.333. Then, the ROUNDUP value will round the number into the nearest whole number. The whole number of 0.333 is 1. After that, subtract 1 from it. So, the final value is 0 which is the required rows down.

⇒OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3)): To extract the columns right, we utilize the MOD function. It gives a reminder after a number is divided by the divisor. First, the Rows function provides the number of rows from the given array. Here, it returns 1. Then, subtract 1 from the return value. Then, divide the value by the total number of columns. The MOD function will return the reminder. Here, it returns 0.

So, for cell reference $B$2 and rows 0 down and cols 0 right, it returns Arif as our answer. Do the same for other cases.

Try this solution I think you will get your desired result. If you face any more problems, inform us.