How to Transpose Excel Columns to Rows

Everyone looks at data differently. Some people create Excel spreadsheets where the main fields are horizontal. Others put the data vertically in columns. Sometimes these preferences lead to a scenario where you want to transpose Excel data. Transpose is an Excel function or predefined formula that converts Excel columns to rows without retyping the data. Microsoft must have anticipated this issue because they offer several methods to switch your data. (Includes example Excel spreadsheet.)

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.

Convert Columns & Rows Using Paste and Transpose

The steps outlined below were done using Microsoft Office 365, but recent editions will work.

1. Open the spreadsheet you need to change. You may also download the example sheet at the end of this tutorial.
2. Click the first cell of your data range such as A1.
3. Shift-click the last cell of the range. Your selection should highlight.
4. From the Home tab, select Copy or type Ctrl + c.



5. Select the new cell where you would like to copy your transposed data.
6. 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.

Using Transpose 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 end result is the same except your original data and the new transposed data are linked.

1. Open your Excel sheet.
2. Click an empty cell where you want your converted data. I’m using A7.
3. Type =transpose.



info Notice how Excel provides a tooltip showing for Transpose – “Converts a vertical range of cells to a horizontal range and vice versa“.

4. Finish the formula by adding a ( and highlighting the cell range we wish to swap.
5. Type ) to close the range.



6. Press Enter.

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 and 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. This site may receive an affiliate commission on these products. Updated: 2020-08-03