How to Transpose Every N Rows in Google Sheets


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

=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 both 5‘s in the formula to a different number.

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

Example: Transpose Every N Rows in Google Sheets

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

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:

Google Sheets transpose every nth row

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 both 5‘s in the formula to a different number.

Additional Resources

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

How to Select Every Nth Row in Google Sheets
How to Sum Every Nth Row in Google Sheets
How to Find First Occurrence of Value in Column in Google Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *