There are 3 ways you can transpose data in Excel (not including VBA).
Download the workbook and follow along
Enter your email address below to download the sample workbook.
1. Copy > Paste Special > Transpose
The downside of Paste Special > Transpose
It’s really only good for one-time use since it’s not linked to the original data. That means if the original data is updated you’ll have to do the whole Copy > Paste Special > Transpose all over again, and manual repetition is Excel blasphemy.
2. Use a formula like the built in TRANSPOSE Function
TRANSPOSE is a multi-cell array function and as such you enter it with CTRL+SHIFT+ENTER like any other array formula.
The TRANSPOSE function only has one argument:
Where ‘array’ is the range of cells you want to transpose.
First select the cells you want to place your transposed data in.
Tip: The trick here is knowing how big the range needs to be. I like to cheat a little and use the Copy > Paste Special > Transpose technique in step 1, and then with the pasted cells selected simply overwrite them with my TRANSPOSE formula.
Don’t forget to press CTRL+SHIFT+ENTER to complete the formula.
The downside of the TRANSPOSE function:
If the size of the source data expands or contracts you have to manually update the formula. Yawn.
3. Use Power Query to Transpose Data
Power Query is available in Excel 2010 onwards so this technique isn’t for everyone (sorry 2007 users).
- Select your data > go to the Power Query tab (Excel 2016 see Get & Transform on the Data tab)
- Click the From Table icon
- Check Excel has correctly detected the range of your data (change if required) and uncheck the “My table has headers” box
- Click OK.
Note: If your data is already in an Excel Table you won’t see the above From Table dialog box.
Once you click OK the Power Query editor window will open and you’ll be able to see a preview of your data:
On the Transform tab in the Table group choose ‘Transpose’:
Then choose ‘Use First Row as Headers’:
Now you’re ready to load your data back into Excel. On the Home tab > click ‘Close & Load To’:
Choose where you want to put your data. I’ve put mine in the Excel worksheet beside my original data so you can see them together:
The downside of using Power Query
What? There’s no downside 😉
Ok, some might say it’s more complicated but it took me 25.62 seconds to complete the steps above (I had my son time me with the stop watch Santa bought him for Christmas so it must be accurate). And the best thing about using Power Query for transposing data is:
- If your table expands or contracts a simple refresh of the query will update the transposed data to incorporate any changes.
- I can get data from almost anywhere and transpose it, as well as loads of other data cleansing tasks I might want to do. For example, merge or split columns, unpivot, filter out duplicates and more. MUCH more.
Click here for more information on what Power Query can do, what versions of Excel it’s compatible with and information about our Power Query training.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.