I have been asked to make graphs of some evaluation forms, however I don't see how to make readable graphs with the data they gave me:

the data they gave me.

Every group has its own little table with data, the graph they want is an overview of how often each grade has been given.
For each group we want two stacked column charts, one per category. In which each part of the stack is the amount each grade is given. Something like this:

Something like this

(though ignore the line), but then with two columns per group.

Eventually more groups will be added and they have to be added to the charts as well, with minimum work.

I'm working in excel 2013.


You will probably need to restructure your data a bit in order to generate a chart of the type you want (skip to the end to see the chart!)

Put all your data into one table, with these headings:

- Group (A-E or however many groups you have)
- Grade (1-10)
- Frequency (number of students with that grade in that group)

You should get something like this:

enter image description here

Now, click select any cell in this table and on the ribbon click Insert, then PivotTable.

In the grey popup box click and drag Group and Grade into Rows, and drag Frequency into Values. You should get something like this:

enter image description here

Now, click somewhere in your PivotTable and insert a chart You should get a chart like this, grouping first by the Group name, then showing the distribution of total students by grade.

enter image description here

| improve this answer | |
  • Thank you for the answer, although it is not really what I was looking for. We want to compare the grades given for the trainer and the total. If I understand correctly with this method you'd only compare the grades given in a group for either total or trainer. We'd like to compare both. – Talkenia Dec 12 '18 at 10:31
  • Like this you mean? i.stack.imgur.com/jDqzt.png You could just add a second frequency column for the trainer's scores. – Andi Mohr Dec 12 '18 at 12:41

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.