Convert A Row To A Column In Excel The Easy Way

Have you ever felt the need to convert the rows into columns in a spreadsheet? If yes, then can you recollect what you had done to achieve this transformation?

Well, as far as I know, you must have first tried the copy paste approach wherein you select rows, copy it, select equivalent number of empty columns or a blank cell, and paste it. However, Excel does not support this method of copy paste.

Now, the only option left for you is to retype the row values in columns. This is fine if you have to retype the values of five cells in a row. However, in case of 100 cells, this manual approach is quite time consuming and tedious. In fact, it is neither a practical solution nor a recommended approach because of the risk of typographical errors as well. Fortunately, Excel offers an easy way to convert rows into columns via one of its Paste Special options. Let us explore this option now!

Problem: Need To Improve The Readability Of Data In Excel

Let us assume a scenario wherein you are working in a company as an HR Assistant. Due to this position, you daily have to work with MS Excel and MS Word for tracking different things of employees, right from recruitment to attendance. One fine day, your Head comes to you and asks you to change the layout of some seven spreadsheets of a workbook for ensuring quick readability.

At present, these rows in these spreadsheets show the months as well as the number of days an employee was present in each month. Further, each sheet has the data of five employees as per the departments, which means one has to scroll down to look for the data of all employees in a sheet. However, this data is only of six months. Therefore, for better visibility and readability, you have been asked to change the layout of data.

Solution: Convert Rows Into Columns

Keeping in mind the above scenario wherein each sheet has attendance of five employees for six months, it would be a good idea to convert the rows into columns. This will eliminate the need of scrolling up and down. Further, anybody will be able to see the data clearly just in one view.

Implement The Solution: Use The Transpose Option Of Paste Special

Irrespective of whether you are using Excel 2003 or 2007, the Transpose option in the Paste Special dialog box allows you to transform rows into columns and vice-versa. So, it is clear that you still have to use the copy paste approach, but this time, it is with a little twist!

Steps To Convert A Row To A Column In Excel

Here are the Steps of Converting Rows into Columns in Excel 2007.

1. Open the spreadsheet whose rows need to be transformed into columns.

2. Select the cells of the adjacent rows, which you want to convert. Right-click the selection. A shortcut menu is displayed. Select the Copy option. Alternatively, you can also press Ctrl + C.

3. Select a blank cell where you want to insert the data. In the Home tab, under the Clipboard group, select Paste –> Paste Special. The Paste Special dialog box is displayed.

4. Select the Transpose check box at the bottom.

Also Read

Make Excel 2007 Read Spreadsheets To You
How To Change The Gridlines Color In Excel 2007
How To Change The Default Font In Excel 2007

5. Click OK. Voila! Your rows have now become columns in just a few seconds!

6. Save the spreadsheet. However, do note that the rows will still remain in your sheet. In case you do not want them, you will have to delete them manually.