Creating pivot table from survey (qualitative & quantitative responses)

[Kaycee831]

hello excel gurus

I'm hoping someone can help. I work for a charity and are required to carry out a Board performance evaluation / survey.

It has various columns for respondents to complete including qualitative / quantitative data. I believe it may not be possible to create a pivot table for both sets of responses, but wanted to ask this community if in deed there may be a solution? I've attached the file for reference.

could someone advise how i can create a pivot table to analyse results by group and according to each question, so for example if i want to know the total/average score for the questions for group 1 / group 2 or all combined groups.

Then how best would it be to document the responses to the qualitative data?

sorry if this isn't very clear. Basically i need to be able to create a report to the Board of the results of the questions for each group, and also provide (in some form) the text based responses.

thank you so much in advance.

PS: i tried a word form solution and import to excel, but that wasn't successful.

[JeteMc]

I came up with something but not sure that it is what you want.
Summed the four groups listed in row one of the scores page using: =SUM(C4:Q4) etc.
The pivot table on Sheet1 puts the recommended practice in the Rows area and the sums of the four groups in the Values area.
(Values filter is applied to only show those practices that have a sum of greater than zero)
I don't know what text you want to show other than the practices. It may help someone better understand what you want if you would mock up some results.
Let us know if you have any questions.

[Kaycee831]

hi JetMc

Thank you so much for that! That's really helpful.

In terms of the qualitative data, as there will be text answers to the questions under 'Recommended Practice' I wasn't sure if there was an easier way of transferring those responses into an excel sheet other than 'copy/paste' from the respondents survey form to an excel doc. I don't believe there is, as it's not something that can be statistically analysed unlike the actual scoring sections?

We also have the added issue of how to show the Priority responses in the results table/pivot. i've added a section in the attached, but not sure how this would transfer to a pivot?

so questions:
1) is there a way to 'analyse' qualitative responses to surveys?
2) in a pivot table, how can we show the Priority areas which respondents have indicated with a Y (if no answer, they will leave blank). Does this need to be a separate pivot table?

Thank you so much again, really appreciate your assistance.

best

[JeteMc]

Please upload a file that manually includes a small sample of the output that you would ideally like for question #2, so that we can attempt to match it with a pivot table (or in some other manner).
There is a method of including text in the Values area of Pivot Tables, however it requires Power Pivot which may not be supported by your version of Excel. Your profile just shows 7 as the Microsoft Office version. Should we assume that means you are using the 2007 version of Excel? If not please update the profile as knowing what version you have may help us in selecting ways to accomplish your request.
Let us know if you have any questions.

[Kaycee831]

Hello again

thanks for the advice. I've added a tab which shows the preferred output in terms of the statistical data. I hope that explains it a bit better.

In terms of the version of excel, I'm so sorry, I hadn't realised I hadn't updated that in my profile.

I use my work laptop which has Microsoft® Excel® for Microsoft 365 MSO 64-bit and it states its version 2104 (if that helps).

It would be awesome if the text could be analysed in some manner.

thank you again

[JeteMc]

As you are using the 365 version of Excel, both Power Query and Power Pivot are available.
Power Query is used to convert the Excel table (I converted it from a range) on the Score & Priority sheet into the three green white tables on the Score Table, Priority Table and Score & Priority Table sheets.
Power Pivot is then used to make connections between the three new tables as well as to add measures to the table on the Score Table sheet.
The final pivot table is on the Score & Priority Table sheet.
Let us know if you have any questions.