We all look at information differently. Some people create Excel spreadsheets where the main fields go across in a row whereas others put the data vertically. This can lead to a scenario where you want to swap or switch Excel columns to rows without retyping the information. Microsoft must have anticipated this issue as they built-in a Transpose feature. (Includes example Excel spreadsheet.)
I recently was given a large Microsoft Excel spreadsheet that contained vendor evaluation information. The information was useful, but because of the way the data was organized, I couldn’t use tools like Auto Filter. I would also have issues if I needed to import the information into a database. A simple example of such a spreadsheet is shown below.
Instead, I want to have the Company names display vertically in Column A and the Data Attributes display horizontally in Row 1. This would make it easier for me to do comparisons and analysis. For example, I can’t easily filter the spreadsheet for vendors in CA.
How to Transpose Columns & Rows in Excel,
- Open the spreadsheet you need to change. You may also download the example sheet at the end of this tutorial.
- Insert a blank worksheet.
- Click the first cell of your data range such as A1.
- Shift-click the last cell of the range. Your selection should highlight.
- From the Home tab, select Copy.
- At the bottom of the page, click the tab for the blank worksheet such as Sheet2.
- In the blank worksheet, click cell A1.
- From the Home tab, select Paste. The Paste dialog should appear.
- Click the option for Transpose.
As you hover over the Paste options, you can see that option’s name.
Once you transpose the data, your company names will show in Column A. I can now sort or filter the data in many ways.
Note: For older versions of Excel, the Copy & Paste commands are on the Edit menu.
Why Did I Use Another Worksheet?
Some of you may be wondering why I opted to create a new spreadsheet. The reason is if I tried to paste my swapped columns and rows into cell A1, I got an error.
I’m using a different size because I swapped my rows and columns. Previously, I had 7 columns and 5 rows. After converting my rows to columns, I have 5 columns and 7 rows. If you have the same number of columns and rows, you’ll be fine.
By using a new worksheet, I avoided this error. Alternatively, I could’ve selected a blank cell down on the same sheet.
If you find yourself transposing your rows and columns on the same spreadsheet, you might be better off creating a pivot table. A pivot table will provide much more functionality.
Additional Resources
Example Practice Sheet to Swap Columns & Rows
Disclaimer: Images from Amazon Product Advertising API. This site may receive an affiliate commission on these products. Updated: 2020-01-21