How to rotate excel data

This is a general question that just popped into my head that I have always wondered. There have been several times when I've needed to rotate data in Excel, for example:

Starting with:

   |  A  |  B  |  C  |
----------------------
 1 | ABC | DEF | GHI |
----------------------
 2 | omg | 开发者_Python百科lol | xyz |
----------------------
 3 |     |     |     |
----------------------

transform into:

   |  A  |  B  |  C  |
----------------------
 1 | ABC | omg |     |
----------------------
 2 | DEF | lol |     |
----------------------
 3 | GHI | xyz |     |
----------------------

I have never found a reasonable way to do this. Solutions I can think of right now are:

1. Write a macro (yuck)
2. Manually copy / paste (yuck)
3. Maybe some pivot table magic? (i doubt this would be possible)
4. Clever formulas using INDEX

Are there other solutions, maybe some hidden built-in feature?

1. Copy your grid
2. Edit ▸ Paste Special...
3. Check the "Transpose" checkbox
4. Click "OK"

Use of the TRANSPOSE() function

Transposing rows and columns?

See:

http://office.microsoft.com/en-us/excel-help/rotate-data-by-converting-columns-to-rows-or-vice-versa-HP005203138.aspx

Construct a pivot table from the data then you can transpose and do a lot more besides (filter, group etc)