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.

Download Workbook


9 Ways to Convert Multiple Rows to Columns in Excel

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.

convert multiple rows to columns in Excel

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.

convert multiple rows to columns in Excel

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

Transpose option

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

Transpose option

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

convert multiple rows to columns in Excel

Read More: Excel Macro: Convert Multiple Rows to Columns (3 Examples)


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.

convert multiple rows to columns in Excel

Steps:
➤ Type the following formula in cell B10.

=TRANSPOSE(B3:E8)

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

TRANSPOSE Function

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

TRANSPOSE Function

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

Read More: How to Transpose Column to Multiple Rows in Excel (6 Methods)


Method-3: Using INDIRECT and ADDRESS Functions

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

convert multiple rows to columns in Excel

Steps:
➤ Use the following formula in cell B10.

=INDIRECT(ADDRESS(COLUMN(B3) - COLUMN($B$3) + ROW($B$3), ROW(B3) - ROW($B$3) + COLUMN($B$3)))

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

  • COLUMN(B3)returns the column number of cell B3
    Output → 2
  • COLUMN($B$3)returns the column number of cell $B$3 (the absolute referencing will fix this cell)
    Output → 2
  • ROW($B$3)returns the row number of cell $B$3 (the absolute referencing will fix this cell)
    Output → 3
  • ROW(B3) → returns the row number of cell B3
    Output → 3
  • COLUMN(B3) - COLUMN($B$3) + ROW($B$3) becomes
    2-2+3 → 3
  • ROW(B3) - ROW($B$3) + COLUMN($B$3) becomes
    3-3+2 → 2
  • ADDRESS(COLUMN(B3) - COLUMN($B$3) + ROW($B$3), ROW(B3) - ROW($B$3) + COLUMN($B$3)) becomes
    ADDRESS(3, 2) → returns the reference at the intersection point of Row 3 and Column 2
    Output → $B$3
  • INDIRECT(ADDRESS(COLUMN(B3) - COLUMN($B$3) + ROW($B$3), ROW(B3) - ROW($B$3) + COLUMN($B$3))) becomes
    INDIRECT(“$B$3”)returns the value of the cell $B$3.
    Output → Month

INDIRECT & ADDRESS Functions

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

INDIRECT & ADDRESS Functions

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

convert multiple rows to columns in Excel

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)


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

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

convert multiple rows to columns in Excel

Steps:
➤ Apply the following formula in cell B10.

=INDEX($B$3:$E$8,COLUMN(A1),ROW(A1))

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.

INDEX Function

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

INDEX Function

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

convert multiple rows to columns in Excel

Read More: How to Add Multiple Rows and Columns in Excel (Every Possible Way)


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.

convert multiple rows to columns in Excel

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

INDEX, MATCH Functions
➤ Type the following formula in cell B11.

=INDEX($C$3:$C$8,MATCH(B$10,$B$3:$B$8,0))

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

  • MATCH(B$10,$B$3:$B$8,0) becomes
    MATCH(“Month”,$B$3:$B$8,0)returns the row index number of the cell with a string Month in the range $B$3:$B$8
    Output → 1
  • INDEX($C$3:$C$8,MATCH(B$10,$B$3:$B$8,0)) becomes
    INDEX($C$3:$C$8,1)returns the first value of the range $C$3:$C$8
    Output → Orange

INDEX, MATCH Functions

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

INDEX, MATCH Functions

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

INDEX, MATCH Functions

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

=INDEX($D$3:$D$8,MATCH(B$10,$B$3:$B$8,0))

INDEX, MATCH Functions

=INDEX($E$3:$E$8,MATCH(B$10,$B$3:$B$8,0))

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

convert multiple rows to columns in Excel

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


Similar Readings


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.

convert multiple rows to columns in Excel

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

VLOOKUP Function

➤ Write down the following formula in cell B11.

=VLOOKUP(B$10,$B$3:$E$8,2,FALSE)

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.

convert multiple rows to columns in Excel

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

VLOOKUP Function

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

VLOOKUP Function

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

=VLOOKUP(B$10,$B$3:$E$8,3, FALSE)

VLOOKUP Function

=VLOOKUP(B$10,$B$3:$E$8,4, FALSE)

convert multiple rows to columns in Excel

Read More: How to Add Rows and Columns in Excel (3 Easy Methods)


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.

convert multiple rows to columns in Excel

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

Power Query

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

Power Query

Then, the Power Query Editor window will appear.

convert multiple rows to columns in Excel

➤ 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.

Power Query

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.

Power Query

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.

Power Query

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

convert multiple rows to columns in Excel

Read More: How to Switch Rows and Columns in Excel (5 Methods)


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.

convert multiple rows to columns in Excel

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

VBA Code

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

VBA Code

After that, a Module will be created.

VBA Code

➤ 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.

convert multiple rows to columns in Excel

➤ 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.

VBA Code

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.

VBA Code

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

convert multiple rows to columns in Excel

Read More: How to Switch Rows and Columns in Excel Chart (2 Methods)


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.

convert multiple rows to columns in Excel

Steps:
➤ Type the following formula in cell D4.

=OFFSET($B$4,COLUMN()-4+(ROW()-4)*3,0,1,1)

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

  • COLUMN()returns the column number of cell D4 where the formula is being applied.
    Output → 4
  • COLUMN()-4 becomes
    4-4 → 4 is subtracted because the starting cell of the formula is in Column 4.
    Output → 0
  • ROW() → returns the row number of cell D4 where the formula is being applied.
    Output → 4
  • (ROW()-4)*3 becomes
    (4-4)*3 → 4 is subtracted because the starting cell of the formula is in Row 4 and multiplied with 3 as we want to transform 3 rows into columns each time.
    Output → 0
  • OFFSET($B$4,COLUMN()-4+(ROW()-4)*3,0,1,1) becomes
    OFFSET($B$4,0+0,0,1,1)
    OFFSET($B$4,0,0,1,1) → OFFSET will extract the range with a height and width of 1 starting from cell $B$4.
    Output → Joseph

OFFSET function

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

OFFSET function

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

OFFSET function

Read More: Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)


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.

practice


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 Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo