Assuming you have a matrix/table like the one shown below and you want to interchange its rows and columns, here are two ways how you can achieve it.
Using Paste Special:
Copy the range of cells you want to transpose (in our case A1 to F4) and then right click on the cell where you want to have the transposed matrix (in our case A7). Select ‘Paste Special’ from the options.
Copy the range of cells you want to transpose (in our case A1 to F4) and then right click on the cell where you want to have the transposed matrix (in our case A7). Select ‘Paste Special’ from the options.
Tick the ‘transpose’ option in the Paste Special menu and click on OK. 
Using TRANSPOSE Operation: Select the range of cells where you want your transposed matrix (in our case A15 to D19) and enter the array operation in the fx tab:
=transpose(A1:F4) and press CTRL+SHIFT+ENTER
Advantage of using Paste Special is that you can copy the format and the formulae of the copied cells along with the cell values, which is not possible using the array operation of TRANSPOSE. The advantage of the array operation is that when you modify the parent matrix, automatically the child matrix is also updated. So, you can use any of the two options based on your requirement.
Great explanation. Thanks
ReplyDeletesuper... thanks :)
ReplyDeleteThats cool, although the use is (from my point of view) a little limited. But i guess i will need it one day, so im happy i hopefully know how to do it. I just have one question: What happens with data that refers to transposed cell, lets say via a VLOOKUP function? (for those of who who dont know what i mean by that here is a link to an overview: http://www.excel-aid.com/excel-matrix-the-vlookup-and-hlookup-functions.html ). With "transpose" i guess it will get screwed up, but if i use special paste, will it recognize the change and the VLOOKUP function accordingly? (in theory, it could be changed to a HLOOKUP function, possibly maintaning the correct refernce). I would really be interested in a solutioen for this, as the only workaround would be to change the references manually, which would be a pian to do.
ReplyDelete