How do I transpose columns in Excel?

Question

I have a spreadsheet that looks like this:

enter image description here

I want to automatically transform it to this:

enter image description here :

I know I can transpose it using this Window:

enter image description here

But the result is like this:

enter image description here

How can I get what I described?

Do you want a solution that will automatically transform it for you, hands-off? If so, @Raystafarian is right, VBA is the way to go. If you're okay with an approach that just involves laying in formulas by hand, there's a pretty simple way to go about it; I can post an answer if you want. — hBy2Py, Feb 25 '15 at 03:09

score 4 · Answer 1 · edited Mar 20 '17 at 10:17

4

You are trying to "unpivot" the data, as described in this question.

In your case, you'll need to add an extra column so the unpivot process works properly:
Press Alt+D then P to start the Pivot Table and Pivot Chart Wizard.
Select Multiple consolidation ranges and PivotTable:
Select I will create the page fields:
Specify the worksheet range:
Specify the destination as New worksheet and then Finish.
You'll now be presented with a new worksheet (that doesn't look very much like the data you want... yet):
Now comes the magic - double-click on the cell I've highlighted in red - the intersection of the grand totals and you'll get a new worksheet containing your original data the way you want.