How to Transpose Data in Excel: A Step-by-Step Guide
Transposing data means moving the row data to a column and a column data to a row. Transposing data is useful for data analysis. At times, we have to pull data from various files with different formats for analysis and preparing reports. In such circumstances, we may have to transpose some data from one file to the other.
In excel, we can transpose data in multiple ways. Let us discuss these methods one by one.
Static method using Paste special
In this method, the data is transposed using Paste Special Transpose feature in Excel. Here the transposed data is static. It does not change with the change in the source data. We can transpose the selected data using the Paste Special Transpose feature in excel.
Let us quickly understand with the help of a simple example.
We have a list of Companies with Revenues, Profits, and margins in a horizontal format. We want to transpose this data in a vertical format.
To transpose this data, select the data and Press Control + C to copy it. Then go to the cell where you want to paste this data and right click your mouse. You get a list of options. Select Paste Special.
Once you select Paste Special, You get the following screen
Select the option Values and Transpose.
Then Click OK.
You get the following table.
The data is pasted in a vertical table format.
You can now format the data and proceed with the analysis required.
The same operation can be done by using a keyboard shortcut.
First Copy the data by using Control & C
Then Press, Alt & E & S to activate Paste Special Dialogue Box.
Then Press V & E to transpose and paste the copied values.
There is an inbuilt Transpose function in Excel. This function can transpose data dynamically, meaning that whenever there is a change in the original table, the transposed data gets updated automatically.
To Transpose the data, select the range where you want to transpose the data.
In our example, we have 4 rows and 10 columns in our table.
To transpose the data, select the range with 4 columns and 10 rows. Then type the transpose function.
Then press enter. In office 365 version of Microsoft excel, you get the transposed data (as shown below). The formula is in cell B12. The data has spilled over to the other cells. There is no formula in those cells. In case you want to change the formula, it can only be done by going back to the same cell (B12).
In earlier versions of the excel, you get an error when you press enter, as the transpose function is an array function. Excel can’t hold multiple values in a single cell.
So, to get the transposed data you have to type, = Transpose(B5:K8)
Then Press Control & Shift & Enter.
When you press these keys you get the data in transposed format.
This is a dynamic table. When we make any change to the original data source. This table gets updated automatically.