How-To Geek
Convert a Row to a Column in Excel the Easy Way
Sometimes we’ve entered data in a column in Excel, only to realize later that it would be better to have this data in a row, or vise-versa. Here’s a simple trick to convert any row or set of rows into a column, or vise-versa, in Excel.
Please Note: This is tested in Excel 2003, 2007, and 2010. Here we took screenshots from Excel 2010 x64, but it works the same on the other versions.
Convert a Row to a Column
Here’s our data in Excel:
We want to change these two columns into rows. Select all the cells you wish to convert, right-click, and select copy (or simply press Ctrl+C):
Now, right-click in the cell where you want to put the data in rows, and select “Paste Special…”
Check the box at the bottom that says “Transpose”, and then click OK.
Now your data that was in columns is in rows!
This works the exact same for converting rows into columns. Here’s some data in rows:
After copying and pasting special with Transpose selected, here’s the data in columns!
This is a great way to get your data organized just like you want in Excel.
Got Feedback? Join the discussion at discuss.howtogeek.com
Comments (56)
Matthew digs up tasty bytes about Windows, Virtualization, and the cloud, and serves them up for all to enjoy!
- Published 03/8/10
Works exactly the same way with Open Office Spreadsheet
Hey, that’s helpful. Thanks for the article.
This also works in OpenOffice (tested in 3.1.1 on Linux). The location of the ‘Transpose’ box is slightly different, but otherwise it works the same.
This works in Open Office Calc (Excel equiv.) as well. Good tip.
Glad to hear it works in OpenOffice Calc the same way!
It also works in Excel 2000, for those of us still in the Dark Ages.
With Vista is ther a way to rename a “Favourite” please?
When i right click on a bar nothing happens.
Regards
Mike Snowdon
Very Smart Shortcut
tnx for this useful tip
Paste Special is a very useful tool – for those who weren’t familiar with it before, try all the different functions available. Just want to paste a format? A comment? It is a great option, definitely. It has saved me a lot of time over the years when I worked with Excel every day.
I love this Blog, it always shows useful tips & tricks for our daily basis work
Greetings from Mexico!
Thanks a lot for sharing,keep it up…
on my excel chart the columns are very narrow – how do I make them wider please ?
Mike Snowdon
I searched this tip a long time. thank you
Anyone know how to do this dynamically across tabs? Example – as a user enters text in a row on one tab, I’d like that same text to be copied to a column in another tab. So when a user enters text in cell A1 on Sheet1, the text is automatically copied to cell A1 on Sheet2. When they enter text in cell B1 on Sheet1, the text is automatically copied to cell A2 on Sheet2, and so on. I have a formula that will do this, but when I try various ways to copy the formula down the column on Sheet2, it either doesn’t increment the cell references for Sheet1, or it doesn’t increment them properly.
Thanks in advance.
Extreamly good and highly helpfull for me. Many Thanks
Thanks dear. I was looking for this for so long…
This is really great. Thanks for the help.
Thanks a lot. It is useful.
Worked. Useful tip!!!!
It’s helpful! Thanks.
Its very helpful, Thanks
cool! thanks a lot!
Hi … very very tanx .. very helpful.. good .. job ,,
Thanks a lot ! It has been a huge help to me.
THANKS SO MUCH!
Love it when the solution is easy. Thanks so much.
Hi.. this is working.. but if i use more date to tranpose, i couldn’t.. is there a restriction on the number of the cells which can be copied?
i think probably 250 is the max. please confirm. Thank you:)
Hello! I thought this was going to be an easy fix. I ran into a roadblock. I’m sure it’s something simple though. When I hit paste special and check the transpose block and ok … what happens in my Excel file is that the first address turns into the columns as needed and repeats all the way down the entire page.
I need ALL the addresses converted not just the first one repeated all over the place!!
HELP!
Thanks so much for sharing this information. Saved me hours and hours of manual work.
Thank you SO very much for this info! I was trying to create a pivot table, but my data was in columns instead of rows. Once I figured this out, the whole project was SO much easier.
God bless the internet. This was the most helpful thing I learned all month. Thanks!
Thanks for the demo…:) it worked
Thank you very much. Very easy indeed and it worked
Thanks a lot, it has really good,
Thanks, really helpful.
Thanxs
it is very use ful nice article
It works…really helpful
Very Very Achive command..
fantastic! thank you!!
Hi it is very helpful for me, thanks for your sharing.
Thanks, very helpful!
thanks a lot
superrrrrrrrrrrrrrrrrrr
Superb, thanks a lot
thank u
thanku so much
you just saved me from so much embarrasment in front of a class of freshmen and my teacher! thanks!
It saved me tons of hours. Thanks
So glad I found this. Thank you!!!
It saved my time too, thank you soooooo much guys! :-)
Yay, thanks so much for posting! I knew this was somewhere in Excel but I always forget where it is. I had my doubts that a google search would turn up anything useful, and yet, here I am.
Thanks so much!!
hey its very useful thanks ….. :)
Very helpful! thanks.