How to Transpose Columns Into Rows in Excel 2013

Excel 2013 is used to analyze large sets of data that are traditionally organized into tables; this allows for efficient application of statistics, the creation of complex analytics and the transformation of the data into graphs; usually tables are organized with the headings in the columns and the data below the headings in the rows.

Often after building a complex data table it becomes evident that it would be more efficient to place headings in the left most column and have the data follow in the subsequent columns. Microsoft Excel 2013 provides an easy way to transpose the data using the simple functionality of the copy and paste feature of the worksheet.

Step 1 – Select the data that you want to transpose.

The example below shows a simple table with the headings in the top row and the data below the headings in the following rows. To begin the process to transpose the table, select the table:

• Left click on the top left cell of the table (cell C6 with the heading ‘Heading 1’).
• Hold down the left mouse button and drag the cursor to the bottom right cell within the table (cell G16 with the value ‘65’).

Step 2 - Transpose the table.

Now that you have selected the table you are able to transpose it using the copy and paste functionality of Excel 2013. First copy the original table – this can be achieved in three ways:

• Using the copy icon on the home menu.

• Pressing Ctrl & C together – this will make a copy of the selected range or table.
• Right clicking on the range and selecingt copy from the menu.

Now that there is a copy of the table in the clipboard, the target cell can be selected and the original table can be transposed so that the headings are now in rows. Excel 2013 uses the functionality of copy and paste to allow easy transposition:

• Select the cell where you want the transposed table to appear (in the example cell I6 was selected).
• Using the Paste icon on the Home menu, select the Paste Special option. (The last icon under the ‘Paste’ sub-menu can also be used, however for this example the formatting is not required and therefore the Paste Special option is selected).

• A dialog box will open – select the checkbox next to Transpose and press OK. Selecting ‘Values’ will ensure that the formatting isn’t pasted as well.

The diagram below shows the original table and the transposed table.

Conclusion

The process to transpose data is very easy and versatile. You can transpose large tables very quickly and also have the ability to use the ‘Undo’ feature to revert back to the original table as required. Excel 2013 has made the process far more intuitive and giving extra functionality that allows you to manipulate data tables in many ways.

This process can also be used to transpose rows into columns in Excel 2013.