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

[@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

[@boblynnamanyire2274]

I've watched this video over and over again and it's surely helpful.

[@MSurveyPoint]

@@boblynnamanyire2274 Happy to know this is helpful. Thanks for the feedback

[@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 KZbin

[@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

[@HoneyBadgerJAB]

Thank you very much!

[@VideoChick666]

So helpful thank you

[@MSurveyPoint]

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

[@andybromley383]

Life saver!

[@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....)

[@joyhardy2467]

Hi, Thank you so much for this video. I have a multiple choice question in which most responses are blank for every option. This results in the grand total being less than the total number of survey respondents Is there a way to get the % of the total number of survey respondents without manually adding a formula? I am using MS Excel 2016.

[@MSurveyPoint]

@@joyhardy2467 Do you get the reduced total after you filtered the Blank label in the rows of the pivot table? If you don't have Blank label (or if had not filtered it out at all) but the total is still less than expected: Do this 1. Go back to power query 2. Before you Unpivot; 3. And an index column (starting from 1....). Let's name it Dummy 4. When selecting columns to Unpivot, add the Dummy column too 5. Then Unpivot . However, this will result in many values when you do crostab. 6. So you need to filter out the dummy label in the rows of your pivot table. Alternative to the long list of values in the dummy column: 1. Add a conditional column: Name Dummy column 2. Condition: If ID is not equal to 0 return 1 else return 0 3. That way will will only have 0 or 1 in your dummy column. Let me know if this helps If this doesn't help, you can send me an email to connect and understand the issue better

[@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%.

[@MSurveyPoint]

@marylusenie689 Find the explanation in this video. You may want to check it out kzbin.info/www/bejne/g6PPd6OZjLWpjMU

[@andrewtirtawijaya5627]

Hi bro, thanks for the video. One question, what if i have couple of question with multiple answer (3 question with multiple answer) what is the best way so pivot table can work just fine? Thanks.

[@TruthofDilly]

Thank you this is GREAT 😊

[@MSurveyPoint]

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

[@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?

[@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.

[@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. kzbin.info/aero/PLoR189xZ0hCNyjLSap6oSmJH51VRnS0c8&si=_v2w8w8PtBq7M8zh

[@boblynnamanyire2274]

Hello Msurveypoint. using the same example, I want to see the analysis for how many own only, how many own at least 2, how many own 4 and how many own 5 types of gadgets.

[@MSurveyPoint]

@boblynnamanyire2274 Check this recent video on this: kzbin.info/www/bejne/a4OyoJaNocd6j9k

[@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.

[@Dante_danielz]

Thank you bro,

[@MSurveyPoint]

Glade this helps. Kindly subscribe to support me

[@Dante_danielz]

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

[@MSurveyPoint]

​@@Dante_danielz Sure!