How to Analyze Select-All-That-Apply Survey Questions in Excel ✅ [MS FORMS!]

[@SpotlightImpact]

Bundle Sale! Check out all of my data design templates and examples on Gumroad spotlightimpact.gumroad.com/l/ahkdm

[@007epsifa.a7]

seriously i watched umpteen vedio regarding this, none of the vedio couldnt not able to understand but your vedio is very helpful for me. thanks for this

[@SpotlightImpact]

Yay! I’m so glad it was helpful. I hope you’ll join, subscribe, or share 🙏🏻

[@CarolineMagee-i6z]

Thank you for this - really helpful and easy to follow.

[@SpotlightImpact]

Yay! I’m glad you found it helpful! I hope you’ll share and subscribe 🙏🏻

[@madslundberg6328]

Excellent. Just what I was desperately seraching for to analyse my 17-multi-choice column in Forms. Thanks a lot.

[@SpotlightImpact]

Yay! I’m so glad it helped! I hope you’ll subscribe and share with others! 🙏🏻

[@pathofclouds85]

Thank you so much! I looked everywhere and could not find a solution to this until I came across your video!!

[@SpotlightImpact]

Yay! I’m glad it worked. I hope you’ll subscribe and share. 🙏🏻

[@Kris10limon]

Oh my goodness I have been looking for the answer to this FOREVER! Thank you!

[@SpotlightImpact]

Woohoo! Me too!! 🤣 this has been a godsend a time or two for me! Glad you liked it, I hope you’ll subscribe and share! 🙏🏻

[@loisndungu4805]

Thank you! Your videos have helped me tremendously in analyzing this data set.

[@SpotlightImpact]

Yay! I’m so glad they have helped! ❤️❤️✅

[@MsAngulu]

Wow 🥹 very well explained. Thanks 🙏🏾

[@SpotlightImpact]

Yay glad you liked it! I hope you’ll share and subscribe 🙏🏻

[@nkabessa]

Very nice! Loved that transpose / copy / special... so easy

[@SpotlightImpact]

That so for watching I’m glad it was helpful! I hope you’ll subscribe and share! 🙏🏻

[@staceyc496]

This is life saving!! Thank you so much.

[@SpotlightImpact]

Yay! I’m so glad it’s helpful. Definitely a cool trick. I hope you’ll subscribe to my channel and share with others 🙏🏻

[@staceyc496]

@@SpotlightImpact Of course - subscribed immediately and will share it with my university lab mates. We do a lot of psychometrics in the educational field so this is very useful.

[@SpotlightImpact]

@@staceyc496 so cool! Thank you so much! I appreciate you! 🔥🙏🏻

[@katherineruark4401]

You are the best. Thank you!

[@SpotlightImpact]

Thanks for watching! Isn’t this trick a lifesaver?!? I hope you’ll subscribe and share with others - I’ll post other survey design/analysis videos in the future ❤️🙏🏻

[@ketop]

Thank you!!!!

[@SpotlightImpact]

You're most welcome! I hope you'll subscribe and share with others. 🙏

[@MrPKORC]

Thst is awesome. Thanks so much!🎉

[@SpotlightImpact]

Thanks for watching! I hope you’ll share and subscribe 🙏🏻

[@markmyers514]

thankyou excellent video

[@SpotlightImpact]

Thanks for watching. I hope you’ll subscribe and share 🙏🏻

[@nhatguy5543]

Thank You!!!!!! so much

[@SpotlightImpact]

You’re so welcome! I’m glad you watched. I hope you’ll subscribe and share with others 🙏🏻

[@BartomiejMatoga-mm3gn]

Fantastic video, thank you. And how would you deal with the "Other" option? If someone selects the "Other" option, the actual word "other" doesn't show up in the file.

[@SpotlightImpact]

I’ll have to check in forms but I think those comments will export in their own column separate from the cell with the rest of the options do. If that’s the case here’s a way you might work with those. kzbin.info/www/bejne/h3i3dZx9lqqnmpYsi=qtI68Sqej1sBlH2d

[@SpotlightImpact]

Hi Bartomie! I just tried it out and you are correct (I was wrong) - An other response in a multiple select q in Forms does simple produce the open text in the string of other responses in the same cell which is TERRIBLE lol, boo on you Forms! I'm going to think of a way to deal with this and post it in a viewer question video soon. Another option may be to reconsider the format of the survey question all together - for example many survey experts recommend not using select all that apply q's instead using a forced choice yes/no list for each of the items you'd include in the list. More soon!

[@BartomiejMatoga-mm3gn]

@@SpotlightImpact Thank you! I really appreciate ♥ I wonder whether the recommendation to use a list of yes/no question is only because of this issue here or in general - it is better from a methodological point of view? Can't wait to see the video :)

[@SpotlightImpact]

@@BartomiejMatoga-mm3gn Hi Bartomie! I made a video to answer this question, it's up for channel members now, and will be posted publicly on July 10th, so stay tuned! kzbin.info/www/bejne/bqqXi3aAm5ubZ5Y

[@tekaah]

Magic😀

[@ahmedabdulahimahamud3272]

Can you please do analysis of how Multiple response questions analyse with Region, gender any other question, Thank you

[@SpotlightImpact]

Will definitely do that! I’ll let you know when the video is posted.

[@SpotlightImpact]

Hi @ahmed I just posted a video on how I would analyze these kinds of data with other variables using the data filters and SUBTOTAL function. I hope this helps! kzbin.info/www/bejne/m6i2oXp4rruFbcU

[@ahmedabdulahimahamud3272]

It is awesome really, this support me

[@SpotlightImpact]

Thank you! I hope you’ll subscribe and share with others 🙏🏻

[@ahmedabdulahimahamud3272]

@@SpotlightImpact immediately subscribed

[@SpotlightImpact]

@@ahmedabdulahimahamud3272 awesome! Thank you! 🔥

[@gabriels6717]

Can you suggest a way to analyze option responses that include "Other" with a write-in box? They are exported as whatever the response to "Other" is.

[@SpotlightImpact]

Will add this to my list and create a video - I think we could do this 1) by transforming that column into a “1/0”, and then another pivot table for the text so you can see all of the responses as a list. More soon! 🤙🏻

[@SpotlightImpact]

Hi Gabriel! Here's one way I thought about working with this kind of data. It might not help 100% but could get you close! I hope it helps. kzbin.info/www/bejne/h3i3dZx9lqqnmpY

[@lizmartin3790]

Is there a way to set this up so it's a little more automated? For instance - I have a survey that is continuing to collect new responses and will be reanalyzing periodically. How can I streamline this so that I can just add the new data to the sheet without needing to re-do the later analysis steps?

[@SpotlightImpact]

That’s a bit trickier for select all that applies. In this case I think I’d probably use pivot tables - create one pivot table per column (item response). Then you can create a nicely formatted table you create pulling the data in from each pivot tables. I’ll make a video soon on how I’d do it. 👍🏻

[@SpotlightImpact]

Hi @Liz, just made this video on how I might think about doing this. Hope this helps! Thanks again for watching. I hope you'll subscribe and share with others. 🙏🙏 kzbin.info/www/bejne/bZnaZnSrnM50rZI

[@Olecram_Spartan_Gaming]

Great! But how do present that in a graph?

[@SpotlightImpact]

Lots of ways! Framed bar charts are my favorite! But a regular bar chart would work well too, or a lollipop chart … likely many other ways too! spotlightimpact.com/blog/f/framed-bar-charts-so-chic

[@SpotlightImpact]

Framed Bar Chart - kzbin.info/www/bejne/l3PCdnxpYs57rdUsi=GCFMRzZGMefnMPkX

[@SpotlightImpact]

Lollipop Chart - kzbin.info/www/bejne/rJS8qnqrr8mmpKssi=ix8o7qGucwsuiXjk

[@JohnDanenbarger]

The formula you have posted is wrong. It should be =countif($B2,"*"&C$1&"*"). Hope that helps.

[@SpotlightImpact]

thanks for that catch John! I updated it in my description. 🙏

[@shahanapeeroo991]

Nice' but how we will use this into spss?

[@SpotlightImpact]

If you’re using SPSS that’ll be a whole different bag. You’ll make each response option its own variable with 1 and 0 for the value. then under analyze you’ll select multiple response and add all of the new variables into that - then you can summarize them as a group.

[@SpotlightImpact]

www.ibm.com/docs/en/spss-statistics/saas

[@shofaacademic]

Thank you for your explanation but its not work for me and it said " there is problem with this formula .... and to type an apostrophe" what should i do?

[@SpotlightImpact]

If it asks you to type and apostrophe go ahead and try it. See if that works for you.

[@shofaacademic]

@@SpotlightImpact i tried but it didn’t work not sure if i put it in wrong location or not

[@SpotlightImpact]

@@shofaacademic hmmmm … I would go back and try to replicate the formula exactly - same cells and columns - step by step. Go Slowly and step by step. If you’d like send me the workbook you’re using I can take a look later today also. [e-mail]

[@shofaacademic]

@@SpotlightImpact Thank you for guidance, i will try it again slowly 🙏

[@EricAtchison0]

Terrible question / option set. You ask which they like "best" which implies a single choice should be selected.

[@SpotlightImpact]

You’re so right! thanks for your valuable insight. Good thing this was just a fake example, right Eric! 👍🏻