Mar
6

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 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 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 function and enter the data range you want to transpose in brackets: =TRANSPOSE(array). In our case, we enter =TRANSPOSE($A$1:$G$6).

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

Writing the TRANSPOSE function

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:

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

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

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

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

4. 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!

Want to find more inventive tips, tricks and tutorials?

Please follow us on Google+:

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

  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:

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

  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
    BECOME
    1
    4
    0
    5
    0
    6
    8

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

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!
Ultimate Suite for Excel Professionals
 
 
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
Awesome!!!
Sheila Blanchard