Changing rows into columns in Excel

Question

I have a table that looks like this in Excel:

|firstname |Greg     |
|secondname|Armstrong|
|firstname |Joe      |
|secondname|Bloggs   |
|firstname |Jane     |
|secondname|Doe      |

And I need to change it to look like this:

|firstname|secondname|
|Greg     |Armstrong |
|Joe      |Bloggs    |
|Jane     |Doe       |

I'm not particularly well versed in all the functionality that Excel has so I don't even know what to look for.

Any help would be appreciated. Thanks.

score 1 · Accepted Answer · answered May 19 '16 at 22:04

Assuming the original table is in columns A and B, pick any cell and enter:

=OFFSET($B$1,COLUMNS($A:A)-1+(ROWS($1:1)-1)*2,0)

copy this cell across and down:

Note:

Using the right formulas, you can always map any single column or row into a 2-D table and any 2-D table into a single column.

1 Answers1