How to transpose columns to rows in Excel

The choice of what values to appear on the column or the rows may be a challenge especially when we are dealing with sensitive data. You may choose the column header of a particular data set but find that the column header would have been better being used on the side of the rows.

This problem or issue may arise when you are almost done or you are done with your work of data recording in the excel sheet. This situation can cause distress to the one doing the work of recording the data. However, thanks to excel in-built functions and tools you can transpose the columns to rows.

What is to transpose?

To transpose is the act of rotation or turning the columns to be on the side where the rows have been and the rows to rotate where the columns originally were. With this function, you do not have to delete or start over again once you make the row to column mistake or error.

Method 1: Using copy-paste method

To do the transposition follow the steps below as they are discussed in depth.

1. Open an excel sheet from the computer and record the columns and rows of your choice and insert some values into them.

2. With the above data set, we need to transpose the columns to rows. First of all, highlight the entire data set and click on copy to have the entire data set copied. Place your cursor to the new location where you want your new data set to be located.

3. On the new location where your cursor is, click on the paste drop down from the home tab.

4. Choose the paste option and click on transpose to rotate the columns to rows.

5. The paste option does rewrite the first data set but now in a different line and a different format.

The transpose function has worked out successfully and the columns have been transposed to rows. This process is time-saving unlike manual deleting and starting over again once you realize you have done an error in the arrangement of the columns and rows.

Method 2: Using shortcut + Paste Special

1. Highlight the data you want to transpose

2. Press CTRL+C to copy the highlighted section

3. Right-click an empty cell where you wish to display the new results

4. Click on paste special

5. Tick the transpose box and click okay 

6. The Columns will be transposed to rows automatically

Method 3: Using the Transpose Formula

The transpose formula is the best method to use if you have dynamic data. For example when the data from original section changes, the transposed data will update automatically

1. Use the cursor to highlight empty cells exactly equal to the size of your data

2. Type =TRANSPOSE( on the formula bar and highlight the range of data you need to transpose

3. Your data will be transposed automatically.