In this article, I’m going to explain how to change a column into a row or change a row into a column in Excel 2007, 2010 and 2013 (I think this works in 2016 too but have not yet tested it).
Why would I want to swap a column for a row?
You might start off creating a header row then decide it should be a column, or create a spreadsheet then want to rotate it 90 degrees. Or, worse, someone might decide that for you and expect you know how to do it!
How to change a column into a row or rotate a block of cells
Here’s our original block of cells:
We want to turn this around so that everything runs along the top rather than down the side. Here’s how to do it:
First, copy all the cells you want to move:
Highlight the cells, right-click with the mouse, and select Copy:
Now, and this is important, find a free, empty cell to paste into. Don’t worry about it being in the middle of the spreadsheet, we will tidy that later.
Right-click with your mouse in an empty cell and then choose Paste Special and Transpose (a hint will appear when you hover over the button, but it’s the one on the bottom right)
Behind all those dialogue boxes, Excel will show you what this is going to look like.
Click on Transpose and your cells will appear, starting from the empty cell you clicked on:
Your original cells are still there – so highlight their columns, right-click with the mouse and choose Delete:
And here’s your spreadsheet, the opposite way around from how you started!
In this article, we’ve learned how to change rows into columns and change columns into rows in Excel 2007, 2010, 2013 and probably 2016.
If you’ve found this article helpful, please do post a comment below, and if you think others would find it useful, please share it using the sharing buttons below the article. Thank you!
Other useful posts on Excel on this blog:
How to view two workbooks side by side in Excel 2007 and 2010
How to view two pages of a workbook at the same time
How do I print the column headings on every sheet in Excel?
How to print the column and row numbers/ letters and gridlines
Freezing rows and columns in Excel – and freezing both at the same time
How to flip a column in Excel – turn it upside down but keep the exact same order!
June 12, 2016 at 7:13 pm
Transpose is such a great tip for getting people’s data back into a manageable state. Fields and records people! Fields and records!
July 8, 2016 at 7:38 am
Thank you for that Liz I didn’t know about that, I’m going to practice that when I get to work in a mo.
July 13, 2016 at 7:29 am
Excellent – hope you enjoyed trying it out!