RSS

How do I change a column into a row or a row into a column in Excel 2007, 2010 and 2013?

08 Jun

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:

Swap excel rows for columns

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:

copying cells in excel

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)

How do I swap columns and rows in Excel

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:

swapped columns and rows

Your original cells are still there – so highlight their columns, right-click with the mouse and choose Delete:

Flipped cells in Excel

And here’s your spreadsheet, the opposite way around from how you started!

5 finished

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!

 
3 Comments

Posted by on June 8, 2016 in Excel, Short cuts

 

Tags: , ,

3 responses to “How do I change a column into a row or a row into a column in Excel 2007, 2010 and 2013?

  1. Alex Powers

    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!

    Like

     
  2. GinPoodle

    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.

    Like

     
    • Liz Dexter

      July 13, 2016 at 7:29 am

      Excellent – hope you enjoyed trying it out!

      Like

       

I love hearing from my readers - do please leave a comment!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: