Analyze 'Other' Responses in Check-All-That-Apply Questions with Microsoft Forms 🔥 [VIEWER QUESTION]
⚡ Support my channel with a $1.99 membership (cancel anytime!) / @spotlightimpact
Thanks to @bartomiejMatoga-mm3gn for this question about how to deal with "other" responses from multiple select or "select all that apply" questions in Microsoft Forms. They certiainly don't make it easy for us to analyze these in a user friendly way, but with this series of Excel formulas with functions we can separate out the most important parts of the export to use in simple analysis. Thanks again for the question. Let me know how you use it comments below!
🧪 Excel Formulas We Used
Remove the trailing semi-colon from the cell "=LEFT(A1,LEN(A1)-1)"
Return the last phrase after the final semi-colon "=TRIM(RIGHT(SUBSTITUTE(A1, ";", REPT(" ", LEN(A1))), LEN(A1)))"
⌚ Timestamps
00:00 Ahoy + Here's the Question
01:13 How Microsoft Forms exports select all that apply data
02:40 Remove the final semicolon from the export
03:46 Return the last word after the final semi colon in the list
05:32 Find & Replace the words from each survey response option before "other"
07:55 Adding your re-worked data back into the Other column in your data table.
📣 Shoutouts
Analyze Select All that Apply in Forms • How to Analyze Check-All-That-Apply Survey...
One way to deal with "Other" response • How to analyze "Other" Write-In Survey Dat...
Segment Select All that Apply with Pivot Tables • How to Analyze Check-All-That-Apply Survey...
Make a Multi-Race Category from Select All that Apply Demographics • How to Add a Multi-racial Category to Chec...
💻 Template Downloads
Zip Code Lookup Template [Excel] https://spotlightimpact.gumroad.com/l...
Square Carousel Template [PowerPoint] https://spotlightimpact.gumroad.com/l...
10 Minute Timer Slide Template [PowerPoint] https://spotlightimpact.gumroad.com/l...
Net Promoter Slider Chart Template [PowerPoint] https://spotlightimpact.gumroad.com/l...
Waffle Chart Template [Excel] https://spotlightimpact.gumroad.com/l...
Data Placemat or Dashboard Template [PowerPoint] https://spotlightimpact.gumroad.com/l...
🙏 Support My Channel
SUBSCRIBE to this channel for more tips, tricks, and tutorials! / @spotlightimpact
JOIN Spotlight Super Fans for early access to weekly tutorials / @spotlightimpact
HIT the SUPER THANKS 💗 button on any video!
DOWNLOAD templates from on Gumroad https://spotlightimpact.gumroad.com/
DOWNLOAD templates from Payhip https://payhip.com/spotlightimpact
BUY stickers, I 💗 CHARTS! www.charthearts.com
🎒 Courses & Affiliates
Depict Data Studio Courses https://depictdatastudio.teachable.co...
Excel University https://affiliate.excel-university.co...
🙋 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
https://support.microsoft.com/en-us
Buying courses through affiliate links above costs no extra, but gives a small percentage of the proceeds to my channel.
#survey #excel #microsoftforms