by 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:
To switch rows to columns in Excel, perform these steps:
- 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.
- Copy the selected cells either by right clicking the selection and choosing Copy from the context menu or by pressing Ctrl + C.
- 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.
- Right click the destination cell and choose Paste Special from the context menu, then select Transpose.
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.
- Copy the rows you want to convert to columns (or columns to be changed to rows).
- Select an empty cell in the same or another worksheet.
- Open the Paste Special dialog, as explained in the previous example and click Paste Link in the lower left-hand corner:
You will have a result similar to this:
- Select the new table and open Excel's Find and Replace dialog (or press Ctrl + H to get to the Replace tab straight away).
- Replace all "=" characters with "xxx" or any other character(s) that does 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 panic, just 2 more steps, and you'll achieve the desired result.
- Copy the table with "xxx" values, and then use Paste Special > Transpose to flip columns to rows
- 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.
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:
In this example, we are going to convert another table that lists U.S. states by population:
- 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.
- With the empty cells selected, type this formula:
=TRANSPOSE(A1:G6)
- 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:
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):
To have columns switched to rows, do the following:
- 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))) - 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:
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:
- Copy the original table.
- Select the resulting table.
- Right click the resulting table and choose Paste Options > 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:
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:
- Open the target worksheet, press Alt + F8, select the TransposeColumnsRows macro, and click Run.
- Select the range where you want to switch rows and columns and click OK:
- Select the upper left cell of the destination range and click OK:
Enjoy the result :)
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:
- Select any single cell within your table, go to the Ablebits tab > Transform group, and click the Transpose button.
- 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.
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!
74 comments
Hi i have found 500 keyowrds list for my website.
the keywords are related to Ayurveda with their search volume and keyword difficulty. i have copied data from google keywords planner and past it in excel. but the coppied coloumn from google keywords planner of the keywords difficulty and search volume are shows in rows in the excel sheet.
I just wanted to know that how i can convert the alternate rows into the coloumn.
Thanks
Ajay
Hi! From your question, it is impossible to understand how your data is written. If recommendations in the article above cannot help you solve the problem, please give an example of your data and explain desired result.
data
A1:G7
totally 7x7 table.
i want to put all data in single column in H..... all datas must be in H column. starting from A1 to G1, the A2 to G2, till A7 to G7. how to do it simple with a code
Hi! To convert a 2D array in Excel to a 1D array using a formula, you can use the TEXTJOIN and TEXTSPLIT functions. Here's how:
=TEXTSPLIT(TEXTJOIN(" ",TRUE,A1:G7),," ")
The TEXTJOIN function will merge all array values into a text string. The TEXTSPLIT function will split this text into a separate value using a space and write these values into a column.
I hope this helps!
Hi Team,
I want convert few rows to multiple columns, Its not happening with transpose function, is this any other function I can use, I am using Excel 365
pls find the sample data
Emp_No allow amount
1111111 bonus 100
1111111 incentive 150
1111111 Var Pay 200
3333333 bonus 250
2222222 incentive 300
2222222 Var Pay 350
It has to reflect as the below table
Emp_No bonus incentive Var Pay
1111111 100 150 200
2222222 0 300 350
3333333 250 0 0
I tried with filter, wrapcol, tocolumns and I didn't succeeded to get this data. can you pls help me to resolve this issue, Thanks.
Hi! If I understand your task correctly, our Ultimate Suite for Excel may help you solve it in a few clicks. First use the Merge Duplicates tool to merge the amount for each unique Emp_No value. Then use the Split Text tool to split the amount data by column.
You can install Ultimate Suite for Excel in a trial mode and check how it works for free.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Thank you for your quick support, Thank you for introducing Ultimate Suite for Excel tool, I will go through this.
Looks like I have not communicated properly.
My data is in different columns and its not merged.
Emp_No Allow amount
1111111 Bonus 100
1111111 Incentive 150
1111111 Var Pay 200
3333333 Bonus 250
2222222 Incentive 300
2222222 Var Pay 350
It has to reflect as the below table - Unique data of first column should reflect in column and second column should reflect as header. Now I have prepared with unique formula for hinderers and sumifs /sumproduct for values. I was looking if any alternative method.
Emp_No Boonus Incentive Var Pay
1111111 100 150 200
2222222 0 300 350
3333333 250 0 0
Hi! You can solve this problem using several formulas. Extract unique values from columns A and B using the UNIQUE function. Use the TRANSPOSE function to write the result in a row. To find the values by row and column, use this guide: Excel INDEX MATCH MATCH and other formulas for two-way lookup.
Copy the formula in cell G2 to the right and down in the table.
Cell F2: =UNIQUE(A1:A6)
Cell G1: =TRANSPOSE(UNIQUE(B1:B6,FALSE))
Cell G2: =IFERROR(INDEX($C$1:$C$6,MATCH(1,($F2=$A$1:$A$6)*(G$1=$B$1:$B$6),0)),"")
Thank you very much for guidance, I tried and its perfectly working for requirement, Thanks again.
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
Hi! Your question is not entirely clear. Does your problem have any relation to the article above? Explain.
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
Hi! To write your data in 4 columns, I recommend trying our Ultimate Suite for Excel.
Step 1: Split the text by columns using the Split Text tool. The separator is a space.
Step 2. Convert 2 columns to 4 columns using the Create Cards tool.
Step 3. Remove duplicates using the Remove Duplicates tool.
You will get exactly the result you wanted.
You can install Ultimate Suite for Excel in a trial mode and check how it works for free.
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% |
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
how to convert from
Raja
128
sathish
148
venkat
129
into
raja 128
sathish 148
venkat 129
8pls consider space as a next cell
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.
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.
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.
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
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
very nice and useful.
1
2
3
4
kindly share the fromula
1,2,3,4
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
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
Thank you Svetlana.
anyone know how to get data from this
Value dataA dataB dataC
Car X Y Z
to this
Car X
Car Y
Car Z
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