# How do I transpose and combine cells in Excel?

Do you know how to generate the following?

Excel table:

``````+-----------------------+
| A | 1 | 2 | 3 | 4 | 5 |
+-----------------------+
| B | 1 | 2 | 3 | 4 | 5 |
+-----------------------+
``````

I want to turn it into:

``````+----+
| A1 |
+----+
| A2 |
+----+
| A3 |
+----+
| A4 |
+----+
| A5 |
+----+
| B1 |
+----+
| B2 |
+----+
| B3 |
+----+
| B4 |
+----+
| B5 |
+----+
``````

Can you do that in Excel?

-

## migrated from stackoverflow.comAug 11 '11 at 5:42

This question came from our site for professional and enthusiast programmers.

Here's a method that can produce the combinations that you need; although, this formula won't list them down in one column.

Let's say the data was in A1:F2, like so:

Select a range that's 2 columns by 5 rows below it, like so:

Type this into the Formula box, then press Ctrl + Shift + Enter:

``````=TRANSPOSE(A1:A2)&TRANSPOSE(\$B1:\$F1)
``````

You'll get this:

You can then just Copy-Paste the data. You'll get virtually the same result by using the array formula

``````={"A","B"}&{1;2;3;4;5}
``````

Hopefully, someone else can provide a better solution.

-

It looks like none of these answers are giving exactly what you asked for. You can use this formula to get the "transposed" table in another location.

``````=OFFSET(K\$1,INT((ROW()-6)/5),0) & INDIRECT("R" &  INT((ROW()-6)/5)+1 & "C" & COLUMN()+MOD(ROW()-6,5)+1,FALSE)
``````

To use this formula in your workbook, replace all "6"s with the row number of your target location, replace all "5"s with the width of your table (not counting the header), and replace "K\$1" with the address of the top-leftmost cell in your original table (the "A" in your example). Then fill down.

-

Sure you can. If you highlight the A cells and hit Ctrl+C to copy the test, then in the area you want to transpose it, right click the mouse and select "paste special" then at the bottom right of the option box that appears is "transpose" which will change the direction of the cells from horizontal to vertical.

You can actually do both the A cells and B cells at once, but it will transpose them in adjacent columns, so you'll need to do one final copy paste to put the B under the A values.

-
The transpose would only create a column of A,1,2, ... it will not create a column of A1, A2, A3 etc. – Dheer Aug 11 '11 at 5:29

First copy 1 to 5 and then use paste special and transpose them to column. Similarly for the vlaues on the second row. Then in the next column write a CONCATNATE formulae to join the fixed value A with the numbers.

-

Issun and Dheer answers are the best solutions. For completeness, you can also use the `TRANSPOSE()` formula. See here for more information

-