How do you transpose data?
I need the information from Column 1 - rows 2-10 to be Flipped or transposed into Row 1 - Columns 2-10. 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?
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
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Hi @Genevieve P. I tried doing exactly your formula, but it is not working.
=INDEX([Column1]:[Column1],
[Column2]$1)
I am simply trying to transfer a vertical row of cells to horizontal, I am sure there must be an easy solution to this? I was looking to copy and paste horizontally, but could not find a solution.
Here is a screen shot of my example. After writing this formula in "Column2" I tried to drag the formula horizontally. All cells are UNPARSEABLE.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.4K Get Help
- 447 Global Discussions
- 144 Industry Talk
- 480 Announcements
- 5.1K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 72 Community Job Board
- 491 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 304 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!