Microsoft Forms multiple choice surveys: How to easily convert and visualise results

To analyse the results of your MS Forms (basic version) survey you have two options: Either you access the visuals under the “Results” tab. Which can be useful and even nice. Or you can export the results to Excel for further analysis and customized visualisation. The issue is however that the exported data are presented in unstructured form. This short tutorial will help you to structure the exported data for analytics and visualisation.

The Survey

Imagine you are a cantine manager and want to know people’s preferences, asking them to choose 2 options:

The challenge

Now, your manager asks you to visualize the result.

The exported output in Excel for 10 answers doesn’t look like it’s easy to get a bar chart out of it:

How should we tackle the task?

The solution

The following 4 steps lead us to delivery of the task:

1. Add the options as columns to the result sheet

We add the multiple choice options either manually or from an existing Excel sheet by transposing. Do not fear, transposing is easy in Excel.

(Tip: Leave out one column between the result data and the options, otherwise the option columns integrate to formatted table, which messes up with the formula in step 2.)

First approach: Add the options manually.

Second approach: Copy/paste existing options. If in a column, the entries can be easily transposed by using paste & transpose option.

2. Convert answers to 0/1 values

In the first row of the new columns, enter the following formula (adapt the field references/coordinates if needed):

=IF(ISNUMBER(SEARCH(F$1,$D2)) = TRUE,1,0)

(This formula will (i) run a search whether the column title string is contained within the value in the “Selection” column, (ii) identify whether the result of the search has a value i.e. there is a match, or no match(in which case #VALUE! is returned), and then (iii) assign 1 if there is a value, or 0 if there is no match.)

Then, cast the formula to the other cells by dragging the fill handle.

3. Create a new line under column titles and fill in the sums

This should not be a huge problem even if you have basic Excel skills. I add an instruction video here all the same.

4. Create the chart

Creating the chart is simple. The only issue is that the data for the chart is in the second row. So, if we want to sort it to present it in a sorted manner in the chart we have to apply the trick of selecting the “from left to right” option: Data>Sort>Option>“from left to right”.

This is demonstrated in this video:

Et voilà!

Conclusion

Creating a survey in basic MS Forms version is simple. Structuring the results requires some advanced skills. I hope that this little tutorial will be useful.