Convert Columns to Rows and Rows to Columns in Microsoft Excel
When you are using Microsoft excel most of the time it is necessary to convert a column in a sheet to a row and vice versa. Especially with the data in table format it is a difficult task to convert the columns of the table to rows along with the complete data. This article explains in step by step how to convert a column of an Excel 2010 sheet into row with pictures.
Assume you have data in a table in an excel sheet as shown in the picture beside. Now you want to convert the complete table from columns to rows format. Copying and pasting each cell is a time consuming and error prone process.
Sample Table for Conversion
Microsoft Excel offers a simple functionality called “Transpose” to do such a difficult task in a few clicks. Follow the below steps to understand the transpose functions.
- Select the complete table data and copy to clipboard (Ctl+C).
- Right click on the cell where you want to paste it.
- Click on the option “Paste Special” as shown in the picture beside.
- New popup window will open as shown in picture.
- Click on the checkbox “Transpose” and then click on “OK” button.
Transpose Option in Excel
Paste Special in Excel
Now all your table data is transposed from rows to column format. This can be used for a single row or column to big table of data spread into multiple columns and rows.
Converted – Rows to Column
When you right click on a cell, you also can click on the “Arrow” mark next to “Past Special” to see complete paste options including “Transpose” function as shown in the below picture.
All Paste Special Options
Whenever you paste content in a cell you shall notice a small button appearing as “Paste Options (Ctrl)”. Once you used “Transpose” function, it will appear automatically in the paste options as a frequently used option. Hence from next time onwards, just copy the table and paste it in a cell where you want and click on the “Paste Options (Ctrl) to see “Transpose” option.
Transpose Function as Default
