Pivot table noob in need of guidance

[veeejay]

I'm trying to create several pivot table to clean things up in a massive excel spreadsheet.
In my columns I have about 75 questions, and the answers are on separate rows. I tried "grouping" some of the questions to create pivot tables with similar content and get the count of each possible answer for them (5 answers possible : poor, fair, good, very good and excellent) but I can't for the life of me figure out how to make the pivot table work.

First image is an example of how the table would look like before
Screen Shot 2020-12-10 at 3.29.00 PM.png
And the second image is what I want to achieve with the pivot table
Screen Shot 2020-12-10 at 3.29.05 PM.png

I've also included a test spreadsheet if someone is kind enough to show me how to proceed to achieve the desired result...
Pivot Test.xlsx

Thanks.

[Andy Pope]

You need to restructure your data to get the After layout.

The data should be 3 fields.
ID|Question|Answer

I also added the answers to the Custom Lists in order to get the order, which would usually be alphabetical.


If you stick with your original layout you can see 1 question at a time.