Learning how to transpose in Excel can be a super handy skill to learn. Transposing data takes an array and flips it horizontally or vertically, depending on the initial ouutput.
This can be very useful when we receive a spreadsheet that has a whole lot of horizontal rows of data, but we want the structure to fit more of a traditional database model of columns. Or if we are working with arrays of numbers, sometimes transposing a matrix can be super valuable.
In this simple guide, we are going to run through how to use the Excel TRANSPOSE function. We will cover:
- How to transpose data using the TRANSPOSE function
- How to transpose data with paste special
For the purposes of this tutorial we are going to run with a sample dataset that runs across the spreadsheet horizontally, but the two concepts we will run through work exactly the same way when trying to accomplish the opposite approach.
Our data set here is quite a simple one – we have a list of students and their test scores. As we can see, the data headers are to the left with each new row being a new dimension or metric, with each of the values running across the row:
Let’s start with the built in function for TRANSPOSE.
Using the Excel TRANSPOSE function
The first approach involves using a formula to use a built in function, simply called TRANSPOSE. The formula is as follows:
=TRANSPOSE(array)
That’s it! Simply select all of the cells and we are good to go. In our sample dataset the formula would end up being:
=TRANSPOSE(B2:G3)
You only need to enter the formula into the top left most cell of where you want your new data to sit, and from there Excel automatically fills in the rest.
The output then looks like the below:
One thing you will notice here, is that while we had some formatting applied to our initial dataset for the ‘headers’, this does not copy over with the transposed data, so depending on how important formatting is this does add a little bit more manual work to then copy this over with it.
As we can see, this formula is quite simple and does the job nicely. Let’s move on to our second, and arguably more useful approach – paste special.
How to transpose with paste special
Next up we have what is essentially just a copy and paste. When copying data from one spot of excel to another we have the ability to specify how we want the data to paste over – maybe we want to just copy the values of a cell rather than a formula, maybe we want to include or exclude the formatting and so on. One of the options we have is whether the data gets pasted in the same structure, or transposed.
To transpose our data in Excel using this method, first we need to either right click and select copy, or just use CTRL + C:
From here when we right click, under paste there will be a bunch of options to choose from outside of the paste button itself. The one circled in blue below indicates transpose:
Once we select this option, again using the top left most cell as the base, we are presented with the below output:
Outside of being a bit easier in that we aren’t actually typing out a formula, is that it has also copied the formatting with it! Super handy and saves us the time to manually get those headers looking the way we wanted.
In terms of the data itself, both the Excel TRANSPOSE function, and the pasting option essentially do exactly the same thing and outside of formatting really just come down to personal preference.
This sums up our guide on how to transpose data in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.