Transpose Using the Right-Click
The Transpose tool in Excel helps to eliminate the need to re-key data. Imagine you just spent the last couple of hours working on a worksheet that displays financial information. The worksheet shows your company's quarterly revenue results for the last year. You happen to be the owner of a small, but successful, sporting goods store. You have five departments: apparel, fishing, hunting, camping and sports.
In your worksheet, you set up the departments across the top as the column titles and each quarter is displayed by row (quarter 1, quarter 2 and so on). You decide that the information would work better if the column and the rows were switched. In other words, you want the columns to be the rows and the rows to be the columns. No problem! Ready, set, switch!
Let me start with a note. The Transpose feature uses the copy/paste commands. Copy areas and paste areas cannot overlap. You can transpose the data to the same worksheet or another worksheet in the workbook, but the paste area must fall outside the area from which you copied the data. So here are the steps:
- Highlight or select the worksheet data you want to switch (you can select any cell of data and press Control + A on your keyboard).
- Right-click on the selected area and choose Copy from the drop-down menu.
- Click your mouse in the first cell of the area you want to paste your rearranged data (either on the same worksheet or a new worksheet in the workbook).
- Right-click and choose Paste Special from the drop-down menu.
- Add a check to the box next to Transpose.
- Click OK.
Check it out!
Worksheet after rows and columns have been transposed
 |
The rows are now the columns, and the columns are now the rows - much easier than re-typing all the information. At this point, the original set of data you copied can be deleted.
Transpose Using the Ribbon Commands
If the right-click method is not for you, the Transpose feature can be accessed using the ribbon commands as well. Let's go back to our example worksheet and use the ribbon commands to switch it back.
- Select the worksheet data you want to switch.
- Look in the Clipboard grouping of commands.
- Click on the copy command.
- Select the first cell of the area where you want to paste your rearranged data.
- In the clipboard grouping of commands, click on Paste.
- Choose Paste Special.
- Add a check to the box next to Transpose.
- Click OK.
And, just like that, we switched the columns and rows back to their original places. I prefer the right-click method, but it's really user preference.
Lesson Summary
This lesson discussed the Transpose feature in Excel. The feature is quite basic, but powerful. It uses the copy/paste commands to switch columns and rows of data. Simply copy the data you want to switch, and transpose or paste the rearranged data using the Paste Special command.
You learned in this lesson there are two ways to access the Transpose feature. You can use the right-click method or the Clipboard grouping of commands in the ribbon.
Learning Outcomes
Following this video lesson, you will be able to:
- Identify the purpose of the Transpose feature in Excel
- Explain how to transpose rows and columns using both the right-click method and the Clipboard commands
