I wanted to do a pivot table as shown below.

Clearly it is not correct, as Business is the most popular subject and it shouldnt have any "<0 or (blank)".

I am assuming that the problem here is that I haven't sorted out my data. But which column should I sort (before creating the table)?

enter image description here

I tried to use the Business column, then REFRESHED my table,

enter image description here

It is still rubbish.

  • click on the little drop down trianlge next to Row Lables, and you should be able to de-select (blank) – PeterH Jan 4 '18 at 15:53
  • @User91504 But either way, the entries in the group are not even correct. It looks like a random group? – Chen Stats Yu Jan 4 '18 at 15:57
  • what do you have in place for Row Labels ? when you right clicck and show list values – PeterH Jan 4 '18 at 15:58
  • @User91504 I used Bio at the moment. The numbers that are under each group dont even come from Bio. – Chen Stats Yu Jan 4 '18 at 15:59

Your data is not in the correct structure for a PivotTable. You need to unpivot it. Google "Unpivot" and "Excel" and you will find many ways to do this, including using the PowerQuery addin (available natively in Excel 2013 or later under the "Get & Transform" tab).

After it is unpivoted, you will have two columns: Course Value

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.