How to Transpose the data in Pivot table?

Question

I have a source content as shown below

Name    Age Month   Maths   Science Physics
John    21   1        80     88      76
John    21   2        89     99      78
John    21   3        76     76      89
John    21   4        78     78      90
John    21   5        88     89      96
Sara    22   1        76     76      89
Sara    22   2        78     78      90
Sara    22   3        88     89      96
Sara    22   4        76     76      89
Sara    22   5        78     78      90

and i am looking to create a pivot table in excel something like this.

Name    John
Age     All                 

Month               1   2   3   4   5
Maths               80  89  76  78  88
Science             88  99  76  78  89
Physics             76  78  89  90  96

Is this possible? Thanks for looking

Solution

1. On your destination sheet, select an empty range with the correct number of columns and rows.
2. On the Formulas ribbon, select Insert Function and then specify All Functions in the dialog box.
3. In the list of functions, select Transpose, and give the entire range from the source sheet that you wish to transpose;
4. Click OK - your data range should now be transposed on the destination sheet
5. Select the entire destination range and then on the Data Ribbon select Auto-Filter.
6. You can now filter on any of the columns as desired.

Comments

[Tim Williams]

You'd first have to unpivot the last three columns into a single "subject" column.

[SSK]

Hi Tim, yes i know if i break the columns into rows and create a pivot i can achieve what i am looking for, but the original data is 50000 rows with 10 columns and i dont want to multiply the records to 50000 X 10

[SSK]

I just want to achieve this without VBA or changing the format my source. However i can add additional columns if it does not exceed too much of size of the data

[L42]

The closer you could get is to make individual Pivots for each Subjects. Then from those pivots extract data using GETPIVOTDATA. But for this to work, you should be using XL 2010 for you to have a Slicer which will synchronize all pivot selections (eg. Slicer for name and age).