by Svetlana Cheusheva, updated on
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.
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:
To switch rows to columns, performs these steps:
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.
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:
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.
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).
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.
As its name suggests, the TRANSPOSE function is specially designed for transposing data in Excel:
In this example, we are going to convert another table that lists U.S. states by population:
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.
=TRANSPOSE(A1:G6)
Voilà, the columns are changed to rows, exactly as we wanted:
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:
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.
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):
To have columns switched to rows, do the following:
=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
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:
But don't get disappointed, this problem can be easily fixed. To restore the original formatting, here's what you do:
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.
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?
To automate the conversion of rows to columns in Excel, you can use the following macro:
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.
With the macro inserted in your workbook, perform the below steps to rotate your table:
Enjoy the result :)
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:
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.
Done! The table is transposed, the formatting is preserved, no further manipulations are needed:
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
