We all look at data differently. For example, some people create Excel spreadsheets with the main fields horizontal. Others prefer the data flipped 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 convert rows and columns in Excel.
Article Contents
The Layout Problem
I was recently given a large Microsoft Excel spreadsheet that contained vendor evaluation information. The information was useful, but I couldn’t use tools like Auto Filter because of the way the data was organized. 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 your columns’ orientation (vertical range) and rows (horizontal range). My original data rows would become columns, and my columns would become rows. So this fixes my layout problem above without me having to retype my original data.
The transpose function is pretty versatile. For example, you can use the feature in Excel formulas or with paste options. However, 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.
✪ 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 same background color would be applied. The same is true if I used red text on cell E:5.
Use 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. As a result, 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.
✪ Notice how Excel provides a tooltip showing for Transpose – “Converts a vertical range of cells to a horizontal range and vice versa“.
- Finish the formula by adding a ( and highlighting the cell range we wish to swap.
- Type ) to close the range.
- Press Enter.
✪ 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. So, for example, 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.
Now that we’ve shown you how to transpose data in Excel, try playing around with the practice worksheet below. You’ll be swapping your rows and columns in Excel in no time.
