We all look at data differently. Some people create Excel spreadsheets with the main fields horizontal. Others prefer the data vertically in columns. Sometimes these preferences lead to a scenario where you want to transpose Excel data. Microsoft must have anticipated this issue because they offer several methods to switch your columns and rows around.
The Layout Problem
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 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 the analysis. For example, I can’t easily filter for California vendors.
What is Excel’s Transpose Function
In simple terms, the transpose function changes the orientation of your columns (vertical range) and rows (horizontal range). My original data rows would become columns and my columns would become rows. It fixes my layout problem above without me having to retype my original data.
The transpose function is pretty versatile. It can be used in Excel formulas or with paste options. There are some differences based on which method you use.
Convert Columns & Rows Using Paste & Transpose
The steps outlined below were done using Microsoft Office 365, but recent Microsoft Excel versions will work.
- Open the spreadsheet you need to change. You may also download the example sheet at the end of this tutorial.
- Click the first cell of your data range such as A1.
- Shift-click the last cell of the range. Your data set should highlight.
- From the Home tab, select Copy or type Ctrl + c.
- Select the new cell where you would like to copy your transposed data.
- Right-click in that cell and select the Transpose icon under Paste.
info As you hover over the Paste options, you can see the data layout change.
You should now see your data with the columns and rows switched. You can resize your columns to suit your needs.
These two data sets are independent. You can delete cells from the top set and it will not impact the transposed set.
When using this method, your original formatting is maintained. For example, if I added a yellow background to my original cells B1:G1, the color would be applied. The same is true if I used red text on cell E:5.
Using the Transpose Function in a Formula
As I mentioned, Excel has multiple ways for you to switch columns to rows or vice versa. This second way utilizes a formula and array. The result is the same except your original data and the new transposed data are linked. You may lose some of your original formatting. For example, colored text and styles came across, but not cell fill colors.
- Open your Excel sheet.
- Click an empty cell where you want your converted data. I’m using A7.
- Type =transpose.
- Finish the formula by adding a ( and highlighting the cell range we wish to swap.
- Type ) to close the range.
- Press Enter.
info Notice how Excel provides a tooltip showing for Transpose – “Converts a vertical range of cells to a horizontal range and vice versa“.
Note: If you’re not using Microsoft 365, you’ll probably need to press CTRL + SHIFT + Enter
Differences in Transpose
While these two methods produce similar results, there are differences. In the first paste method, whatever action I take on a cell is independent of the transposed version. I could delete the original values and nothing would happen to the columns I swapped.
In contrast, the Transpose formula version is tethered to the original data. If I change the value in B2 from 1200 to 1500, the new value will automatically update in B8. However, the reverse isn’t true. If I change any transposed cells, the original set will not change. Instead, I will get a #SPILL error and my transposed data will disappear.
Transpose Formula & Blank Cells
Another difference with using the Transpose formula is it will convert blank cells to “0”s.
The fix for this quirk is to use an IF statement to the formula that keeps the blanks cells as blanks.
=TRANSPOSE(IF(A1:G5="","",A1:G5))
Alternatively, you could do a search and replace on the zeros.
Tutorial Resources
attach_file Example Practice Sheet to Swap Columns & Rows
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-01-14