Transposing a table or matrix is a method of rotating or pivoting a table so the rows become the columns and the columns become the rows. Excel offers a couple of methods of performing this action without having to manually recreate the table.

Click here to download the Exercise File: Transpose

Method 1: Paste Special

This method is very simple.

1. Highlight the table, Right Click > Copy
2. Select a cell where you want the Transposed Table
3. Right Click > Paste Special > Select the Transpose Clipboard

Closer look at the Transpose Clipboard Icon

Method 2: Transpose()

While the Paste Special method works well, it does require user interaction. If you want to automate the process, you can use the Transpose() function.

1. Highlight some blank cells. Make sure you highlight enough to fit you data. In this case, we need to highlight 4 down and 5 across.
2. Now click inside function bar (ƒx)  Type the following: =transpose(
3. Highlight the table you wish to transpose: =transpose(A1:D5
4. Close the function: transpose(A1:D5)
5. Now hold down Shift+Ctrl and press Enter

Argument for Method 2

While the Paste Special method is easy to use, here is a major draw back to it. Notice the red circled cells. After creating Transposed tables using the Transpose() method and the Paste Special method, I changed the Sensor 1 Qrt 3 value from 23 to 24. Notice the Transpose() table updates with the new information, while the Paste Special table does not.