Transpose Excel Data in Groups of Five into Rows
Question details
Transpose consecutive groups of five vertical cells into horizontal rows, advancing the source range automatically.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The source data is in a vertical column, and each group of five values should appear across a separate row on another sheet.
- Observed behavior
- Each destination row should display the next five source cells without manually changing the source references.
Identify the source sheet, column, and first source cell, as well as the first destination cell. The example below assumes source data starts at A1 on a sheet named Source and the destination starts at A20 on another sheet.
Use INDEX to transpose each group of five cells
Use this formula when each destination row needs to pull the next five values from a source column.
The formula uses the destination row to advance by five source cells and the destination column to select each value within the group.
Open the destination sheet and click cell A20. Make sure the destination range does not overlap the source data if both are on the same sheet.
In A20, enter =INDEX('Source'!$A:$A,5*(ROW(A20)-ROW($A$20))+COLUMN(A20)-COLUMN($A$20)+1). Replace Source with the name of your source sheet.
Drag the fill handle from A20 across to E20. These cells will return Source!A1 through Source!A5.
Select A20:E20 and drag the fill handle down. The next row will return Source!A6 through Source!A10, and each subsequent row will advance by five cells.

Transpose grouped data in WPS Spreadsheet
Use the same INDEX formula in WPS Spreadsheet to turn a vertical list into rows of five while automatically advancing through the source column.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and check that the source data starts in A1 on a sheet named Source.
- 2. Enter the formula: On the destination sheet, click A20 and enter =INDEX('Source'!$A:$A,5*(ROW(A20)-ROW($A$20))+COLUMN(A20)-COLUMN($A$20)+1). Change Source to your source sheet name if needed.
- 3. Fill across and down: Drag the fill handle from A20 to E20, then select A20:E20 and drag down to create additional groups.
- 4. Save the workbook: Choose File > Save As and select an Excel format such as XLSX if you want to share the workbook with Excel users.

Frequently Asked Questions
Can the source data be on another sheet?
Yes. Include the sheet name before the column reference, as in 'Source'!$A:$A. Replace Source with the actual sheet name.
How do I start with a source cell other than A1?
When using the full-column reference $A:$A, change the final +1 in the formula to the starting row number. For example, use +2 to start at A2.
Can I transpose groups of a different size?
Yes. Replace the 5 in the formula with the number of cells per group, then fill the formula across that many destination columns before filling down.
Why use INDEX instead of TRANSPOSE?
INDEX makes it straightforward to advance to the next group as you fill the formula down. A TRANSPOSE formula can also rearrange data, but automatically advancing each copied row to a new group requires additional setup.




