• ARTICLES
SEARCH

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:

image

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):

image

Now, right-click in the cell where you want to put the data in rows, and select “Paste Special…”

 image

Check the box at the bottom that says “Transpose”, and then click OK.

image

Now your data that was in columns is in rows!

image

This works the exact same for converting rows into columns.  Here’s some data in rows:

 image

After copying and pasting special with Transpose selected, here’s the data in columns!

image

This is a great way to get your data organized just like you want in Excel.

Matthew digs up tasty bytes about Windows, Virtualization, and the cloud, and serves them up for all to enjoy!

  • Published 03/8/10

Comments (56)

  1. James

    Works exactly the same way with Open Office Spreadsheet

  2. SquareWheel

    Hey, that’s helpful. Thanks for the article.

  3. Duane

    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.

  4. ja

    This works in Open Office Calc (Excel equiv.) as well. Good tip.

  5. Matthew Guay

    Glad to hear it works in OpenOffice Calc the same way!

  6. Mike

    It also works in Excel 2000, for those of us still in the Dark Ages.

  7. Mike

    With Vista is ther a way to rename a “Favourite” please?

    When i right click on a bar nothing happens.

    Regards
    Mike Snowdon

  8. A J WADHWA

    Very Smart Shortcut

  9. Prateek

    tnx for this useful tip

  10. John

    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.

  11. Gamaware

    I love this Blog, it always shows useful tips & tricks for our daily basis work

    Greetings from Mexico!

  12. Rasheed

    Thanks a lot for sharing,keep it up…

  13. Mike

    on my excel chart the columns are very narrow – how do I make them wider please ?

    Mike Snowdon

  14. Nicholas Norton

    I searched this tip a long time. thank you

  15. Dean

    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.

  16. Han

    Extreamly good and highly helpfull for me. Many Thanks

  17. Gagan

    Thanks dear. I was looking for this for so long…

  18. ABe

    This is really great. Thanks for the help.

  19. Pooja

    Thanks a lot. It is useful.

  20. Mark

    Worked. Useful tip!!!!

  21. YK

    It’s helpful! Thanks.

  22. Shri

    Its very helpful, Thanks

  23. ypormm

    cool! thanks a lot!

  24. Sujad ullah

    Hi … very very tanx .. very helpful.. good .. job ,,

  25. Eklavya

    Thanks a lot ! It has been a huge help to me.

  26. BK

    THANKS SO MUCH!

  27. Mouse

    Love it when the solution is easy. Thanks so much.

  28. bharath

    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?

  29. bharath

    i think probably 250 is the max. please confirm. Thank you:)

  30. Angela Johnson

    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!

  31. Haseeb

    Thanks so much for sharing this information. Saved me hours and hours of manual work.

  32. Daniel

    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.

  33. Bob

    God bless the internet. This was the most helpful thing I learned all month. Thanks!

  34. Sunil

    Thanks for the demo…:) it worked

  35. Lethabo

    Thank you very much. Very easy indeed and it worked

  36. Michael

    Thanks a lot, it has really good,

  37. Julio

    Thanks, really helpful.

  38. Nanda

    Thanxs

  39. jjjjj

    it is very use ful nice article

  40. annu

    It works…really helpful

  41. Rahamath

    Very Very Achive command..

  42. arko

    fantastic! thank you!!

  43. Aung Lwin

    Hi it is very helpful for me, thanks for your sharing.

  44. e

    Thanks, very helpful!

  45. Nitesh

    thanks a lot

  46. sabari

    superrrrrrrrrrrrrrrrrrr

  47. Nitesh

    Superb, thanks a lot

  48. MMMM

    thank u

  49. soujanya

    thanku so much

  50. emily

    you just saved me from so much embarrasment in front of a class of freshmen and my teacher! thanks!

  51. Paul

    It saved me tons of hours. Thanks

  52. Kate

    So glad I found this. Thank you!!!

  53. Rose

    It saved my time too, thank you soooooo much guys! :-)

  54. Bud

    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!!

  55. Rohit

    hey its very useful thanks ….. :)

  56. Jay

    Very helpful! thanks.

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!