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.
- Highlight the table, Right Click > Copy
- Select a cell where you want the Transposed Table
- 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.
- 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.
- Now click inside function bar (ƒx) Type the following: =transpose(
- Highlight the table you wish to transpose: =transpose(A1:D5
- Close the function: transpose(A1:D5)
- 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.