Top Nav

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 was 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 end result will be that your data has been converted (transposed) to a new layout style.  This 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 Responses to How-To Convert Excel 2010 Rows Into Columns and Back

  1. Clint March 30, 2011 at 12:37 pm #

    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 February 29, 2012 at 12:22 pm #

      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 January 16, 2013 at 11:46 am #

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

  2. VLad October 25, 2011 at 8:00 pm #

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

  3. Wendy Lauber Kilbourne August 13, 2012 at 8:46 am #

    SWEET! 5 STARS!!

    • Steve Krause August 13, 2012 at 9:32 am #

      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 April 6, 2013 at 3:54 am #

        Hi Steve,

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

  4. Dennis Mungle December 17, 2012 at 8:02 am #

    Thanks. Too easy, I just forgot the steps.

  5. Phil Olivero April 25, 2013 at 6:56 am #

    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 September 6, 2013 at 2:43 am #

    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 October 15, 2013 at 8:53 am #

    Thank you!! Great help for me!!!

  8. Kate October 25, 2013 at 1:55 am #

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

  9. Ed April 14, 2014 at 3:41 pm #

    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

 

×

Subscribe to our free newsletter and have our tips delivered to your inbox. Free Signup