When typing information into Excel, sometimes users type information and press the “Tab” key. This adds the information typed into one column at a time. See the image as an example.
I cannot tell you how many times I have seen users decide that the information needs to be placed into the rows instead of the columns, only to see them delete the data and retype it. This can be very time consuming.
But, there is an easier way to convert columns to rows or rows to columns.
Using the “Transpose” option allows users to convert data in two easy steps.
Let’s use the following example.
A Human Resources Manager wants to track employee attendance records for meetings. The HR Manager setup the spreadsheet as follows. See image 1 above.
Now that she’s typed the data with the employee names in Row 1, the employee records of meetings are typed into each respective column. However, now she’s realized that it would be easier to read the spreadsheet if the names were all located in Column A with the meetings in the rows associated with each employee.
This spreadsheet is rather small, but imagine if the HR Manager had 500 employees or more; it would be very time consuming to retype the data into the format she now wishes to use.
Here’s how the HR Manager can convert the columns to rows in a few short steps.
Highlight the data using the mouse.
Right-click the highlighted area and select the “Copy” menu item.
Place the cursor in a blank cell where you want the data. We’ll use Cell A10 for our example.
Right-click in Cell A10 and select the “Paste Special” option from the menu.
The “Paste Special” window opens providing many options.
Select the “Transpose” checkbox in the lower right corner of the window. See image 2 above.
Click the “OK” button to accept the paste and return to the spreadsheet.
Notice the data has now been converted from columns to rows.
Using this example, you’ll see that you now have duplicate data. The old formatted data will need to be deleted. This can cause extra steps; but some users I’ve trained over the years like to see the data and compare it prior to deleting it.
However, if you prefer, you may paste the data into a new blank spreadsheet tab. After the data has been transposed, or converted from columns to rows, the old spreadsheet tab may simply be deleted. This will be faster when dealing with larger amounts of data.
To do this, simply place the cursor in Cell A1 of a blank spreadsheet tab when using the “Paste Special” steps listed above. Once the data has been placed into the new spreadsheet tab, simply right-click on the old spreadsheet tab and select the “Delete” menu item and confirm the deletion by clicking on the “OK” button.
This example provided steps for converting columns to rows. To convert rows to columns simply follow the same steps.