Comments on: How to convert rows to columns in Excel (transpose data)

In this article, you will find several ways to convert rows to columns and columns to rows in Excel. These solutions work in all versions of Excel 365 - 2007, cover many possible scenarios, explain most typical mistakes and provide good examples of using Excel's Transpose function. Continue reading →

Comments page 2. Total comments: 74

  1. Hi, I have a large data set (500000 columns). Certain data contained within the row I need to transpose into a new row. For example a row will contain part number, 1mg pack size, price, 2mg pack size, price etc.
    I would be hoping to transpose so it reads,
    Part number, 1mg pack size, price
    Part number, 2mg pack size, price
    Etc
    Is this possible?
    Thanks

  2. Good Afternoon -
    I'm going to do my best to make this look right on the screen.

    3128
    Product number: 7896
    Product: Apples
    Importer: Rick
    Owner: Gyna
    Name of receiver: Bill
    Number of cases: 1232
    Date: 10/15/18
    Amount: $3.00

    7898
    Product number: 456
    Product: Oranges
    Importer: Richard
    Owner: Gyna
    Name of receiver: Henry
    Number of cases: 1
    Date: 10/15/18
    Amount: $13.00
    Approval: N/A

    OK... so I need this transposed. There are hundreds of these. Some on them have 5 lines of information, some 6 and some 7.
    Is there a macro that can tell Excel 2016 to grab the information from a row labeled ie "owner" and place it in a column named "owner"?
    I am dealing with garbage data.
    Does this even make sense?

    And mind you... This information has been exported out of a PDF. They are purposely making this difficult if I have to guess.

  3. Dear all,

    i have a problem, i have following situation, everything is in column A
    A1
    A2
    A3
    A4
    B1
    B2
    B3
    B4
    and so on, i want to look like this
    A1 A2 A3 A4
    B1 B2 B3 B4

    I tried formula from this tutorial but it's only good for first row, later everything is mixed up. I obviously doing something wrong but can't see it.

    Thanks in advance.

    Kind regards

    Dario

    1. Dario:
      Select the cells you want to transpose. In your case select the first four cells, select Copy. Move the cursor to the right one column. Select the first cell, right click and select Paste Special, select Transpose, click OK. Select the next four cells, select Copy, move the cursor one column to the right, select the first cell and right click. Select Paste Special, select Transpose click ok.
      Repeat this process until you've moved all the data.

  4. Hi,

    This is Vivek, while I writing the function not getting proper data.My input is below.

    Jan 100
    Feb 200
    Mar 300
    Apr 400
    While pasting check the Transpose Its works properly but while writing function(=TRANSPOSE(A1:B4) and press Ctl+Shift+Enter) I'm getting output is Jan only didn't get all values.I'm using Excel 2007.Please let me know.

    Thanks,
    Vivek

  5. To add to the above description I have 20,000 questions along with their answers in this single column file.

    1. Ryan:
      Because each Copy/Transpose procedure has to be done with a specific number of rows that you choose, I would say this has to be done manually. After which you'll have to go through the data and clean it up. That question being split is a problem by itself. Sorry.
      Just need to put your head down and grind through it.

  6. Hi Team,

    I need some help:
    I have the question along with 4 answers listed in a single column (column a) in excel
    EG:
    q) The color of the
    sky is
    a) blue
    b) green
    c) silver
    d) purple
    The sky is blue because blah
    blah blah blah

    I need to transpose this to the format below where the question and each answer and finally the description is stored in a separate column:
    q) the color of the sky is a) blue b) green c) silver d) purple The s

    Please suggest a solution.

  7. THANKS FOR HELP

  8. It is realy an interesting lecture. However, I am not able to transpose one column into multiple of rows of cumulative data at every two years. eg. it refuses to transpose when i want to transpose 1-2 year of data from the column to the first row, 2-3 years of data from the same column to the second row,3-4 years of data to the third row etc.......cumulatively.

    thank you in advance.

  9. Hi
    I have a really large file up to 30000 rows. I need to extract one column based on another column and transpose. I've installed your tool but it isn't showing the wizard as described. I need to transpose below but pivot tables aren't helping... will your tool help?

    DocN DocTitle
    1006 ABCE Pathway fred
    1006 ABCE Pathway mardy
    1006 ABCE Pathway pod
    1006 ABCE Pathway pony
    1006 ABCE Pathway mouse
    1007 HIJK Guidance veg
    1007 HIJK Guidance meat
    1007 HIJK Guidance biscuits
    1007 HIJK Guidance honey
    1029 Pod Form green
    1029 Pod Form blue
    1029 Pod Form red
    1029 Pod Form yellow
    1029 Pod Form blue box
    1029 Pod Form hat
    1019 Pod Fod green wall
    1019 Pod Fod blue
    1019 Pod Fod red ball
    1019 Pod Fod yellow
    1019 Pod Fod blue box
    1019 Pod Fod hat
    1019 Pod Fod bluey

    DocN DocTitle
    1006 ABCE Pathway fred mardy pod pony mouse
    1007 HIJK Guidance veg meat biscuits honey
    1029 Pod Form green blue red yellow blue box hat
    1019 Pod Fod green wall blue red ball yellow blue box hat bluey

  10. Dear Team,

    Good Day!

    Could you help me to find the solution for propble,

    I have the date like below

    I have data like this But, In need to tranpouse like below
    ID Type2 Code
    VST2754800 Principal 714.9 714.9 041.9 268.9 274.9 460 714.0 790.6
    VST2754800 Secondary 041.9
    VST2754800 Secondary 268.9
    VST2754800 Secondary 274.9
    VST2754800 Secondary 460
    VST2754800 Secondary 714.0
    VST2754800 Secondary 790.6

    VST2827596 Principal I10 I10 E56.9 E78.4 K30 R10.11 R30.0 R53.83
    VST2827596 Secondary E56.9
    VST2827596 Secondary E78.4
    VST2827596 Secondary K30
    VST2827596 Secondary R10.11
    VST2827596 Secondary R30.0
    VST2827596 Secondary R53.83

    1. Hello,
      Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.
      Sorry we can’t assist you better.

  11. Fantastic

  12. Very helpful. Thanks.

  13. may i please now how to transpose column values that have been auto-calculated into rows

  14. Is there any way to do transpose at a time for multiple columns to rows for all sheets in workbook

  15. It's nice your work but if you are going to change the size of your table you need to be very careful with these procedures because you can confuse and get another result that you don't want.
    Take care all of you.

  16. I have a large spreadsheet with close to 5000 rows.

    Columns are:
    Col1 Col2 Col3 Col4 Col5 Email1 Email2 Email3 Email4 Email5 Email6 Email7

    I need to repeat columns 1-5 seven times in order to show each email on a separate row.

    So it would like this:
    Col1 Col2 Col3 Col4 Col5 Email1
    Col1 Col2 Col3 Col4 Col5 Email2
    Col1 Col2 Col3 Col4 Col5 Email3
    Col1 Col2 Col3 Col4 Col5 Email4
    Col1 Col2 Col3 Col4 Col5 Email5
    Col1 Col2 Col3 Col4 Col5 Email6
    Col1 Col2 Col3 Col4 Col5 Email7

    Thanks in advance for your help.

    1. I believe you could do this with Query Editor.
      1. Open your worksheet in query editor
      2. Open the transform tab
      2. Make sure headers are correct. You may have to select "use first row as headers"
      3. Select all columns that you want to stack into one column (cntr+shft)
      4. Select to unpivot table. This should do the trick.

      1. Best answer

    2. no takers?

  17. Is there a fast way to convert or change the files in 1 row ex row a1 b1 c1 d1 e1 f1 to 1 column exp A1 A2 A3 A4 A5 A6

  18. One Order No. has 4 different sku and 3rd coloumn signifies the quantity of units ordered.. Blank space in first column represents that all sku s belong to order no in the above row. This is what I have:
    No sku Qty
    200090505 DO-NANO-CABLE-WRAP-TEAL 2
    DO-PICO-CABLE-WRAP-TAN 2
    DO-SMALL-CABLE-WRAP-TEAL 2
    DO-TINY-CABLE-WRAP-BEIGE 2
    200090494 BRAINSTO-PPS-PLN-NTBKA5 1
    DO-SMALL-CABLE-WRAP-BEIGE 1

    This is How I want Basically Transpose it in 1 single row: All sku's in 1 single row adjacent to the order:
    Column A B C D E F G H I
    Order200090505 SKU QTY SKU QTY SKU QTY SKU QTY
    Order200090494 SKU QTY SKU QTY

  19. In excel, how can I transpose data in vertical column into 4 columns and 2 rows because these data pertain to 2 days?
    The raw data are the following:

    Dec 12 8:15
    12:00
    1:00
    5:00

    Dec 13 8:00
    12:15
    1:00
    5:15

    I want it to look like this:

    12/12/2016 8:15 12:00 1:00 5:00
    12/13/2016 8:00 12:15 1:00 5:15

  20. Thank you so much. This helped me a lot.

  21. My data is in the below format.

    Type 1/1/2016 1/2/2016 1/3/2016......
    A a b g
    B g a b
    C b g a

    I want to change it to the below format:-

    Type Date Series
    A 1/1/2016 a
    B 1/1/2016 g
    C 1/1/2016 b
    A 1/2/2016 b
    B 1/2/2016 a
    C 1/2/2016 g
    and so on.

    I have data for more than 6 months and 35 rows each.

    Is there any way I can transpose the data in this format?

    Thanks,
    Smitha
    C

  22. Awesome Article. Thank you so much!

  23. Why do you say "You always need to use absolute references in the Transpose function" -- ? It seems to work just fine without the "$" signs. If I move the source cells, the transposed cells stay the same. If I move the transposed cells, they still stay as is.

  24. Have multiple data lines for same person (not everyone) with multiple values on each line and be a flat figure or a % and would like to move to columns and end up with 1 line for all.

    Retirement spreadsheet: the attributes that were created in the HR database was not done in a hierarchy form so each of the below are unique lines.

    Roth flat
    Roth %
    Traditional flat
    Traditional %
    Partner flat
    Partner %
    Catch up
    Partner catch-up

    Is there a simple way to move these into columns?

    1. Hello, Judy,

      Looks like you need a VBA script for your task. Sorry, we cannot help you with it.

  25. I want to stack multiple Columns in a column but don't know how??

  26. Hi Svetlana

    I wan to transpose row to column and skip the blank cells..
    The row could be contain hundred cells,should be transpose to one column avoid the blank.

    1-4-0-5- -0-6- -8
    BECOME
    1
    4
    0
    5
    0
    6
    8

    PLS HELLP ME TO MAKE IT WORK..in VBA or Formula
    THANKS

    1. You can use wrap text here to get all values in one cell.
      =CONCATENATE(H9,
      ,"
      ",
      I9)

  27. The transpose function = amaze balls!!!!

    Thank you

  28. "If your data starts in some row other than 1 and column other than A, simply replace A1 in the formula with a reference to your top-right-most cell."

    Not true. It doesn't work with other than A1.

    1. Hi Sandy,

      Oops, you are right, it does not work.

      The formula for other rows and columns is not so obvious:

      =INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

      Where A1 is the top-right-most cell of your source table.

      Thanks for pointing this out!

  29. thank you so much. nice

  30. I have a problem in need of a solution.
    I have columnar data like this
    Type1 Type2
    ann bob
    bob chris
    chris john
    sue

    How do I turn this into a data format presented like this
    Type1 Type2
    ann X
    bob x x
    chris x x
    john X
    sue X

    Of course, there are many types and hundreds of names.

    1. Create a pivot table. Use the peoples' names as the "Row". Then select the data you wish to count in the "Values". Make sure that the the value type is either "Sum" or "Count", depending on what you're looking to do with the data.

  31. Aw, this was a really nice post. In idea I would like to put in writing like this additionally

  32. good job

  33. This is brilliant. Thanks so much. You saved me hours and hours of time.

    1. Thank you very much! I'm really glad to know this article was helpful to you.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)