How to Convert Multiple Rows to Columns in Excel (9 Ways)

If you are looking for some of the easiest ways to convert multiple rows to columns in Excel, then you will find this article helpful. So, let’s dive into the main article.

How to Convert Multiple Rows to Columns in Excel: 9 Easy Ways

Here, we have some records of sales for some of the products for the months from January to May. We will try to convert the rows into columns so that we can visualize the records for the months as column headers and we will utilize this dataset mainly to demonstrate the ways of conversion of multiple rows to columns easily.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Using Transpose Option to Convert Multiple Rows to Columns in Excel

Here, we will use the Transpose option within Paste options to convert the following multiple rows into columns easily.

Steps:
➤ Copy the whole range of the dataset by pressing CTRL+C.

➤ Choose the cell where you want to have the output, Right-click on your mouse, and select the Transpose option from the Paste Options.

Then, you will be able to transpose your data which means converting the rows into columns.

Read More: How to Transpose Multiple Columns to Rows in Excel

Method-2: Conversion of Multiple Rows to Columns Using TRANSPOSE Function

In this section, we are going to use an array function, the TRANSPOSE function, to convert the multiple rows of the following dataset into multiple columns, and to gather the data we have also formatted another table below the main dataset.

Steps:
➤ Type the following formula in cell B10.

Here, TRANSPOSE will change the rows of the range B3:E8 into columns simultaneously.

➤ Press ENTER.
After that, you will get the conversion of the rows into columns like the following figure.

You have to press CTRL+SHIFT+ENTER instead of pressing ENTER for other versions except for Microsoft Excel 365.

Method-3: Using INDIRECT and ADDRESS Functions

Here, we will use the INDIRECT, ADDRESS, ROW, and COLUMN functions to transform the rows of the following dataset into columns.

Steps:
➤ Use the following formula in cell B10.

Here, B3 is the starting cell of the main dataset.

➤ Press ENTER.
➤ Drag the Fill Handle tool to the right side and down.

Finally, you will be able to change multiple rows of the main dataset into multiple columns.

Read More: How to Flip Columns and Rows in Excel

Method-4: Using INDEX Function to Convert Multiple Rows to Columns

In this section, we will use the combination of the INDEX, COLUMN, and ROW functions to convert the multiple rows into columns easily.

Steps:
➤ Apply the following formula in cell B10.

Here, $B$3:$E$8 is the range of the dataset, A1 is used to get the first row and column number of this dataset. We are using the column number for the row number argument and row number as the column number argument to change the rows into columns easily by feeding these values into the INDEX function.

➤ Press ENTER.
➤ Drag the Fill Handle tool to the right side and down.

After that, you will get the conversion of the rows into columns like the following figure.

Method-5: Using INDEX-MATCH Formula

In this section, we are going to use the INDEX function, and MATCH function for converting the multiple rows of the following dataset into columns.

Steps:
➤ Firstly, you have to transpose the first column as the first row of the new table manually.

➤ Type the following formula in cell B11.

Here, $C$3:$C$8 is the second column of the dataset, and $B$3:$B$8 is the first column of the dataset.

➤ Press ENTER and drag the Fill Handle tool to the right side.

Then, you will get the second column of the main dataset as the second row.

Similarly, apply the following formulas to finish the rest of the conversion.

Finally, you will get all of the rows of the first dataset as the columns in the second dataset.

Read More: How to Flip Data from Horizontal to Vertical in Excel

Method-6: Using VLOOKUP Function to Convert Multiple Rows to Columns

In this section, we will be using the VLOOKUP function to transpose multiple rows of the following data table into columns.

Steps:
➤ In the beginning, you have to transpose the first column as the first row of the new dataset manually.

➤ Write down the following formula in cell B11.

Here, $B$3:$E$8 is the range of the dataset, B$10 is the lookup value, and 2 is for looking at the value in the second column of the dataset.

➤ Press ENTER and drag the Fill Handle tool to the right side.

Afterward, you will get the second column of the main dataset as the second row.

In the same way, use the given formulas below to complete the rest of the conversion.

Read More: How to Move Data from Row to Column in Excel

Method-7: Using Power Query

Here, we will use the Power Query to transpose multiple rows into columns easily. But we have to add an extra row at the beginning of the dataset because Power Query will not transform the first row as a column as it considers it as the header.

Steps:
➤ Go to the Data Tab >> Get & Transform Data Group >> From Table/Range Option.

After that, the Create Table wizard will appear.
➤ Select the data range and then click on the My table has headers option.
➤ Press OK.

Then, the Power Query Editor window will appear.

➤ Select all of the columns of the dataset by pressing CTRL and Left-Clicking on your mouse at the same time.
➤ Go to the Transform Tab >> Transpose Option.

You can make the first row of your dataset the header also.
➤ Go to the Transform Tab >> Use First Row as Headers Group >> Use First Row as Headers Option.

Then, you will get the transformed columns from the rows of the main dataset.
➤ To close this window, go to the Home Tab >> Close & Load Group >> Close & Load Option.

In this way, the table in the Power Query Editor window will be loaded to a new sheet named Table5.

Method-8: Converting Multiple Rows to Columns Using VBA Code

In this section, we are going to use a VBA code to convert multiple rows into columns.

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

➤ Write the following code

Sub conversionofmultiplerows()

Dim multiple_rows_range, multiple_columns_range As Range
Set multiple_rows_range = Application.InputBox( _
Prompt:="Choose the range of rows", Title:="Microsoft Excel", Type:=8)
Set multiple_columns_range = Application.InputBox( _
Prompt:="Choose the destination cell", Title:="Microsoft Excel", _
Type:=8)
multiple_rows_range.Copy
multiple_columns_range.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

End Sub

Here, we have declared multiple_rows_range, and multiple_columns_range as Range, and they are set to the range which we will select through the Input Boxes by using the InputBox method.
Then, we will copy the main dataset multiple_rows_range and then paste it as transpose in the destination cell multiple_columns_range.

➤ Press F5.
Then, you will get the input box where you have to select the range of the dataset $B$3:$E$8 in the Choose the range of rows box and press OK.

Then, another input box will pop up.
➤ Select the destination cell $B$10 where you want to have the transposed dataset and then press OK.

Eventually, you will get the transformed columns from multiple rows even with the formatting of the main dataset also like the following.

Read More: VBA to Transpose Multiple Columns into Rows in Excel

Method-9: Conversion of Multiple Rows to Columns and Rows Using OFFSET Function

We have a list containing some students’ names, their subjects, and corresponding marks in multiple rows. Now, we want to convert the first three rows into three different columns of the table beside this list. Similarly, we want to convert the rest of the rows as columns per three rows. So, you can see we need to convert rows into columns and rows at a time.
To do this, we are going to use the OFFSET, ROW, and COLUMN functions.

Steps:
➤ Type the following formula in cell D4.

Here, $B$4 is the starting cell of the list.

➤ Press ENTER.
➤ Drag the Fill Handle tool to the right side and down.

Eventually, you will be able to do the conversion from multiple rows to columns and rows.

Read More: How to Change Vertical Column to Horizontal in Excel

Practice Section

For doing practice by yourself we have provided a practice section like below in a sheet named Practice. Please do it by yourself.

Download Practice Workbook

Conclusion

In this article, we tried to cover the ways to convert multiple rows to columns in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• Excel VBA to Transpose Array
• How to Paste Link and Transpose in Excel

<< Go Back to Transpose Data in Excel | Learn Excel