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.

**Table of Contents**hide

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

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

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

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

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

** 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

becomes`COLUMN(B3) - COLUMN($B$3) + ROW($B$3)`

**2-2+3 → 3**

becomes`ROW(B3) - ROW($B$3) + COLUMN($B$3)`

**3-3+2 → 2**

becomes`ADDRESS(COLUMN(B3) - COLUMN($B$3) + ROW($B$3), ROW(B3) - ROW($B$3) + COLUMN($B$3))`

`ADDRESS(3, 2) →`

`returns the reference at the intersection point of`

`Row 3`

`and`

`Column 2`

**Output →**$B$3

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

returns the value of the cell`INDIRECT(“$B$3”)`

→**$B$3**.**Output →**Month

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

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

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

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

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

`=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.

becomes`MATCH(B$10,$B$3:$B$8,0)`

returns the row index number of the cell with a string`MATCH(“Month”,$B$3:$B$8,0)`

→*Month*in the range**$B$3:$B$8****Output →**1

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

returns the first value of the range`INDEX($C$3:$C$8,1)`

→**$C$3:$C$8****Output →**Orange

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

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

`=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.

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

**Similar Readings**

**[Fixed!] Rows and Columns Are Both Numbers in Excel****How to Hide Rows and Columns in Excel (10 Ways)****Excel VBA: Set Range by Row and Column Number (3 Examples)**

__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**.

`=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.

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

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

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

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

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

** 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:** **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**.

** 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`

becomes`COLUMN()-4`

`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`

becomes`(ROW()-4)*3`

`(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`

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

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