Microsoft

How-To Convert Excel 2010 Rows Into Columns and Back

Groovy Microsoft Office How-To, Tips, and NewsHave you ever been working on a project in Excel just to realize that it would look better if it were arranged horizontally instead of vertically (swapping around the Rows and Columns)?  Here is an age old Excel trick that works whether you’re using Excel 2010, 2007, 2003, or even the stone age Excel 2000. (Gasp!)

 

Here we have a typical spreadsheet in Excel 2010 that is lined up vertically using a column style.  Just for demonstration sake, we’re going to go ahead and change this into a horizontal row style.

excel 2010 in a vertical layout

 

1. In Excel 2010, Select all of the cells that you want to convert.  Once you’ve Selected them, Right-Click and Click Copy.*

*You can also use the keyboard shortcut Ctrl+C to copy.

copy an entire excel 2010 datasheet for layout conversion

 

2. Now Right-Click and empty cell and Select Paste Options: > Transpose. One of the groovy features in Outlook 2010 is that it will even show you a preview of what the paste will look like before it happens.

paste in excel using transpose

2.b  Alternatively if the Paste Options aren’t showing up.  Right-Click an empty cell and Select Paste Special.

paste special function of excel 2010

 

3. From the Paste Special window Check the Transpose box and then Click OK.

selecting transpose in excel 2010 paste

 

The result will be that your data has been converted (transposed) to a new layout style.  This tip will work the opposite way as well if you ever need to convert your horizontal row data sheets into the vertical column style.

image


13 Comments

13 Comments

  1. Clint  

    I can’t seem to get the transpose option when copying from one excel file and pasting to another. I have to add the extra steps of copying as-is, copy THAT list, transpose it to where I need it, then delete the original list I copied over. There’s got to be something I’m missing.

    • JC  

      This is an old post, so I’m sure you’ve figured this out, but I just ran into this problem and you solved it for me. I had been using transpose for a week and suddenly I couldn’t anymore. In searching for an answer, your post made me realized I had opened a second Excel session and, as you pointed out, you can’t transpose from one session to another. However, you can open up a second file in your current session and successfully transpose from one spreadsheet to the other, which is what I had been doing previously. They really should fix this, since sometimes you want to have two sessions open to see full pages side by side when copying and pasting. Anyway, thanks for solving my little mystery!

      • Mary Anne Sullivan  

        I’m very glad you posted this b/c i couldn’t figure out what i was doing wrong!

  2. VLad  

    Thanks for help Austin Krause!
    Excellent easy to read and straightforward article! Five Stars!

  3. SWEET! 5 STARS!!

    • Hi Wendy — welcome to my blog. I’m glad the tip helped you out!

      Have you tried out Excel 2013 yet? What do you think of it so far?

      • Shah  

        Hi Steve,

        Very nice feature, I am using this from 2010. Easy me to use in 2013.

  4. Dennis Mungle  

    Thanks. Too easy, I just forgot the steps.

  5. Phil Olivero  

    Perfect instructions, pictures replace a thousand words. The feature saved me a lot of time. I will use your Blog going forward.

  6. Lesley Payne  

    Hi, Many thanks, such a big help. Its something I have struggled with and once you know the answer you think, DOH! why didn’t I do that before. 🙂

  7. ankruxandra  

    Thank you!! Great help for me!!!

  8. Kate  

    Wonderful thank you,was given an assignment at work .Managed to do it quickly with your help.

  9. Ed  

    In your example, if the ‘Cats Like’ column referred to a different tab on the same workbook, is there a way to make this work when transposing columns to rows or vice versa? If so, please explain. I can only get the table to work if all the value come from the same tab.

    Thanks,

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top