Table of Contents
Transposing data in Google Sheets refers to rearranging rows and columns to switch their positions. This can be done manually by copying and pasting, but it can be time-consuming for large datasets. To transpose every N rows in Google Sheets, you can use the TRANSPOSE function in combination with other functions such as INDEX and ROW. This allows you to specify the number of rows to transpose and automatically rearrange the data accordingly. By using this method, you can quickly and efficiently transpose large amounts of data in Google Sheets.
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:
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: