How to Transpose Multiple Rows in Groups to Columns in Excel

Transposing multiple rows in groups to columns in Excel means converting repeated data entries into a horizontal format. Instead of having the same category listed multiple times with different values, you can rearrange them. By doing that each category appears once, and related data is shown across columns.

For example, if you have monthly sales listed in separate rows for each product, transposing them turns those rows into columns. This makes your dataset cleaner, easier to read, and ready for reporting or analysis.

In this article, you’ll learn how to transpose multiple rows in groups to columns in Excel using several methods.

In the dataset below, we have monthly sales records for different products. Each product appears multiple times, once for each month. Column A lists Products, Column B shows the Month, and Column C contains Sales.

We’ll convert this data so that each product appears once in a row, and the months appear as separate columns.

The TRANSPOSE function is a simple and built-in way to convert vertical data into a horizontal layout in Excel. It’s helpful when you have grouped rows and want to display them as separate columns.

In our dataset, the products Laptop, Phone, Tablet appear three times, once for each month. We want to display these three sales values of each product in a single row with columns for Jan, Feb, and Mar.

Here’s how to do it step by step:

➤ Open your dataset in Excel.
➤ Enter the following formula in cell E2 for Laptop:

➤ Press  Ctrl  +  Shift  +  Enter  if you’re using older versions of Excel. In newer versions, pressing Enter is enough.
➤ Now the three sales values will appear horizontally.

➤ Repeat the same for each product. For example:

For Phone, enter this following formula in cell F3:

And, for Tablet, enter this following formula in cell F4:

The INDEX function combined with SEQUENCE is a powerful way to transpose multiple rows into columns dynamically. This method works more efficiently when you have several groups, and it updates automatically when the source data changes.

Here’s how to do it step by step:

➤ Create a list of unique products in a new column. For example, list them in column E like Laptop, Phone, Tablet similar to the previous method.
➤ In cell F2, enter this formula:

➤ After typing the formula, press Enter. The three monthly sales values for Laptop will now appear horizontally across three columns.

➤ Drag the fill handle down to copy the formula for the other products.

If you want a quick and easy way to convert grouped rows into columns without using formulas, Excel’s Transpose feature in Paste Special is a great option. This method is manual but works well for small datasets or one-time tasks.

Here’s how to do it:

➤ Copy the range you want to transpose. For example, select the range C2:C4 for Laptop.
➤ Go to the Home tab and click Paste.
➤ Choose Paste Special >> Transpose.
➤ The vertical range will now appear horizontally.

➤ Repeat the process for each group. Although it’s manual, this is a quick solution for small datasets or one-time tasks.

Power Query is the best solution for larger datasets or when you want to automate the transposing process.

Here’s how to apply Power Query:

➤ Select your dataset and go to Data >> Get & Transform >> From Table/Range.

➤ Make sure the My table has the headers box checked and click OK.

➤ In Power Query, go to Transform >> Pivot Column.

➤ Select Month as the column to pivot.
➤ In the Values Column, select Sales.
➤ Click OK.

➤ Your data will now be grouped by Product with separate columns for each month.
➤ Finally, click Close & Load >> Close & Load To… to load the data into your worksheet.

➤ Now, you’ll see your grouped rows neatly transposed into columns in the selected location.

If you work with large datasets or need to transpose grouped rows into columns frequently, using a VBA macro can save a lot of time. With a simple macro, Excel can automatically rearrange your data without manually copying and pasting each group.

Here’s how to do it:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Go to Insert >> Module and paste this code:

Sub TransposeGroupedRows()
    Dim src As Range, dest As Range
    Dim i As Long, r As Long
    ' Set the source data range
    Set src = Range("A1:C11")
    ' Set the destination cell
    Set dest = Range("E1")
    ' Create headers for the output table
    dest.Offset(0, 0).Value = "Product"
    dest.Offset(0, 1).Value = "Jan"
    dest.Offset(0, 2).Value = "Feb"
    dest.Offset(0, 3).Value = "Mar"
    r = 1
    ' Loop through each row in the source
    For i = 2 To src.Rows.Count
        If src.Cells(i, 1).Value <> src.Cells(i - 1, 1).Value Then
            r = r + 1
        End If
        dest.Cells(r + 1, 1).Value = src.Cells(i, 1).Value
        Select Case src.Cells(i, 2).Value
            Case "Jan": dest.Cells(r + 1, 2).Value = src.Cells(i, 3).Value
            Case "Feb": dest.Cells(r + 1, 3).Value = src.Cells(i, 3).Value
            Case "Mar": dest.Cells(r + 1, 4).Value = src.Cells(i, 3).Value
        End Select
    Next i
End Sub

➤ Close the VBA editor and press  Alt  +  F8  .
➤ Select TransposeGroupedRows, and click Run to run the macro.

➤ The grouped rows will be transposed into columns automatically.

Frequently Asked Questions

How to quickly transpose a group of rows to columns in Excel?

The fastest way is to use Excel’s built-in Transpose feature in Paste Special. Select the group of rows, copy them, choose the destination cell, and use Paste Special with the Transpose option. For dynamic updates, functions like INDEX and SEQUENCE or Power Query are better choices.

How to convert every 4 rows into columns in Excel?

You can use the INDEX formula combined with SEQUENCE or OFFSET to pull every 4 rows into a horizontal layout. Power Query is also ideal for automating this task, especially for larger datasets.

How to do bulk transpose in Excel?

For large datasets, the easiest method is Power Query. It allows you to pivot grouped rows into columns automatically. Alternatively, you can write a simple VBA macro to automate bulk transposing without manual effort.

Wrapping Up

Transposing multiple rows in groups into columns helps organize your data and improves how you view and analyze it. In this tutorial, each method serves a different purpose.

Choose the one that best fits your data size and how often you need to repeat the task. With these techniques, you can reshape messy row-based data into a clear, structured table in Excel.