You can use the following syntax to transpose every nth row in Excel:

=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

This particular formula transposes every 5th row in column A in a spreadsheet.

Note: To transpose a different multiple of rows, simply change each 5 in the formula to a different number.

The following example shows how to use this formula in practice.

Example: Transpose Every N Rows in Excel

Suppose we have the following column of 15 basketball team names in Excel:

Now suppose that we would like to transpose the rows into columns based on every 5th row.

We can type the following formula into cell C2 to do so:

=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

The following screenshot shows how to use this formula in practice:

Next, click and drag this formula to the right until 5 total team names are shown:

Lastly, click and drag the formula down until every team name is shown:

We have now transposed every 5th row from the original column of team names into an array with 5 columns and 3 rows.

For example:

• The first five team names in column A are shown in the first row.
• The second five team names in column A are shown in the second row.
• The third five team names in column A are shown in the third row.

To transpose a different multiple of rows, simply change each 5 in the formula to a different number.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Select Every Other Column in Excel
How to Sum Every Nth Row in Excel
How to Select Every Nth Row in Excel