Tech Dreams

Microsoft Excel Tip – How To Convert Columns To Rows And Vice Versa

Today while editing a spread sheet I had to transform data of 100 columns to rows. Data in my source spread is like this

Converting Columns To Rows In Microsoft Excel -  1

and I have to transform it like this

Converting Columns To Rows In Microsoft Excel -  2

First I tried copying one by column from the source spreadsheet to target spreadsheet. After copying few cells, I realized that it is a very tedious job. Going through to help of Microsoft pointed to a simple tip that automatically converts columns to rows and vice versa.

Here are the steps to be followed for converting columns to rows in excel

1) From the source spreadsheet, select the list of columns that you want to transform as rows and copy them using Ctrl + C or copy option

2) Open your target spreadsheet and choose the menu option Edit -> Paste Special.; It pops up Paste Special window.

3) Check the option Transpose and click on OK button

Converting Columns To Rows In Microsoft Excel -  Paste Special Option

Microsoft Excel automatically pastes column data into rows in the target spreadsheet. You can follow the same tip for converting columns to rows also.

  • disqus_GuiucSuiuP

    Thanks a lot. Works like a charm

  • Eddie K

    Best tip ever! Exactly what I was looking for. Thanks!