Transpose Multiple Rows in Group to Columns with Formula in Excel

If you ever need to convert your Rows into Columns and Columns into Rows, this article will help you. Transposing your table in Excel can be messy. But fear not, as we will explain some easy ways you can save your time and transpose multiple Rows in group to Columns using formula in Excel.


📁  Download Excel File


Learn to Transpose Multiple Rows in Group to Columns Using Formula with These 5 Methods in Excel

In order to transpose a dataset, the rows and columns must be switched so that the rows become the columns and the columns are the rows. Explicitly, we will use a dataset of 6 Rows and 4 Columns which will rotate to 4 Rows and 6 Columns. In this article, we not only explained some built-in features offered by Excel but also showed how you can use some functions like TRANSPOSE, INDIRECT, ADDRESS, COLUMN, and ROW to formulate formula to transpose multiple Rows in group to Columns. Again, we tried to create VBA code to avert some of the limitations offered by the functions. Let’s dive into the article.

Dataset to transpose multiple rows in groups to columns


Method 1

1.  Making Use of Built-in Features

For our first method, we will see how Excel’s features work to transpose multiple Rows in groups to columns. Let’s learn about them in detail.

Built-in Features

1.1. Applying Keyboard-Shortcut

The easiest way we can transpose multiple Rows in groups into Columns in Excel is by using keyboard shortcuts.

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range you wish to transpose first. For our case, we selected, B5:E10.
  • Then, press Ctrl+C which will copy the range.
  • Next, select the first cell of your desired output range. We choose B13.
  • Now, press Ctrl+V.

Paste the data to transpose multiple rows in groups to columns

  • Then choose Transpose from the drop-down menu.

Consequently, you’ll obtain the desired outcome.

📕 Read More: Convert Column to Comma Separated List with Single Quotes


Built-in Features

1.2. Utilizing Paste Special

We can also apply the Paste Special option like this.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the entire data range. Then, press Ctrl+C to copy the cells you want.

  • Then, choose the very first cell in the target range. We selected B13. Right-click on the selected cell and the Context Menu will appear. Here, choose Paste Special.

Paste Special to transpose multiple rows in groups to columns

  • Finally, select Transpose in the Paste Special dialogue box and Press OK.

As you’ve just seen, the Paste Special function enables you to change data from row to column (or column to row) in a matter of seconds. Additionally, this approach duplicates the layout of your original data, which strengthens its case even further.


Built-in Features

1.3. Using the Ribbon

On the other hand, Ribbon can offer some assistance in this case.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the range that has to be transposed and then, copy it using Ctrl+C.

Copying the data to transpose multiple rows in groups to columns

  • Then select the first cell of your output range and go to the Home tab and in the left corner of the Ribbon, select A drop-down menu will appear.

Paste and Transpose to transpose multiple rows in groups to columns

  • Finally, press Transpose to see the results.


Method 2

2. Applying TRANSPOSE Function

The TRANSPOSE function, as its name implies, is specifically developed for transposing data in Excel. Let’s learn about the function.

Syntax of the function:

TRANSPOSE(array)

An array is transposed by making the first row of the array the first column of the new array, the second row of the array the second column of the new array, and so on.  Let’s apply the function to our table.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the first cell of the area you want to transpose. We choose B13.
  • Then, write the following formula in the Formula Bar.

=TRANSPOSE(B5:E10)

TRANSPOSE Function to transpose multiple rows in groups to columns

  • Finally, Press Enter to apply the formula.

As shown in the screenshot above, the original table formatting is not retained in the converted table. This is one of the problems with the TRANSPOSE function.  So you have to format it again for the transposed one. After formatting, this is what our table looks like.

Edited TRANSPOSE output to transpose multiple rows in groups to columns


Method 3

3. Employing INDIRECT Function

Another function we can use to create a formula to transpose multiple Rows in group to Columns in Excel that is useful is the INDIRECT function. The INDIRECT function in Excel returns a legitimate cell reference from a text string. When you wish to construct a text value that may be used as a legitimate reference, INDIRECT is beneficial. However, we can also use this function to transpose.

Syntax of the INDIRECT function:

INDIRECT(ref_text, [a1])

The arguments are:

ref text – a text-based reference

a1 – A boolean value that indicates whether the reference is A1 or R1C1. TRUE: The default style is A1.

INDIRECT

3.1. Applying INDRECT with COLUMN Functions

Using the INDIRECT function alone won’t get our job done.  We need to call another function for this purpose.  We will also use the COLUMN function.

Syntax of COLUMN function:

COLUMN([reference])

The COLUMN function gives a reference’s column number. COLUMN(B5), for example, gives 2 because B is the 2nd column in the spreadsheet.  If you don’t provide any references, the function will return the selected cell’s index number.  Let’s just transpose our data range using the INDIRECT and COLUMN functions.

⬇️⬇️ STEPS ⬇️⬇️

  • As usual, First, select the first cell where you want to put your transpose table. We choose B13 and type the following formula and hit Enter.

=INDIRECT(“B”&COLUMN()+3)

INDIRECT and COLUMN functios to transpose multiple rows in groups to columns

  • Next, go to the next row B14 and type this formula, and hit Enter.

=INDIRECT(“C”&COLUMN()+3)

  • Keep this up for every column you have in the original table. So, in cell B15, we type,

=INDIRECT(“D”&COLUMN()+3)

And for cell B16,

=INDIRECT(“E”&COLUMN()+3)

  • Finally, select the 4 cells together and drag them to your right side. Since we have 6 Rows in the original data, we will need to drag the selected cells for another 5 columns.

Drag to transpose multiple rows in groups to columns

See the magic as your Rows turn to Columns and Columns turn to Rows. You also have to edit the formatting of the table in this case.

🔨 Formula Breakdown

👉  In the formula, the COLUMN() function returns the index number of the cell where we are writing the formula.  For example, in case B13, the COLUMN function returns 2.

👉  Next, within the INDIRECT function, we provided the cell reference for our original data.  As our starting cell was in B5, we typed “B”&COLUMN()+3 as the reference.  What this will do is, INDIRECT function will search in the B column, and as for the Row number, it will add 3 to 2 (returned by COLUMN function).  Hence, collecting the ‘Fruit list’ from B5.

📕 Read More: Transpose Formulas Without Changing References in Excel


INDIRECT

3.2. Nesting ADDRESS into INDIRECT Function

We can also nest the ADDRESS function into the INDIRECT function to create a formula to transpose multiple Rows in group to Columns in Excel. Additionally, we will use the COLUMN and ROW functions as well.

Syntax of ADDRESS function:

ADDRESS(row_num, col_num, [abs_num], [a1], [sheet])

The arguments are as followed:

row num – the row number that will be used in the cell address.

col num –  the column number that will be used in the cell address.

abs_number – the address format (i.e. absolute, relative). Absolute is the default.

a1 –  A1 or R1C1 is the reference style. The default style is A1.

sheet – the name of the worksheet that will be used. The current sheet is the default.

Syntax of ROW functions:

ROW([reference])

The Row function is the same as the COLUMN function. It only gives the row index number.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select B13 as this is where our transposed table will start, and type the following formula.

=INDIRECT(ADDRESS(COLUMN(B5)-COLUMN($B$5)+ROW($B$5),ROW(B5)-ROW($B$5)+COLUMN($B$5)))

INDIRECT and ADDRESS functions to transpose multiple rows in groups to columns

  • Press Enter and drag the Fill Handle tool downward. Your table headers will turn into rows.

Dragging downward to transpose multiple Rows in group to columns

  • Finally, select the rows and drag the Fill Handle tool to your right to get the remaining data converted.

As you can see, you will need to format the table again to make it look better.

🔨 Formula Breakdown

👉  The ADDRESS function returns the cell address based on the row and column reference numbers we supply.

👉We input the coordinates in reverse order in our formula, and this is what actually works. In other words, this section of the formula ADDRESS(COLUMN(B5)-COLUMN($B$5)+ROW($B$5),ROW(B5)-ROW($B$5)+COLUMN($B$5)), flips rows to columns, converting a column number to a row number, then a row number to a column number.

👉  Finally, the rotated data is produced via the INDIRECT function.

Method 4

4. From Table

By transforming our data range into a table, we can also transpose. Let’s see how.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select your entire data range as we selected B5:E10.
  • Next, go to Insert and select Table.

Inserting Table to transpose multiple rows in groups to columns

  • Consequently, a dialogue box will show up. You will see the range we selected is housed here. Further, check the box behind My Table has Headers. Then click OK.

  • As a result, your data range has turned into a Table. Next, go to Data Tab and select From Table/Range.

Data tab to transpose multiple Rows in group to columns

  • Thus, a Power Query Editor will show up. Here, go to Transform.

Transform tab in Power Query Editor to transpose multiple Rows in group to columns

  • Then, select Use First Row as Headers and then Use Headers as First Row.

Adding Column to transpose multiple Rows in group to columns

You will notice another Row has appeared before the first Row of your Table.

  • Finally, select Transpose to transpose the table.

Henceforth, your Table has transposed successfully.

method 1

5. Using VBA

If you like, we can use a VBA code to accomplish the same thing. With this in mind, you must first open the Developer tab in order to begin writing your code. To learn how to get to the Developer tab, click here.  Let’s see how we can transpose multiple rows to columns in groups using Excel’s coolest version.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select Visual Basic from the Developer tab.

Visual Basic to transpose multiple Rows in group to columns

  • Next, we will enter our code in the Microsoft Visual Basic for Applications window that appears after choosing Module from the Insert.

  • In the white module, copy and paste the formula below.
Sub Transpose()
  Dim SourceTable As Range
  Dim OutputTable As Range

  Set SourceTable = Application.InputBox(Prompt:="Please Select the Table to Transpose", Title:="Transpose Rows to Columns", Type:=8)
  Set OutputTable = Application.InputBox(Prompt:="Select the First cell of the Output Table", Title:="Transpose Rows to Columns", Type:=8)

  SourceTable.Copy
  OutputTable.Select
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
  Application.CutCopyMode = False

End Sub

Vba code to transpose multiple Rows in group to columns

  • Next, press F5 to execute the code.
  • At this moment. a box will show up demanding the table you want to transpose. Select your table as we selected B5:E10 and press OK.

selecting table to transpose multiple Rows in group to columns

  • Finally, another pop-up box will appear to know the first cell of your output transpose table, provide that and press OK.

selecting destination to transpose multiple Rows in group to columns

Your table will be transposed as shown below.


📄 Important Notes

🖊️  Utilizing the TRANSPOSE function, the transposed table preserves the relationship to the source table, and when the source data changes, it does the transposed table.

🖊️  If any cells in the original table are empty, the rotated cells will display 0 instead.

🖊️  VBA code will retain the original formatting of the Table.


📝 Takeaways from This Article

📌  In the first method, we showed how users can use the built-in features of Excel in a prolific way.

📌  The second method showed the use of the TRANSPOSE function.

📌  The third Method describes how users will use the INDIRECT function with the assistance of the COLUMN and ADDRESS functions.

📌  Users can also transpose data from the property of Table.

📌  Finally, a VBA code was provided to ease the process.


Conclusion

Finally, we have concluded this article.  I hope I articulated this article in simple language. If you have any questions, feel free to comment below.  Moreover, if you know of any easier methods, please share them below. For more tutorials, visit Excelden.com.

(Visited 48 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo