Excel has made data collection very easy. It has lots of functions and formulas that looked very wonderful to use data as per our need. Here we will discuss how to** transpose multiple rows in the group to columns in Excel**. Manually transposing is very stressful work. We will discuss some easy and some typical methods so that all users can get their problem solution from our discussion.

Here, we will have a data set of certain childrenâ€™s Names, their favorite fruit, and fruit IDs.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice sheet to exercise while you are reading this article.

**5 Methods to Transpose Multiple Rows in Group to Columns in Excel**

We will **transpose rows into columns**. Here we will describe 5 methods to do this.

**1. Using Paste Special to Transpose Multiple Rows in Group to Columns**

The easiest way to **transpose rows to columns** is by using **Paste Special**. You can implement this **Paste Special** with both **Ribbon Commands** and **Keyboard shortcuts**.Â

**1.1 Ribbon Commands**

First, We will discuss **Ribbon commands**.

**Step 1:**

- Select the range we want to transpose.
- Then go
**Home**. - From ribbon select
**Copy**. - Select the cell where we will transpose
**.** - From the ribbon go to the
**Paste**drop-down and select**Transpose(T)**.

**Step 2:**

- Now, click
**Transpose(T)**.

**1.2 Keyboard Shortcut**

We can also do this by using **Keyboard Shortcut**.

**Step 1:**

- First, select the range you want to
**transpose.** - Press
**Ctrl + C**. - Select the cell where you will transpose
**.** - Press
**Ctrl + V**.

**Step 2:**

- After that from the drop-down select
**Transpose**marked as red. And you will get the desired transposed result.

**Read more: Excel Paste Transpose Shortcut: 4 Easy Ways to Use**

**2. TRANSPOSE Function to Transpose ****Multiple Rows in Group to Columns**

Excel has a default function to transpose, which is **TRANSPOSE**. You can** transpose** easily by using this function. You need to use function keys for this function.

**Note:** Determine the rows and columns of your data. For example, you have 2 columns and 7 rows. Once you transpose the data it will be 2 rows and 7 columns.

**Step 1:**

- Determine the rows and columns of data. In our dataset, we have 11 rows and 3 columns.
- Select a vacant region in the worksheet which is 3 rows deep and 11 columns wide.
- Press the F2 key to enter edit mode while the region is still selected.
- Write the TRANSPOSE function following the equal sign and we will enter the determined data range to transpose. The formula is:

**Step 2:**

- Press
**Ctrl****+ Shift + Enter**. - You cannot just click the Enter key but
**Ctrl****+ Shift + Enter**as it is an array formula. The formula is:

**{=TRANSPOSE(B3:D13)}**

**Read more:** **How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)**

**Similar Readings**

**How to Transpose Duplicate Rows to Columns in Excel (4 Ways)****How to Convert Columns to Rows in Excel (2 Methods)****Transpose Multiple Columns into One Column in Excel (3 Handy Methods)****How to Reverse Transpose in Excel (3 Simple Methods)**

**3. Using INDIRECT function**

**INDIRECT** is one other function to transpose rows to columns.

Our data is based on 11 rows and 3 columns, which start from cell B3 to D13. Letâ€™s **transpose data** on cell E3. We will put the formula on cell E3 and click **Enter**.

**=INDIRECT(â€śbâ€ť&COLUMN()-2)**

And this formula in E4 and press Enter:

`=INDIRECT("c"&COLUMN()-2)`

And this formula in E5 and press Enter:

`=INDIRECT("d"&COLUMN()-2)`

Now select these three cells and drag the fill handle across to the column. So, there we will get the data transposed. But letâ€™s understand what the formula is doing.

**Note:** Indirect formula helps in converting text strings to cell references. The **COLUMN** function gives us the number of columns in which the formula is entered.

For example, if you put **=COLUMN** in any cell of column A it will give the value of 1 as it is the first column. Here we put the formula on column E, it will return 5 as it is the 5th row.

So, with the column function, we were able to incremental numbers as we drag the formula across columns. But as we inserted the formula in the fifth column and not the first column so it would have returned 5 in which case the first address to be fetched would have been E5. But we wanted the values from the first cell of column E therefore we had to insert â€ś-2â€ť. So, the formula:

**=INDIRECT(â€śBâ€ť&COLUMN()-2)**

**Read more: Excel Transpose Formulas Without Changing References (4 Easy Ways)**

**4. Using INDEX function**

Using **the INDEX function**, we can easily understand how to tackle the data which is arranged in rows to shift in columns. The **INDEX** function is given below:

**INDEX(reference, row_num, [column_num],[area_num])**

for **row_num**: now we will use the **COLUMN()** function to generate numbers so that as we drag the formula towards the right the row number will get updated because we are jumping across columns and thus the **COLUMN** function will fetch the column number. Adjust the value generated by the column number by adding or subtracting constants so that you get the desired result.

for **column_num**: mention 1,2,3 depending on the column within the selected array from which we want the values fetched.

Here our data is based on 3 columns and 11 rows.

I want the data transposed in rows in column E. Therefore, we use this formula cell E3:

**=INDEX($B$3:$D$13,COLUMN()-4,1)**

Here, we use -4 as we are using **columns E**, and 1 for the first column of the range. Now, do the rest of the cells.

For cell E4:

**=INDEX($B$3:$D$13,COLUMN()-4,2)**

For cell E5:

**=INDEX($B$3:$D$13,COLUMN()-4,3)**

Select three cells and drag them to the right until column O.

**Read more: How to Transpose Array in Excel (3 Simple Ways)**

**5. Using ribbon shortcut**

**Step 1:**

- First, select the range you want to transpose.
- Then select Data from the ribbon.
- After that click
**From Table**.

**Step 2:**

- We will see a
**POP UP**that shows the range we selected. - We can also change the range and tick to
**My table has headers**as our table has headers**.** - Then click
**OK**.

**Step 3:**

- Now we will see a new window, where we will select all the columns.
- From the ribbon go to
**Transform**. - Then click
**Transpose**.

**Step 4:**

- Finally, we will get the desired result.

**Step 5:**

- We can also show the transpose by another sheet.

**Read more: Conditional Transpose in Excel (2 Examples)**

**Things to Remember**

When we use the array function, we must remember that we should press **Ctrl**** + Shift + Enter****Â **instead of **Enter**Â only.

**Conclusion**

Here we discussed five methods to transpose multiple rows in the group to columns in Excel**.** Hope it will fulfill everyoneâ€™s needs. If you have any suggestions please inform us.

## Further Readings

**Data clean-up techniques in Excel: Changing vertical data to horizontal data****How to Swap Rows in Excel (2 Methods)****Convert Columns to Rows in Excel (2 Methods)****Excel VBA: Transpose Multiple Rows in Group to Columns****How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)****VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)****Transpose Multiple Rows in Group to Columns in Excel**