Excel Data, Pivot and Chart help

Copper Contributor

I am looking for some guidance. 

I have some academic course assessment data.  I need to report on the total percentage of times an assessment type appears at a specific academic level and for a specific subject group. 


For example COURSE A at LEVEL 4 has 3 ASSESSMENTS, ASSMNT 1 = Coursework, ASSMNT 2 = Coursework, ASSMNT 3 = Exam. 

When I have tried to produce a pivot to summarise the data I don't think I get the correct result as I can't seem  to group the 3 assessment columns to count as one overall. 





4 Replies


*screen caps and attachment removed at OP's request*


This task becomes much easier if you load the table in PowerQuery and 'unpivot' those last 3 assessment columns.

Unpivot columns (Power Query) - Microsoft Support


 Then all you need to do is choose 'close and load to' and opt to create a pivot table at the prompt.

That's perfect thank you. Is it possible to delete these posts? I have been asked because of the data-screenshots.
I've removed the screen captures and attachment so all that remains is a "general how-to", if that's OK with you.


Thank you Patrick. And once again many thanks for your help. I'm keen to understand the workings of that more so off to do some Power Query learning.