How to Transpose Data from Rows to Columns in MS Excel – Guide
We all have different perspectives on data. Some individuals, for example, design Excel spreadsheets with the main fields arranged horizontally. Others like the data in columns inverted vertically. These settings can sometimes lead to a situation where you need to transpose Excel data. Microsoft must have anticipated this problem because they provide many techniques in Excel for converting rows and columns.
The layout problem
I recently received a large Microsoft Excel spreadsheet that contained vendor evaluation information. The information was helpful, but I couldn’t use tools like Auto Filter because of the way the data was organized. I would also have problems if I needed to import the information into a database. Instead, I want the company names to be displayed vertically in Column A and the Data Attributes to be displayed horizontally in Row 1. This would make parsing easier. For example, I can’t easily filter out California providers.
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. So this fixes my layout problem above without me having to retype my original data. The transpose function is quite 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 and rows using paste and transpose
The steps outlined below were done using Microsoft Office 365, but recent versions of Microsoft Excel will work. You should now see your data with the columns and rows swapped. You can resize your columns to suit your needs. These two data sets are independent. You can exclude cells from the top set and it will not affect 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 happens if I use red text in cell E:5.
Use the transpose function in a formula
As I mentioned, Excel has several ways to switch columns to rows or vice versa. This second way uses a formula and an 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 the original formatting. For example, colored text and styles appeared, but not cell fill colors.
Differences in Transposition
While these two methods produce similar results, there are differences. In the first pasting method, any 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 version of the Transpose formula is linked 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 is not true. If I change any transposed cell, the original set will not change. Instead I will get a #SPILL error and my transposed data will disappear.
Transposition formula and blank cells
Another difference with using the Transpose formula is that it will convert blank cells to “0”s. The fix for this quirk is to use an IF statement for the formula that keeps the blank cells as blank. =TRANSPOSE(IF(A1:G5=””,””,A1:G5)) Alternatively, you can do a search and replace the zeros. Now that we show 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 quickly.
Final note
I hope you like the guide How to Transpose Data from Rows to Columns in MS Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.
