Why is the transpose check box in the Paste/Special dialog greyed out?
To do a transpose, select the area to be transposed and execute a Copy to get it onto the clipboard. A Cut will not work.
Select the Target area, and do a Paste/Special. Click the Values radio button; this will ungrey the Transpose checkbox. Check the Transpose checkbox, and click the OK button. The data will be transposed, and pasted to the newly-selected area.
In order for the Transpose to be successful, all of the following conditions must occur:
The area selected for the target paste must be the right shape and size. For example, if the copied source data is six columns wide and three rows high, you have to select a target area that is three columns wide and six rows high.
The target area must be empty; you can't transpose in place.
Doug Mitkiff has an excellent tutorial for this, along with an Excel file you can download and practice on.
Also..You cannot paste special between separate sessions in excel. For example, when I use dual monitors it is often times convenient to have two separate sessions of excel open (one on each monitor). In order to do a paste special you must be within the same session. I will typically paste the entire sheet I'm working from into a new tab and paste special from there.
Hope this helps...
If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first, or you can use the TRANSPOSE function to rotate the rows and columns.
Source: a Microsoft Excel help page