Transpose Multiple Rows in Group to Columns in Excel

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.


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

Paste Special to Transpose Multiple Rows in Group to Columns

Step 2:

  • Now, click Transpose(T).

Using Paste Special to Transpose Multiple Rows in Group to Columns


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.

Using Paste Special to Transpose Multiple Rows in Group to Columns

Step 2:

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

transpose_rows_paste_special_4

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:
=TRANSPOSE(B3:D13)

TRANSPOSE Function to Transpose Multiple Rows in Group to Columns

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

TRANSPOSE Function to Transpose Multiple Rows in Group to Columns

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


Similar Readings


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.

Using INDIRECT function

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.

Using INDEX function

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.

Using ribbon shortcutStep 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.

Using ribbon shortcut

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo