In Excel, you are supposed to be able to change the rows to columns and the columns to rows (in effect, rotating the sheet through a diagonal axis), by selecting the area to be transposed, and doing a Copy, Paste Special. But the dialog that opens has the Transpose checkbox greyed out.
I've tried several methods, such as selecting a square area, selecting the upper left-hand cell before the paste, selecting the lower right hand cell before the paste, but they all have the same result.
How do I ungrey the box so I can check it?
Best Answer
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.