Table of Contents
To transpose every N rows in Google Sheets, first select the N rows that you want to transpose. Then, copy the selected rows by pressing “Ctrl+C” on a PC or “Command+C” on a Mac. Next, right-click on the cell where you want the transposed data to start and select “Paste Special”. In the pop-up window, check the box next to “Transpose” and click “Paste”. This will transpose the selected rows into columns. Repeat this process for every N rows that you want to transpose. Alternatively, you can use the TRANSPOSE function to automatically transpose the data in a formula.
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:
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: