Excel Converting rows to columns with groups

Question

Let's say that I have my columns in excel set up like this:

Name     A    B    C    D  
George   10   5    4    6

And I want it to instead be like this:

Name   Category   Amount  
George     A        10  
George     B         5  
George     C         4  
George     D         6

Is there an easy way to do a copy and paste with transpose, or with a pivot table, or does it have to be done with VBA? I've been trying to only do it with VBA but I havent gotten very far.

Answer 1

If you want to maintain a "dynamic" link between your source and target data, so whenever your source data changes, your target also does it automatically, you need formulas. In that case, you can use Item 7 from Excel: Formulas for converting data among column / row / matrix :

To get in matrix_data3 the transpose of matrix_data2, one only needs to use matrix_data2_top_left and matrix_data3_top_left, with the formula

=OFFSET(matrix_data2_top_left,COLUMN()-COLUMN(matrix_data3_top_left),ROW()-ROW(matrix_data3_top_left))

and copied to a suitable target range.

Note that matrix_data2_top_left and matrix_data3_top_left are convenient Excel range names for the upper left cells of the source and target ranges, respectively, and they can be replaced in the formula by their absolute references (e.g., $B$1 and $B$6). Also, matrix_data2 and matrix_data3 are Excel range names, but these ranges (either as names or absolute references) are not needed here.

Answer 2

How about a real answer instead of a link?

1. Highlight the two rows to pivot.
2. Copy
3. Find open row
4. Paste Special -> Transpose

Answer 3

As per comment, you can follow that link. A related SO answer that describes similar steps for Excel 2010 is How can I transform rows into repeated column based data?.