How do I transpose columns in Excel?

Question

I have a spreadsheet that looks like this:

I want to automatically transform it to this:

:

I know I can transpose it using this Window:

But the result is like this:

How can I get what I described?

Answer 1

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

1. In your case, you'll need to add an extra column so the unpivot process works properly:

   

2. Press Alt+D then P to start the Pivot Table and Pivot Chart Wizard.

3. Select Multiple consolidation ranges and PivotTable:

   

4. Select I will create the page fields:

   

5. Specify the worksheet range:

   

6. Specify the destination as New worksheet and then Finish.

   

7. You'll now be presented with a new worksheet (that doesn't look very much like the data you want... yet):

   

8. 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.