How to Analyze Columns-separated Multiple Response Survey Data in Excel Pivot Table

This video you will walk you through how to analyze multiple choice survey data where the response appear in separate columns.
BONUS: Learn how to divide the count of the responses by the Number of Respondents for that question inside a pivot table.
Check out this previous videos on multiple response survey data
1. How to analyze comma-separated multiple response survey data:
• How to analyze comma-s...
2. How to analyze space-separated multiple response survey data:
• How to Analyze Space-s...

[@MSurveyPoint]

Connect with me if you need support on the following services
Email: [e-mail]
Book appointment here 👉 calendar.app.google/MMasAbZPDuwCCux76
1. Survey questionnaire design
2. Online survey scripting
3. Survey data Analysis
4. Research report writing
5. Data analysis in Excel
6. Survey Dashboard in Excel
7. Survey Dashboard in Power Bi
The scope of my services includes but not limited to the areas of Market Research, Customer Experience Research, Product Research, User Experience Research, Employee Engagement Surveys, NPS, CSAT & Voice of the customer, Monitoring & Evaluation, etc.

[@wiszewska890]

Thank you, random Indian guy. You saved my life. Again.

[@MSurveyPoint]

Glade to hear you found it helpful.

[@judahokonkwo3866]

He sounds Ghanian

[@NicoleNotarioRisk]

I've come back to your video several times to help me understand something I'm working on. Thanks so much!

[@MSurveyPoint]

Thanks for the feedback.
I am glad you found it helpful.
I will continue to share more relevant videos.
Best regards

[@chloeludden2398]

Thank you so much you really made my day you know. All the best from London ! Such a game changer to have knowledgeable people like you on RUclips

[@MSurveyPoint]

Hi Chloe Ludden
This is satisfying to hear. Glad that it helped. Kindly subscribe for more interesting topics.

[@abdul-wahabinusah3340]

Thanks. So much appreciated

[@ahmedabdulahimahamud3272]

I am always with your videos nice explanation.

[@icogaoke]

thanks man. you're my sunshine

[@andybromley383]

Life saver!

[@TruthofDilly]

Thank you this is GREAT 😊

[@MSurveyPoint]

You're so welcome! We are happy this is helpful. More coming up in subsequent videos

[@VideoChick666]

So helpful thank you

[@MSurveyPoint]

Thanks for the feedback.
Glad to hear that helped.
Subscribe to stay updated

[@soni_b535]

Thank your for your helpful video but for some reason my ID column isnt showing on the pivot chart? What did i do wrong?

[@MSurveyPoint]

Can you check if you did have a column named ID in your survey data?
The ID column can be any column that column with entries that uniquely identifies each respondent or data entry in your data like telephone number, student Id number, survey submission Id, etc.
You can use any similar column in your data that uniquely identifies each respondent.
If not captured, you can add this manually in excel (insert column in your data table and number each row from 1, 2, ...n) or in power query using the Index feature from 1,... (Add column>>Index column....)

[@SP-gf2ti]

Thank you for this video! It is very helpful. Do you know what to do if the Count of Id and Distinct Count of Id are listed as the same values in tge pivot table, even while the grand totals are different? Any idea why this would happen?

[@MSurveyPoint]

That is the normal case and nothing has really gone wrong. The counts you get in the Pivot table for each case are the number of responses selected for each of the response items.
Both counts the number of IDs that matches each response item in the cross-tabulation . However, COUNT sums this number of responses to give the grand total. Distinct count on the other hand Counts the number of Unique IDs to give the Grand total.
So the number of responses wouldn't change in each case but the grand total will.
We summarize by Distinct count to get the grand total to be equal to the number of respondents. In that case the percentages we get can be interpreted with reference to the sample or sub-sample.

[@ahmedabdulahimahamud3272]

Thank you bro, can you please analyze with other variable such District, location or Male and Female

[@MSurveyPoint]

Thank you @Ahmed
Sure! This comes next after this series.

[@danielodedeotieno1393]

Thank you bro,

[@MSurveyPoint]

Glade this helps. Kindly subscribe to support me

[@danielodedeotieno1393]

@@MSurveyPoint done. Hope more videos are coming up soon?

[@MSurveyPoint]

​@@danielodedeotieno1393 Sure!

[@abdoulayebadji2205]

Thanks so much. How can I export multiple response variable in single column with comma (,) separated responses

[@MSurveyPoint]

This will largely depend on the survey platform you are using and the features available on multiple response questions. Which survey platform do you want to do this?

[@marylusenie689]

thanks so much. but I am just curious to know this. When you add the percentage of the distinct count of ID, it is more than 100%. Why is that?

[@MSurveyPoint]

Happy you found it helpful. In multiple response questions, survey participants are given the option to make more than one selection (if applicable - and some will very likely have more than response).
So the number of responses (video 74) will be >= no. of survey participants (video 50). In most MR surveys the former is greater than the later.
So when you take %s out of the number of respondents or survey participants who answered the question, the sum of the %s will be >= 100%.

[@samanthagaulin1921]

Is there a way to use a slicer with this method?

[@MSurveyPoint]

Yes, you can apply slicers to the analysis table.
Click anywhere in the table:
Under the Pivotable Analyze table at the top....
Select Insert Slicer...
Select the Unpivoted Data table....
Check the respective variable (like age group, gender, region, etc) for your Slicer and Okay.
If you have other pivot tables created from your main Data Table in the model...
Insert the Slicer from that...
Then establish relationship or connect that table to the Unpivoted Data Table using the ID column.

[@anaeducruz4133]

Can’t find the distinct count on my excel any alternatives?

[@MSurveyPoint]

Okay. The feature is enabled only if you add your data to a data model and ✅ add this to the data model in the dialog box that comes when you click on "close and load"
There is a chance you skipped that. Could you reload your data and check that let's see?

[@emmamichile2503]

How do I analyse data for multiple response answers. e.g. where a person is allowed more than one option. if a question allows a person to select that they use Phone, Tablet and TV, and others Phone and Tablet.

[@MSurveyPoint]

Hi Michile,
Thanks for the feedback.
This is exactly what this video covers but only for the case when the multiple selection data come in multiple columns and each selection made appear as labels in the columns. Eg. TV, ...etc.
For other data formats in this case, please check the other videos in this playlist.
The difference is how the multiple response questions appear when you download your data from the survey platform.
So the first step is to review your data and see how the multiple response questions appear in your data export.

[@MSurveyPoint]

Please check the playlist for multiple response questions. You will find the video that covers the data format you have.
ruclips.net/p/PLoR189xZ0hCNyjLSap6oSmJH51VRnS0c8&si=_v2w8w8PtBq7M8zh