Ever wished you could effortlessly switch rows and columns in Excel? Now, imagine the convenience of tailoring your spreadsheet’s layout to perfectly suit your data analysis needs. In this tutorial, I’ll show how to convert columns to rows using the Transpose feature. (Includes practice file.)
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 original source table into a database. A simple example is shown below.
Instead, I want to have the Company names displayed vertically in Column A and the Data Attributes displayed 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 with the original table.
What is Excel’s Transpose Function
In simple terms, the transpose feature 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. And the system would adjust for column labels.
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 to Rows Using Paste Special
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 in your cell 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 from the Paste Options.
The data layout changes as you hover over the Paste options.
You should now see Excel switched the columns and rows. 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 a blank 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 Excel’s Transpose Options
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 the Transpose formula is that it will convert blank cells to “0”s.
The fix for this quirk is to use an IF statement in the formula that keeps the blank 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. And while you likely work with multiple rows, you can convert one Excel column to a row.
Show Me How Video
Click the image below for a short video showing how to switch columns and rows without a formula.
Key Points & Takeaways
- Excel Transpose function can switch the orientation of rows and columns without needing to retype the data.
- Use the Transpose feature in Excel formulas or with paste options. Be aware that there are differences based on the method used.
- When using the Transpose function in a formula, type “=transpose”, add a parenthesis, highlight the cell range to swap, then close the range with a parenthesis and press Enter.
- Remember the differences between the two Transpose options: the Paste Special method creates independent data sets, while the Transpose formula version is linked to the original data.