How to transpose in Excel - convert row to column and vice versa

Transposing data in Excel is a problem familiar to many users. Quite often after building a complex table it becomes evident that it would be more efficient to rotate it for better analysis or transformation of data into graphs.

In this article, you will find several ways to convert rows to columns (or columns to rows) in Excel, whichever you call it, it's the same thing : ) These solutions work in all versions of Excel 2016, 2013, 2010, 2007 and lower, cover many possible scenarios, explain most typical mistakes and provide good examples of using Excel's Transpose function and formulas.

Convert rows to columns in Excel using Paste Special (Transpose)

Suppose you have created an Excel spreadsheet 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 may be too long, so we'd better change columns to rows for the table to fit within the screen:
Convert rows to columns in Excel

  1. Select the rows and columns you want to switch. If you want to rotate the entire 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 pressing Ctrl + C, or clicking the Copy button on the Home tab > Clipboard group.
  3. Select the first cell of the destination range into which you want to copy the transposed data.
    Note: It's important that you 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, once you transpose the table it will be 10 columns and 4 rows.
  4. Paste the copied data using Paste Special > Transpose feature. You can open the Excel Paste Special dialog in 3 ways:
    • On the Home tab > Clipboard group, click the little black arrow below the Paste button, and select Transpose.
    • Right click the destination cell and choose Paste Special from the context menu, then select Transpose.
    • And finally, the fastest way is to press Ctrl + Alt + V, select Transpose and click OK.

    Select Transpose in the Paste Special dialog.

    Note: If you transpose cells that contain formulas, make sure that absolute references (with $ sign) are used in all cells. This is necessary because your formulas will also be transposed and cell references automatically adjusted.

As, you have just seen, Excel's Transpose feature lets you perform row to column (or column to row) transformations literally in a few seconds. I believe this solution is a really good way to go if transposing a table is a one-time process. This method also copies the formatting of your original data, which adds one more argument in its favor.

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

  • The transpose option is not available when copying/pasting a fully-functional Excel table, as opposed to a simple range. In this case, you need either to copy the table without column headers, or convert it to range (right click any cell in the table and choose Table > Convert to Range from the context menu).
  • A more serious limitation of this method is that it creates a copy of your original data and whenever the source data change, you'd need to repeat the process and rotate the table anew. No one would want to waste their precious time on converting the same rows and columns over and over again, right?

How to transpose a table linked to the original data

Let's see how you can switch rows to columns in Excel using the familiar copy / paste technique that would link the transposed cells to the original data. The best thing with using this approach is that whenever you change the data in the source table, the transposed table will reflect the changes and update accordingly.

  1. Copy the rows you want to convert to columns (or columns to be converted to rows).
  2. Select an empty cell in the same or another worksheet.
  3. Open the Paste Special dialog, as explained in the previous solution and select Paste Link in the lower left-hand corner of the dialog window.
    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 this new data and open Excel's Find and Replace dialog (you can press Ctrl + H to get to the Replace tab straight away).
  5. Replace all "=" characters with "xxx" (you can actually replace it with any other character(s) that do 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 be afraid, 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 apply Paste Special > Transpose to convert the columns to rows, or rows to columns, depending on your needs.
  7. And 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. Done!
    The table is transposed with all the cells linked to the original data.

This is a quick, stress-free and elegant solution, isn't it? The only drawback of this approach is that the original table's 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).

Transpose data in Excel using formulas

If you are not looking for easy ways, then you can utilize formulas to convert columns to rows in your Excel tables and vice versa.

Convert rows to columns in Excel using TRANSPOSE function

As its name suggests, the Transpose function is specially designed for transposing data in Excel. If you don't have much experience with Excel formulas in general, and array functions in particular, please make sure you follow the below steps closely.

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

The table to be transposed

First off, you need to determine the exact number of rows and columns in your table. Our sample table has 7 columns and 6 rows, including headings. Keeping these numbers in mind, let's start rotating rows to columns.

  1. Count the cells you want to transpose. In our case it is 7 columns and 6 rows.
  2. Select an empty range of cells. Since the TRANSPOSE function shifts the vertical and horizontal orientation of a selected range, you need to select the same number of rows and columns as your source table has columns and rows, respectively. In our example, we are selecting 6 columns and 7 rows.
  3. Press F2 to enter the edit mode.
  4. Write the TRANSPOSE formula and enter the data range you want to transpose in brackets, like this:


    Note: You always need to use absolute references in the Transpose function.

    Use the TRANSPOSE formula to convert rows to columns.

  5. Press Ctrl + Shift + Enter.
    Note: You do need to hit Ctrl + Shift + Enter because this is an array formula, pressing simply Enter won't work.

Done! Our data is transposed and we have 7 columns changed into 6 columns, 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 also 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 sores table, the transposed cells will contain 0 instead.
  • You cannot edit any cells in the converted table because the TRANSPOSE function is very much dependent on the source data. If you try to change some cell's value, you will end up having "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.

Row to column / column to row transformation using INDIRECT function

This method of converting column to row and vice versa is pretty much similar to the previous one. Its main benefit over the Transpose function is that it allows making changes in the transposed table, at the same time keeping the connection to the original table.

We will use a small table in this example so that you can focus on the transpose process rather than on the data because we will be using a combination of INDIRECT and ADDRESS functions, which is a little bit tricky.

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

Rows to be converted to columns using INDIRECT function

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


    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-right-most cell of your source table. Also, please mind the use of absolute and relative cell references.

  2. Copy the formula through the range where you want to put the transposed data as usual by dragging a small black cross in the lower right hand corner of a selected cell or range of cells.

    Copy the formula through the range where you want to put the transposed data.

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

However, the transposed cells do look very plain and dull, compared to the original data.

The formatting of transposed data is lost.

But don't get disappointed, this is not a problem at all. You can easily restore the source formatting in this way:

  • Copy the original table;
  • Select the transposed table;
  • Right click the transposed table and choose Formatting under Paste Options.

Restoring the source table formatting

Summing up, INDIRECT seems to be a more flexible way to turn rows to columns in Excel than the TRANSPOSE function.

Advantages: you can easily edit any cell(s) in the transposed table, at the same time it stays linked to the original table and gets updated automatically whenever you make changes in the source data.

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

Now that you've got a feel how the INDIRECT function works, you may want to get the insight of what the formula is actually doing and why we use it in combination with the ADDRESS function. If you are not interested in tech details, you can skip directly to the next (the easiest!) way of extracting certain rows and converting them to columns.

INDIRECT and ADDRESS functions - syntax and logic

INDIRECT function, as its name suggests, is used to indirectly reference a cell. For example, if you want to have the value of cell B1 in cell A8, then you enter this formula in A8 =INDIRECT("B1").
The syntax of the INDIRECT function

But the real power of the INDIRECT function 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 next. 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 is used to obtain the address of a cell by the row and column numbers you specify, respectively. Please remember this: first - row, second - column. For example, ADDRESS(8,1) returns $A$8.

The COLUMN function returns the column number of the given cell reference. For example, =COLUMN(A3) returns 1, because column A is the 1st column. As you can easily guess, the ROW function works exactly in the same way for rows.

And now, let me remind you the entire formula that we've used to convert rows to columns in our Excel worksheet: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

As you see, in the ADDRESS function, we specify first COLUMN(A1) and then ROW(A1), and this is what actually does the trick! Remember, the ADDRESS function interprets the first number as a row number, and the second as a column number. In other words, the ADDRESS function takes a column number returned by the COLUMN function and changes it to a row number, then takes a row number and turns it to column, i.e. swaps rows to columns.

Now that you know what each function does, let's sum up the entire logic: COLUMN and ROW functions return the column and row numbers, respectively -> ADDRESS function converts column to row and row to column - > INDIRECT function outputs the rotated data to another cell in your Excel spreadsheet. Nothing dreadful at all, is it?

INDIRECT and ADDRESS functions - syntax and logic

Transpose data in Excel using VBA macro

It's not a big problem to write a script that transposes rows to columns, given that you have good knowledge of VBA. If you don't, you can find plenty of ready-to-use macros on the Internet. But if you are working with large tables, please keep in mind that VBA's Transpose method has a limitation of 65536 elements. In case your array exceeds this limit, the extra data will be silently cast away.

Extract rows in Excel based on a certain column

When you a huge data set in Excel, you may often need to extract some rows based on a certain column or pull data from selected columns only. At that, you may want to change a table in vertical orientation into a horizontal style placing the headings in the left most column and having the data in subsequent columns.

For example, we have a large employee database and want to extract the entries of all project managers from company's regional offices.
Extracting  the rows based on a certain column

The task sounds trickier than everything else we have done so far, right? But the solution is very simple! You won't need any complex functions or formulas, just a few clicks in the Extract Data's wizard.

Extract Data is an add-in for Microsoft Excel 2016 - 2003 specially designed for extracting rows of data from Excel worksheets and converting rows to columns. I will give you a quick example demonstrating how it works.

  1. Select the table from which you want to extract the rows and click on the Extract Data icon on the Excel Ribbon > Ablebits Data tab. You can actually click on any cell in your table and the add-in will select the entire table automatically.
    Extract Data add-in for Microsoft Excel 2016 - 2003
  2. Select the rows you want to retrieve. You can choose to extract data from all the columns by checking the Columns check box or from some of the columns, as show in the screenshot below.
    Select the data you want to extract.
  3. In this step, you specify the column by which you want to group the extracted data, it's the Position column in our case. Also, you choose whether to place the extracted and rotated rows in one spreadsheet or separate sheets, in the same or a different worksheet.

    If you are not sure which exactly layout suits you better, hover the mouse over the i icon to the right of the option you choose and you will see a mockup of how the data will be transposed.

    Specify the column by which you want to group the extracted data.

  4. Finally, you choose the destination for extracted and converted rows - the same workbook or a new workbook. You may also choose whether to add headers and auto fit column width.
    Finally, choose the destination for extracted and converted rows.
  5. Click the Finish button and you are done!

If you think this tool may prove to be useful in your work, go ahead with a  free download (15-day trial version). If you like it, the price will come as a pleasant surprise.

And this is all I have to say about converting row to column and column to row in Excel. Hopefully, you've found a solution you have been looking for, if not then don't hesitate to let me know in comments; if yes - please drop me a line too : ) Thank you for reading!

You may also be interested in:

34 Responses to "How to transpose in Excel - convert row to column and vice versa"

  1. tizabuzygirl says:

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

  2. Anonymous says:

    good job

  3. Oakley Com says:

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

  4. Anon says:

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

    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.

  5. Anonymous says:

    thank you so much. nice

  6. sandy says:

    "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:


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

      Thanks for pointing this out!

  7. MicaClem says:

    The transpose function = amaze balls!!!!

    Thank you

  8. Raja says:

    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


  9. Gopal Prasad says:

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

  10. judy says:

    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?

  11. Dave says:

    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.

  12. Rohan Singh says:

    Awesome Article. Thank you so much!

  13. Smitha says:

    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?


  14. Neha says:

    Thank you so much. This helped me a lot.

  15. Tess says:

    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

    Dec 13 8:00

    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

  16. Saurav says:

    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
    200090494 BRAINSTO-PPS-PLN-NTBKA5 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

  17. MICHAEL TAN says:

    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. Steven says:

    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.

    • Steven says:

      no takers?

    • Erin says:

      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.

  19. Sergio says:

    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.

  20. says:

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

  21. Lindy says:

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

  22. Victor says:

    Very helpful. Thanks.

  23. Dejene Duguma says:


  24. Riluvan says:

    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

  25. meme says:

    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

  26. tamiru says:

    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.

  27. ARIJEET PAUL says:


Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools -
60+ professional tools for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard