14

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?

Improve this question

4 Answers 4

Reset to default
22

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:

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

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

Improve this answer
2
  • You also have to be editing an .xlsx file, not a .csv.
    – Noumenon
    Oct 13, 2017 at 18:38
  • If this answer combined the other two answers (no table ranges, single session), this would be complete. More complete than Microsoft's own documentation.
    – Mayyit
    Jan 8, 2020 at 22:35
4

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

Improve this answer
1
  • Thanks for this, I was just tearing hair out wondering why transpose worked in same sheet couple of minutes ago, suddenly greyed out -- hard reset the tab and transpose came back. Google docs are so buggy.
    – Williams
    Sep 4, 2017 at 1:03
2

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

Improve this answer
0

First select rows data to paste-> select column range->Paste Special -> Transpose option will be visible.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.