How-To Geek

How to Convert a Row to a Column in Excel the Easy Way


You’ve set up your worksheet with all your row and column headings, and you’ve entered all your data. Then, you discover that it would look better if the rows were the columns and the columns were the rows.

You don’t need to re-enter all that data! There is an easy way to convert your rows to columns and vice versa using the Transpose feature in Excel. We’ll show you how.

Select the cells containing the headings and data you want to transpose.


Click Copy or press Ctrl + C.


Click in a blank cell on the spreadsheet. This cell will be the top, left corner of the new table of data.


Click the down arrow on the Paste button and select Paste Special from the drop-down menu.


On the Paste Special dialog box, select the Transpose check box so there is a check mark in the box and click OK.


The rows become the columns and the columns become the rows. The original set of data still exists. You can select those cells and delete the headings and data, if desired.


Isn’t that a lot easier and faster than retyping all your data?

Lori Kaufman is a writer who likes to write geeky how-to articles to help make people's lives easier through the use of technology. She loves watching and reading mysteries and is an avid Doctor Who fan.

  • 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.

    Mike Snowdon


    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


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


  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


  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


  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.

More Articles You Might Like

Enter Your Email Here to Get Access for Free:

Go check your email!