12

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?

19

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.

2
  • You also have to be editing an .xlsx file, not a .csv. – Noumenon Oct 13 '17 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 '20 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...

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 '17 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

0

I have the same problem on may Macbook and MS Office for Mac.

Created a new file, type a,v,x on columns C,D,E and grab them (3 cells), then the right clicked on another cell. But the transpose is grayed out.

I thought this bug is just for MS office Mac version.

The MacOS and MS Office are latest version as of 6/8/2020.

enter image description here

1
  • Yes I have the same issue with mac catalina and excel 16.42 ! – Anver Sadhat Dec 8 '20 at 14:17

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.