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.

In the sample dataset that we will use to demonstrate the methods, we have some records of sales for some products for the months from January to May. Let’s convert the rows into columns so that we can visualize the months as column headers.

convert multiple rows to columns in Excel

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


Method 1 – Using the Transpose Option

We can 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

The data is transposed, which means the rows are converted into columns.

convert multiple rows to columns in Excel

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


Method 2 – Using the TRANSPOSE Function

We can use an array function, the TRANSPOSE function, to convert multiple rows into multiple columns. To gather the data, we have also formatted another table below the main dataset.

convert multiple rows to columns in Excel

Steps:

  • Enter 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. *

The conversion of the rows into columns is performed, like in the following figure.

TRANSPOSE Function

* Press CTRL+SHIFT+ENTER instead of ENTER for other versions except for Microsoft Excel 365.


Method 3 – Using INDIRECT and ADDRESS Functions

We can use the INDIRECT, ADDRESS, ROW, and COLUMN functions to transform our rows 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

The formula will change multiple rows of the main dataset into multiple columns.

convert multiple rows to columns in Excel

Read More: How to Flip Columns and Rows in Excel


Method 4 – Using the INDEX Function

We can also use the combination of the INDEX, COLUMN, and ROW functions.

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, and A1 is used to get the first row and column number of this dataset. We use 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

We get the conversion of the rows into columns like in the following figure.

convert multiple rows to columns in Excel


Method 5 – Using the INDEX-MATCH Formula

convert multiple rows to columns in Excel

Steps:

  • Transpose the first column as the first row of the new table manually.

INDEX, MATCH Functions

 

  • Enter 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

We 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))

We 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 Flip Data from Horizontal to Vertical in Excel


Method 6 – Using the VLOOKUP Function

Steps:

  • Transpose the first column as the first row of the new dataset manually.

VLOOKUP Function

  • Enter 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

We get the second column of the main dataset as the second row.

VLOOKUP Function

  • In the same way, use the formulas given 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 Move Data from Row to Column in Excel


Method 7 – Using Power Query

To use the Power Query to transpose multiple rows into columns easily, 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

The Create Table wizard will appear.

  • Select the data range.
  • Click on the My table has headers option.
  • Press OK.

Power Query

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

We can make the first row of our dataset the header too.

  • Go to the Transform Tab >> Use First Row as Headers Group >> Use First Row as Headers Option.

Power Query

We 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

The table in the Power Query Editor window will be loaded to a new sheet named Table5.

convert multiple rows to columns in Excel


Method 8 – Using VBA Code

We can 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

The Visual Basic Editor will open up.

  • Go to the Insert Tab >> Module Option.

VBA Code

A Module will be created.

VBA Code

  • Enter 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 paste it as transposed in the destination cell multiple_columns_range.

convert multiple rows to columns in Excel

  • Press F5.

An input box will pop up.

  • Select the range of the dataset $B$3:$E$8 in the Choose the range of rows box and press OK.

VBA Code

Another input box will pop up.

  • Select the destination cell $B$10 where you want to have the transposed dataset and press OK.

VBA Code

We get the transformed columns, even including the formatting of the main dataset.

convert multiple rows to columns in Excel

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


Method 9 – Using the OFFSET Function

Say 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 next to this list. Similarly, we want to convert the rest of the rows as columns per three rows. So, we need to convert rows into columns and rows at the same time.
To do this, we are going to use the OFFSET, ROW, and COLUMN functions.

convert multiple rows to columns in Excel

Steps:

  • Enter 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

The conversion from multiple rows to columns and rows takes place.

OFFSET function

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


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo