We now know what rows and columns are in Excel. Data is spread into multiple rows or columns. In this tutorial, we are gonna show how you can convert multiple rows into a single column.

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

You can see, there are already multiple rows in a single column. 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 our sample dataset.

Here is our sample dataset. There are multiple rows. Also, rows have multiple columns. Our goal is to convert it into a single column.

**Download Practice Workbook**

**2 Ways to Convert Multiple Rows to A Single Column in Excel**

In this tutorial, we are gonna use two functions. One is **OFFSET** and another is **INDEX **to convert multiple rows to a single column in Excel.

**1. Using OFFSET Function**

The formula we are using:

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

*The functions used here might be complex to understand. So, here is a brief of those functions. Make sure you click those function’s links to get very useful and elaborate information.*

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

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 function rounds up a number to the nearest whole number. It rounds up to a given number of decimal places.

It has two arguments. The first one in number that we want to round up. The second one is num_digits, the place in which the position should be rounded up.

It gives a reminder after a number is divided by the divisor. Here **MOD** is used to highlight an entire cell.

It returns the row number of a reference. If there are multiple references then it returns all the rows in which those cells are located.

First, write or copy this formula in **Cell F5**.

Then press **ENTER**. You will see the result Arif which is placed as the first element in our desired column.

Drag the **Fill Handle** (the + Sign) across the column until you get the last value of the last column. Now it will look like this.

**Similar Readings:**

**Convert Multiple Rows to Single Row in Excel (Easiest 5 Methods)****How to Convert Columns to Rows in Excel (2 Methods)****Combine Multiple Rows into One Cell in Excel**

**2. Using INDEX Function **

The formula we are using :

`=INDEX($B$2:$D$4,1+INT((ROW(B2)-2)/COLUMNS($B$2:$D$4)),MOD(ROW(B2)-2+COLUMNS($B$2:$D$4),COLUMNS($B$2:$D4))+1)`

It returns the value at a given location of an array or range of cells. Its basic syntax is:

**=INDEX(reference, row_num, [column_num], [area_num])**

Here, **reference** is the range of cells.

**row_num** is the position of the row of the desired value.

**column_num** is the position of the column.

**area_num** is the range in reference that should be used.

It returns the integer portion of a number.

It returns the number of columns in a given range. For example, COLUMNS (B3:D3) returns 3 because there are 3 columns in that range.

The process is also the same as our previous one. Copy the formula in cell F5.

Press **ENTER**. You can see the value of cell **B2** in **F5**

Drag the **Fill** **Handler** (+ Sign) across the column to copy the formula and get all the values of the row into one column.

**Conclusion**

These formulas are more than enough to give you enough idea to convert multiple rows to a single column in Excel. Make sure you check all those functions given in the formulas from our website** Exceldemy.com**. It will give you a vast idea about those functions and make your understanding better.

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.