Table of Contents
Transposing every N rows in Excel means rearranging the data in a spreadsheet so that the values in each row are moved to a new column and the columns are shifted to become rows. This allows for a different view of the data and makes it easier to compare and analyze information. To transpose every N rows, the user would select the desired number of rows and columns, then use the “Transpose” function to reorganize the data. This can be useful for organizing data sets, creating charts and graphs, or simply to present data in a more visually appealing way.
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:
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: