How do you transpose data?
I need the information from Column 1  rows 210 to be Flipped or transposed into Row 1  Columns 210. In the sheet below 1 would stay in the same spot. Cell with "2" would need to move to Row 1, Column2. What function can I use to complete this?
Best Answer

Hi @Dakota
You could use an INDEX Function to bring data into your top row. For example for Row 2's information in Column 2, you could use:
=INDEX([Column1]:[Column1], 2)
The number 2 at the end of the Index function identifies what row to bring back. This means for column 3, you could adjust it to be:
=INDEX([Column1]:[Column1], 3)
And so on.
If you don't want to have to update the formula, you could use a helper row at the top of the sheet to list out the row numbers you want to bring back. Then reference the cell in the top of the current column instead of typing the number. For example:
=INDEX([Column1]:[Column1], [Column2]$1)
Then if you drag this formula over into Column3, the column name will update so it's now looking in the top row for Column3:
=INDEX([Column1]:[Column1], [Column3]$1)
Cheers,
Genevieve
Answers

Hi @Dakota
You could use an INDEX Function to bring data into your top row. For example for Row 2's information in Column 2, you could use:
=INDEX([Column1]:[Column1], 2)
The number 2 at the end of the Index function identifies what row to bring back. This means for column 3, you could adjust it to be:
=INDEX([Column1]:[Column1], 3)
And so on.
If you don't want to have to update the formula, you could use a helper row at the top of the sheet to list out the row numbers you want to bring back. Then reference the cell in the top of the current column instead of typing the number. For example:
=INDEX([Column1]:[Column1], [Column2]$1)
Then if you drag this formula over into Column3, the column name will update so it's now looking in the top row for Column3:
=INDEX([Column1]:[Column1], [Column3]$1)
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!