How to Transpose Multiple Rows in Group to Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsft Excel has made data collection very easy. It has lots of functions and formulas that can be used 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. In this article, I will try to explain 5 suitable ways to transpose multiple rows in group to columns in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.


Download Practice Workbook

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


5 Suitable Ways to Transpose Multiple Rows in Group to Columns in Excel

In order to transpose rows into columns, we will describe 5 suitable ways to do so. We can use Paste Special command, the TRANSPOSE, INDIRECT, INDEX functions, and the Form Table features. To clarify the methods, I have used the dataset with sales records in the Employee ID, Name, and Sales columns.

Excel Transpose Multiple Rows in Group to Columns


1. Use Paste Special Feature to Transpose Multiple Rows in Group to Columns

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


1.1 Use Paste Special Feature from Ribbon

First, we will discuss the Paste Special feature from Ribbon.

Steps:

  • Select the range (i.e. B4:D9) that you want to transpose.
  • Then fro your Home tab, go to,

Home → Clipboard → Copy

Use Paste Special from Ribbon

  • After that, select the cell where you want to transpose (i.e. B12).
  • From the ribbon, go to the Paste drop-down and select Transpose(T).

  • Thus, we can simply transpose rows to columns by using the Paste Special feature.

Excel Transpose Multiple Rows in Group to Columns


1.2 Utilize Keyboard Shortcut

We can also do the similar task by using Keyboard Shortcut.  Let’s march forward.

Steps:

  • First, select the entire range (i.e. B4:D9) you want to tanspose.
  • Press  Ctrl + C .

Keyboard Shortcut

  • After that, select the cell where you will transpose.
  • Press  Ctrl + V .

  • After that from the drop-down, select Transpose marked as red.

Excel Transpose Multiple Rows in Group to Columns

  • Finally, you will get the desired transposed result.

Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use


2. Apply 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.

Steps:

  • Select a cell first (i.e. B12).
  • Now, insert the following formula in that cell to have transposed output.
=TRANSPOSE(B4:D9)

Apply TRANSPOSE Function to Transpose Multiple Rows in Group to Columns

  • Press the ENTER button to have the result.

  • You can modify the transposed results according to your choice.

Apply 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. Transpose Multiple Rows in Group to Columns with INDIRECT Function

The INDIRECT function is another function that can be used to transpose multiple rows in group to columns. The whole process is described in the following section.

Steps:

  • Write the following formula in your preferred location and press ENTER to have the first value of your transposed data.

.

=INDIRECT(ADDRESS(COLUMN(B4) - COLUMN($B$4) + ROW($B$4), ROW(B4) - ROW($B$4) + COLUMN($B$4)))

Transpose Multiple Rows in Group to Columns with INDIRECT Function

  • Now, use AutoFill Handle across the horizontal line to have automatically filled transposed data.

  • Next, move the AutoFill Handle across the vertical line to have the entire transposed data.

  • Now, modify the transposed results according to your choice.

Transpose Multiple Rows in Group to Columns with INDIRECT Function

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


4. Utilize INDEX Function to Transpose Multiple Rows in Group to Columns

Using the INDEX function, we can easily understand how to tackle the data which is arranged in rows to shift in columns. To understand the process in detail, go through the following steps.

Steps:

  • Insert the following formula in your preferred location and press ENTER to have the first value of your transposed data.
=INDEX($B$4:$D$9,COLUMN()-1,1)

Utilize INDEX Function to Transpose Multiple Rows in Group to Columns

  • To have automatically filled transposed data, use AutoFill Handle across the horizontal line.

  • Similarly, input the following formula and drag along horizontally to have transposed data for the rest of the rows.
=INDEX($B$4:$D$9,COLUMN()-1,3)

Utilize INDEX Function to Transpose Multiple Rows in Group to Columns

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


5. Transpose Multiple Rows in Group to Columns Using Form Table Feature

Another simple yet smart way to transpose multiple rows to columns is to use the Form Table feature. Follow the following procedures to execute the purpose.

Steps:

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

Transpose Multiple Rows in Group to Columns Using Form Table Feature

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

  • Now, we will see a new window named Powe Query Editor.
  • Followingly, select all the columns.
  • Next, go to Transform.
  • Then, click Transpose from the ribbon.

Transpose Multiple Rows in Group to Columns Using Form Table Feature

  • Now, we will have the entire data transposed.

  • Close the Powe Query Editor and we will have the transposed data in a new sheet.

Transpose Multiple Rows in Group to Columns Using Form Table Feature

Read More: Conditional Transpose in Excel (2 Examples)


Conclusion

At the end of this article, I like to add that I have tried to explain 5 suitable ways to transpose multiple rows in group to columns in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo