How to convert rows to columns in Excel (transpose data)

The tutorial shows different ways to switch rows to columns in Excel: formulas, VBA code, and a special tool.

Transposing data in Excel is the task familiar to many users. Quite often you build a complex table only to realize that it makes perfect sense to rotate it for better analysis or presentation of data in graphs.

In this article, you will find several ways to convert rows to columns (or columns to rows), whichever you call it, it's the same thing : ) These solutions work in all versions of Excel 2010 through Excel 365, cover many possible scenarios, and explain most typical mistakes.

Convert rows to columns in Excel using Paste Special

Suppose you have dataset similar to what you see in the upper part of the graphics below. The country names are organized in columns, but the list of countries is too long, so we'd better change columns to rows for the table to fit within the screen:
Convert rows to columns in Excel

To switch rows to columns in Excel, perform these steps:

  1. Select the original data. To quickly select the whole table, i.e. all the cells with data in a spreadsheet, press Ctrl + Home and then Ctrl + Shift + End.
  2. Copy the selected cells either by right clicking the selection and choosing Copy from the context menu or by pressing Ctrl + C.
  3. Select the first cell of the destination range.

    Be sure to select a cell that falls outside of the range containing your original data, so that the copy areas and paste areas do not overlap. For example, if you currently have 4 columns and 10 rows, the converted table will have 10 columns and 4 rows.

  4. Right click the destination cell and choose Paste Special from the context menu, then select Transpose.

Select Transpose in the Paste Special dialog.

For more information, please see How to use Paste Special in Excel.

Note. If your source data contain formulas, be sure to properly use relative and absolute references depending on whether they should be adjusted or remained locked to certain cells.

As, you have just seen, the Paste Special feature lets you perform row to column (or column to row) transformations literally in a few seconds. This method also copies the formatting of your original data, which adds one more argument in its favor.

However, this approach has two drawbacks that prevent it from being called a perfect solution for transposing data in Excel:

  • It is not well suited for rotating fully-functional Excel tables. If you copy the whole table and then open the Paste Special dialog, you will find the Transpose option disabled. In this case, you need either to copy the table without column headers or convert it to a range first.
  • Paste Special > Transpose does not link the new table with the original data, so it is well suited only for one-time conversions. Whenever the source data change, you'd need to repeat the process and rotate the table anew. No one would want to waste their time on switching the same rows and columns over and over again, right?

How to transpose a table and link it to the original data

Let's see how you can switch rows to columns using the familiar Paste Special technique, but connect the resulting table to the original dataset. The best thing about this approach is that whenever you change the data in the source table, the flipped table will reflect the changes and update accordingly.

  1. Copy the rows you want to convert to columns (or columns to be changed to rows).
  2. Select an empty cell in the same or another worksheet.
  3. Open the Paste Special dialog, as explained in the previous example and click Paste Link in the lower left-hand corner:
    Open the Paste Special dialog and select Paste Link.
    You will have a result similar to this:
    Links to the original data are copied to a new table.
  4. Select the new table and open Excel's Find and Replace dialog (or press Ctrl + H to get to the Replace tab straight away).
  5. Replace all "=" characters with "xxx" or any other character(s) that does not exist anywhere in your real data.
    Replacing all '=' with some other characters
    This will turn your table into something a bit scary, as you see in the screenshot below, but don't panic, just 2 more steps, and you'll achieve the desired result.
    The equality signs are replaced with xxx.
  6. Copy the table with "xxx" values, and then use Paste Special > Transpose to flip columns to rows
  7. Finally, open the Find and Replace dialog one more time to reverse the change, i.e. replace all "xxx" with "=" to restore the links to the original cells.
    The table is transposed with all the cells linked to the original data.

This is a bit long but elegant solution, isn't it? The only drawback of this approach is that the original formatting gets lost in the process and you will need to restore it manually (I'll show you a quick way to do this further on in this tutorial).

How to transpose in Excel using formulas

A quicker way to dynamically switch columns to rows in Excel is by using TRANSPOSE or INDEX/ADDRESS formula. Like the previous example, these formulas also keep the connections to the original data but work a bit differently.

Change rows to columns in Excel using TRANSPOSE function

As its name suggests, the TRANSPOSE function is specially designed for transposing data in Excel:

=TRANSPOSE(array)

In this example, we are going to convert another table that lists U.S. states by population:
The table to be transposed

  1. Count the number of rows and columns in your original table and select the same number of blank cell, but in the other direction.

    For example, our sample table has 7 columns and 6 rows, including headings. Since the TRANSPOSE function will change columns to rows, we select a range of 6 columns and 7 rows.

  2. With the empty cells selected, type this formula:

    =TRANSPOSE(A1:G6)
    Use the TRANSPOSE formula to switch rows to columns.

  3. Since our formula needs to be applied to multiple cells, press Ctrl + Shift + Enter to make it an array formula.

Voilà, the columns are changed to rows, exactly as we wanted:
The table is transposed and rows are converted to columns.

Advantages of TRANSPOSE function:

The main benefit of using the TRANSPOSE function is that the rotated table retains the connection to the source table and whenever you change the source data, the transposed table will change accordingly.

Weaknesses of TRANSPOSE function:

  • The original table formatting is not saved in the converted table, as you see in the screenshot above.
  • If there are any empty cells in the original table, the transposed cells will contain 0 instead. To fix this, use TRANSPOSE in combination with the IF function as explained in this example: How to transpose without zeros.
  • You cannot edit any cells in the rotated table because it is very much dependent on the source data. If you try to change some cell value, you will end up with the "You cannot change part of an array" error.

Wrapping up, whatever good and easy-to-use the TRANSPOSE function is, it certainly lacks flexibility and therefore may not be the best way to go in many situations.

For more information, please see Excel TRANSPOSE function with examples.

Convert row to column with INDIRECT and ADDRESS functions

In this example, will be using a combination of two functions, which is a little bit tricky. So, let's rotate a smaller table so can better focus on the formula.

Suppose, you have data in 4 columns (A - D) and 5 rows (1 - 5):
Rows to be changed to columns

To have columns switched to rows, do the following:

  1. Enter the below formula in the left most cell of the destination range, say A7, and press the Enter key:

    =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

  2. Copy the formula rightward and downwards to as many rows and columns as needed by dragging the a small black cross in the lower right hand corner of the selected cells:
    Copy the formula in all the cells.

That's it! In your newly created table, all of the columns are switched to rows.

If your data starts in some row other than 1 and column other than A, you will have to use a bit more complex formula:

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

Where A1 is the top-left-most cell of your source table. Also, please mind the use of absolute and relative cell references.

However, the transposed cells look very plain and dull, compared to the original data:
The formatting of transposed data is lost.

But don't get disappointed, this problem can be easily fixed. To restore the original formatting, here's what you do:

  • Copy the original table.
  • Select the resulting table.
  • Right click the resulting table and choose Paste Options > Formatting.

Restoring the source table formatting

Advantages: This formula provides a more flexible way to turn rows to columns in Excel. It allows making any changes in the transposed table because you use a regular formula, not an array formula.

Shortcomings: I can see only one - the formatting of the ordinal data is lost. Though, you can quickly restore it, as shown above.

How this formula works

Now that you know how to use the INDIRECT / ADDRESS combination, you may want to get the insight of what the formula is actually doing.

As its name suggests, the INDIRECT function, is used to indirectly reference a cell. But the real power of INDIRECT is that it can turn any string into a reference, including a string that you build up using other functions and the values of other cells. And this is exactly what we are going to do. If you are following up to this, you will understand all the rest with ease : )

As you remember, we have used 3 more functions in the formula - ADDRESS, COLUMN and ROW.

The ADDRESS function obtains the cell address by the row and column numbers you specify, respectively. Please remember the order: first - row, second - column.

In our formula, we supply the coordinates in the reverse order, and this is what actually does the trick! In other words, this part of the formula ADDRESS(COLUMN(A1),ROW(A1)) swaps rows to columns, i.e. takes a column number and changes it to a row number, then takes a row number and turns it to a column number.

Finally, the INDIRECT function outputs the rotated data. Nothing dreadful at all, is it?

Transpose data in Excel using VBA macro

To automate the conversion of rows to columns in Excel, you can use the following macro:

Sub TransposeColumnsRows() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8) Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8) SourceRange.Copy DestRange.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False End Sub

To add a macro to your worksheet, please follow the guidelines described in How to insert and run VBA code in Excel.

Note. Transposing with VBA, has a limitation of 65536 elements. In case your array exceeds this limit, the extra data will be silently cast away.

How to use the macro to convert row to column

With the macro inserted in your workbook, perform the below steps to rotate your table:

  1. Open the target worksheet, press Alt + F8, select the TransposeColumnsRows macro, and click Run.
    Macro to transpose rows to columns in Excel
  2. Select the range where you want to switch rows and columns and click OK:
    Select the range where you want to switch rows and columns.
  3. Select the upper left cell of the destination range and click OK:
    Select the upper left cell of the destination range.

Enjoy the result :)
Rows are converted to columns with a macro.

Switch columns and rows with the Transpose tool

If you need to perform row to column transformations on a regular basis, you may actually be looking for a faster and simpler way. Luckily, I have such a way in my Excel, and so do other users of our Ultimate Suite :)

Let me show you how to switch rows and columns in Excel literally in a couple of clicks:

  1. Select any single cell within your table, go to the Ablebits tab > Transform group, and click the Transpose button.
    Transpose tool for Excel
  2. The default settings work fine in most case, so you simply click Transpose without changing anything.

    If you'd like to Paste values only or Create links to source data to force the rotated table to update automatically with every change you make to the original table, select the corresponding option.
    Switching rows and columns in Excel

Done! The table is transposed, the formatting is preserved, no further manipulations are needed:
Columns are changed into rows.

If you are curious to try this and 70+ other professional tools for Excel, I invite you to download a trial version of our Ultimate Suite. Thank you for reading and I hope to see you on our blog next week!

Table of contents

72 comments

  1. how can i paste region wise data for any short cuts
    Exaple,
    US
    UK
    GM
    FR
    i have the more than 100 codes to paste with the region wise

  2. NAME JOHN
    CLASS C1
    MAIL aaaaa @sss.com
    PASSWORD asddf
    NAME ALEX
    CLASS C2
    MAIL aawe @sdd.com
    PASSWORD dff
    NAME MIA
    CLASS C2
    MAIL fer @csd.com
    PASSWORD dfjg
    NAME SARA
    CLASS C1
    MAIL ggjh @gfd.com
    PASSWORD hjyg

    Hello!
    This is an example of data I have. I need to transfer this data in columns under four parts.
    How can I do?
    Thank you

    NAME CLASS MAIL PASSWORD
    JOHN C1 aaaaa @sss.com asddf
    ALEX C2 aawe @sdd.com dff
    MIA C2 fer @csd.com dfjg
    SARA C1 ggjh @gfd.com hjyg

  3. This is brilliant. Thanks so much.
    How to change one row as column beside other columns.
    for example I have like this report:

    | | Product Name | A | A | A | B | B | B | C | C | C |
    | Seller Name | CCSeller | Target | GrossSales | Ach | Target | GrossSales | Ach | Target | GrossSales | Ach |
    | Julia | 123 | 300 | 250 | 83% | 30 | 20 | 67% | 45 | 60 | 133% |
    | Romeo | 124 | 150 | 20 | 13% | 18 | 20 | 111% | 21 | 1 | 5% |
    | Mahsa | 125 | 400 | 260 | 65% | 20 | 60 | 300% | 54 | 20 | 37% |

    I want to change automatically that report like this:
    | Seller Name | CCSeller | Product Name | Target | GrossSales | Ach |
    | ----------- | -------- | ------------ | ------ | ---------- | ---- |
    | Julia | 123 | A | 300 | 250 | 83% |
    | Romeo | 124 | A | 150 | 20 | 13% |
    | Mahsa | 125 | A | 400 | 260 | 65% |
    | Julia | 123 | B | 30 | 20 | 67% |
    | Romeo | 124 | B | 18 | 20 | 111% |
    | Mahsa | 125 | B | 20 | 60 | 300% |
    | Julia | 123 | C | 45 | 60 | 133% |
    | Romeo | 124 | C | 21 | 1 | 5% |
    | Mahsa | 125 | C | 54 | 20 | 37% |

  4. I have csf_hus_data which have 91 rolls and 800 plus columns. How can I convert this data in excel to 3 rolls and 3 columns only.
    Thanks

  5. how to convert from

    Raja
    128
    sathish
    148
    venkat
    129

    into

    raja 128
    sathish 148
    venkat 129

    8pls consider space as a next cell

  6. Hi, the formula was helpful, but i got a question in this if i need the same data on other sheet is it workable, or is any other formula. kindly help.

  7. What version of Excel is this for. I have a tall column of text and when I paste special I do NOT have an option panel that looks anything like yours. I have no panel at all.
    I'm using the Excel that comes with Office 365 - which auto updates to the current version all the time.

  8. How can i convert rows into 2 columns data. for example i have data in row as A1,A2,A3,A4,A5,A6 and i want to convert it to :
    A1,A2
    A3,A4
    A5,A6
    The same i want to repeat it for multiple rows converting data into 2 columns.

  9. I have 2 worksheets. in first worksheet there is data in alternate Columns A12,C12. E12, F12 etc. i want to copy this figures in sheet 2. like these A1,A2,A3,A4... etc. how i can do it in easy way

    • IN ELABORATE Sheet1 A12 data = Sheet2 A1, Sheet1 C12 data = Sheet2 A2, Sheet1 E12 data = Sheet2 A3 etc

  10. I have rows which contains formula and we need to create Row to Column.
    But due to formula into row when it transpose to column it shows a value to zero
    can you help me with this

  11. very nice and useful.

  12. 1
    2
    3
    4
    kindly share the fromula
    1,2,3,4

  13. Hello All,
    I have Pivot Data as below:
    Expense Claim ID Sum of Reimbursement Amount
    EXC050646 2,000.00
    EXC050647 5,000.00
    EXC051002 4,000.00
    EXC051003 5,000.00
    -----------------------------
    EXC050499 2,000.00
    EXC050736 5,000.00
    EXC050887 5,000.00
    EXC051055 3,000.00
    EXC051334 5,000.00
    I want it to be transpose as below:
    Claim ID 1 Claim ID 2 Claim ID 3 Claim ID 4 Amount 1 Amount 2 Amount 3 Amount 4
    EXC050646 EXC050647 EXC051002 EXC051003 2000 5000 4000 5000
    EXC050499 EXC050736 EXC050887 EXC051055 2000 5000 5000 3000

  14. my data is
    1) Sanjeev
    123, Rajouri
    New Delhi-110027
    2) Sanjeev 2
    456, Uttam Nagar
    New Delhi
    3) Sanjeev 3
    789, Raja Garden
    New Delhi-110028

    How to add this data from rows to column I used transpose but need to all data in one short i.e.
    Sanjeev 1 123, Rajouri New Delhi
    Sanjeev 2 345 Uttam Nagar New Delhi
    Sanjeev 3 789 Raja Garden New Delhi-110028

  15. Thank you Svetlana.

  16. anyone know how to get data from this
    Value dataA dataB dataC
    Car X Y Z
    to this
    Car X
    Car Y
    Car Z

  17. Sub Macro1()
    Application.ScreenUpdating = False
    Sheet1.Select

    Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Select
    ActiveCell.Offset(3, 0).Select ' Position Row 3

    J = 0
    BB = 0
    While J 20 Then GoTo End1
    End If
    'ActiveCell.End(xlToLeft).Select
    'ActiveCell.Offset(0, 0).Select

    A1 = ActiveCell.Value
    If A1 "" Then J = 0
    If A1 = "" And J > 0 Then
    ActiveCell.Offset(1, 0).Select ' move down
    Else
    ActiveCell.Offset(1, 0).Select
    End If

    Sheet2.Activate

    ActiveCell.Value = A1
    BB = BB + 1
    If A1 = "" Then
    ActiveCell.Offset(1, -(BB - 1)).Select
    BB = 0
    Else
    ActiveCell.Offset(0, 1).Select
    End If

    Sheet1.Activate

    End1:
    Wend
    Application.ScreenUpdating = True

    End Sub

  18. 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

  19. 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.

  20. 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

    • 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.

  21. 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

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

    • 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.

  23. 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.

  24. THANKS FOR HELP

  25. 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.

  26. 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

  27. 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

    • 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.

  28. Fantastic

  29. Very helpful. Thanks.

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

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

  32. 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.

  33. 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.

    • no takers?

    • 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.

      • Best answer

  34. 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

  35. 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

  36. 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

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

  38. 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

  39. Awesome Article. Thank you so much!

  40. 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.

  41. 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?

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

  43. 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

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

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

    Thank you

  45. "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.

    • 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!

  46. thank you so much. nice

  47. 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.

    • 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.

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

  49. good job

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

    • 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 :)