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