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

One problem I've had with Microsoft Forms (and Google Forms) is in how it exports 'select all that apply' survey question data to Excel. Forms exports all responses into a single cell and you really want each response to be in it's own cell and column for easy analysis. In this step by step tutorial you'll learn how to use this clever Excel formula (courtesy of @MrXL ) to transform that data export to a more useful format for analysis and summarizing your survey data.

Here's the formula from the video:
=countif($B2,"*"&C$1&"*")

Copy the formula here, drag it over and down your cells and VOILA! 🌟 Your survey data analysis will be a breeze with this little trick! Stay tuned to the end to see my favorite data table trick....Paste Special-Transpose

⌚TIMELINE
00:00 Ahoy
04:45 The magic formula to un-concetenate your select-all-that apply data

📣 SHOUTOUTS

💻 DOWNLOADS FOR PURCHASE

🙏 SUPPORT MY CHANNEL
Hit the THANKS button in any video!

🎒 COURSES & TRAINING AFFILIATES

🙋FAQs
I’m using Microsoft 365 in my videos: PowerPoint, Excel, Word. If you don't have a feature you see here, check the Microsoft support pages for a version feature list
Buying courses through affiliate links above costs no extra, but gives a small percentage of the proceeds to my channel.

#excel #survey #microsoftforms

[epsifa.a]

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

[Krislimon]

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

[madslundberg]

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

[pathofclouds]

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

[CarolineMagee-iz]

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

[loisndungu]

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

[nkabessa]

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

[staceyc]

This is life saving!! Thank you so much.

[ahmedabdulahimahamud]

It is awesome really, this support me

[gabriels]

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.

[lizmartin]

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?

[shahanapeeroo]

Nice' but how we will use this into spss?

[ahmedabdulahimahamud]

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

[Olecram_Spartan_Gaming]

Great! But how do present that in a graph?

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

[JohnDanenbarger]

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

[Eric.Atchison]

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