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 row to column by copying / pasting
- Transpose a table by copy /paste linked to the original data
- Transpose data in Excel using formulas
- Extract rows in Excel based on a certain column
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:
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.
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.
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?
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.
You will have a result similar to this:
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).
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.
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!
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).
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:
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).
5. Press CTRL+SHIFT+ENTER.
Done! Our data is transposed and we have 7 columns changed into 6 columns, 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 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.
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):
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.
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.
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.
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").
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?
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.
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.
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.
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.
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.
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.
4. Click the Finish button and you are done!
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!