How to convert Microsoft Forms multiple answers into binary columns in Excel?

Dear @Putri Ardhya Anindita

What you’re encountering is actually a known limitation of how Microsoft Forms exports multiple‑choice answers into Excel.

When responses are exported, all selected options are stored as a single comma‑separated text string (for example, “Beaker, Microscope, Scale”). If you use “Split Column by delimiter” into columns, Excel/Power Query only creates columns based on values found in each individual row, so it won’t produce a consistent set of equipment columns across the entire dataset.​‌

Then, you can use “Pivot Column” on the equipment field. By using the helper column as the value and setting aggregation to Sum, Power Query will automatically create one column per equipment type across the full dataset, and you can replace nulls with 0 to complete the binary structure. For example:

Step 1: When you use Open in Excel from Microsoft Forms, the responses are exported into a worksheet for analysis.

Step 2: Load data into Power Query by select your table > Data > From Table/Range.

For example, here is raw data:

Step 3: Split into rows (critical step)

Select the Equipment column > Go to Split Column > By Delimiter > Delimiter: comma > Advanced options: Split into Rows > Now each choice becomes its own row.

Step 4: Clean the values

Because Forms exports "Beaker, Microscope" with spaces > go to Transform > Format > Trim

Step 5: Add a binary indicator

Go to Add Column > Custom Column > Name: Used > Formula: = 1

Step 6: Pivot to create columns

Select the Equipment column > Go to Transform > Pivot Column > Settings: Values column = Used, Aggregation = Sum. This converts values into separate columns (Beaker, Microscope, Scale)

Then, select all new equipment columns > Transform > Replace Values > Replace: null > With: 0

Finally, choose "close & load"

Moreover, I completely understand your expectation, ideally Microsoft Forms or the Excel export should offer this structure natively without requiring transformation steps.

I would strongly recommend submitting this as feedback to Microsoft so the product team can consider improving the export format. To ensure your feedback reaches the right teams, I highly recommend submitting your feedback directly to Microsoft Ideas · Community, where our product development team can know your requirements and ideas for product improvements. If enough users raise ticket for the same idea, Microsoft may consider adding this feature in the future. 

Note: As Microsoft Community moderators, please note that we are not involved in product design decisions and do not have direct access to the development roadmap, we want to assure you that your feedback has been acknowledged and is valued.  

I hope the information shared is helpful. Thank you for your patience and understanding. I'm looking forward to your update.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.