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:
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 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.
2. Using INDEX Function
The formula we are using :
It returns the value at a given location of an array or range of cells. Its basic syntax is:
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 into F5
Drag the Fill Handler (+ Sign) across the column to copy the formula and get all the values of the row into one column.
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.