Convert Multiple Rows to A Single Column in Excel (2 ways)

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.

Convert Multiple Rows to A Single Column in Excel Dataset

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.

Main Dataset to convert


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.

OFFSET

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

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 position should be rounded up.

MOD

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

ROWS

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.

Formula in the cell to convert

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

Formula Result to convert

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.

Copy Formula to convert rows into column

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)

INDEX

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.

INT

It returns the integer portion of a number.

COLUMNS

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.

INDEX formula to convert rows into column

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

Result of index formula

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

Drag the index formula


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 into the formulas from our website Exceldemy.com. It will give you a vast idea about those functions and make your understandings better.


Related Articles

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo