Transpose Data with Excel

Video Link for Transposing Data with Excel

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.

transpose1

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

transpose2

Closer look at the Transpose Clipboard Icon

Transpose3

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.

Transpose4

  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.

Transpose7

 

 

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s