You can convert rows in column and column in rows in Excel pretty easy using past special. However it did not work for me. I was copying my tables from SQL Server and paste special would not give me any option to use any of the paste special option. In fact the dialog would be blank.
This occurred because I was copying from outside Excel. To use past special and transpose functionality, you will past the contents the regular way. Once it is in Excel, select the contents again and when you click paste special now, the dialog will show. Simply click transpose and it rows will be converted into column and columns into rows.