Transpose the Matrix

One simple yet useful feature in Excel is to transpose the selected cell range in the sheet.

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.

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.