ICAEW.com works better with JavaScript enabled.
Exclusive

Microsoft Forms: Analysis automation on surveys created in minutes

Author: David Benaim

Published: 14 Oct 2021

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.

Whether its employee feedback, customer satisfaction, post meeting surveys, Forms are very handy. I’ve created more Forms than Word docs for months now, although that’s partially because I think Word has mostly become obsolete.

For anything people typically PDF, I use Sway and OneNote to replace tasks which are sometimes done on Word. I have also seen many a questionnaire done on Word, where one word file is completed for every survey response, then manually input into an Excel spreadsheet.

Auto-analysed results

After you create a form and collect responses, Forms analyses them for you automatically. You can see also see individual responses, or export to Excel for further analysis.

Screenshot from Microsoft forms

Creating a Form

Office 365 customers (of any package) can navigate to Forms via the app launcher. Note this is also available for anyone with a free Microsoft account in the same way.

Screenshot from Microsoft forms

Next, click “New Form”, the form editing capabilities is very similar to its Google Forms predecessor. Here is a list of the questions you can create.

Screenshot from Microsoft forms

1. Choice allows you pick multi or single select, you can show it as either a choice or dropdown, add entries or a free type “Other” field, and create branching .

Screenshot from Microsoft forms

Responses to choice questions are analysed in column charts (if 6 or more options exist) or pie charts (for 5 or fewer), even impressively with multi select responses (a process that is near impossible in native Excel unless you use Power Query). Pro tip: When you have more than 10 options, you will need to zoom out of your browser screen to be able to see all replies. It would be good if replies could appear on multiple columns but it's not possible at the moment.

Screenshot from Microsoft forms

Long lists can be hard to manage, this video has useful tips:

2. Text questions can have a short (one line) or long answer (multiple lines), plus you can force the user to type a number. Since a recent update, replies are shown in a Word cloud.

Screenshot from Microsoft forms

3. Rating can either be a star rating or just ask for a number. You can also add a label for what each number represents. Responses are usually averaged.

Screenshot from Microsoft forms

4. Date asks for a date in the US format month – day – year but gives a day picker.

5. Ranking allows the user to priortise by dragging & dropping phrases in order, such as this:

Screenshot from Microsoft forms

Responses are ranked in a complex way that would be quite difficult with native Excel

Screenshot from Microsoft forms

6. Likert questions are where text replaces rankings, e.g. “Fully satisfied, Partially satisfied, Neutral, Unsatisfied, Highly unsatisfied”. You can create a grid with multiple questions and the Likert scale.

Screenshot from Microsoft forms

Analysis is shown like so:

Screenshot from Microsoft forms

This video showcases how Likert scales work in Forms and how Excel and Power Query can analyse them in an advanced way.

7. File upload: Ask users to upload a file and specify which type (e.g. image file only), but this is unavailable if people outside your organisation are completing the form. The files are saved in a new OneDrive/SharePoint folder with the name of the quiz & question.

8. Net promoter score: On the face of it NPS seems like a 1-10 rating question but the difference is in how the score is calculated. Replies are categorized into one of three groups in this commonly used business assessment tool, where the final scores ranges from -100 to +100.

Screenshot from Microsoft forms
Screenshot from Microsoft forms

Question settings

For each question type you can:

  • Make it required
  • Add a description field
  • Add some formatting, bold, italics, colour text (a fairly recent addition to the software)
  • Rearrange questions
  • Copy questions from the same form, sadly not possible from other forms at the time of writing

Note: When you start a new document, you can choose a new Quiz instead of a Form, the main difference being that you may choose a correct answer from the questions you ask, which is mainly useful for multiple choice questions. The person doing the quiz will then see their score autogenerated after they complete the quiz.

Branching & sections

One major area where Microsoft Forms outshines Google’s is branching. Depending on which answer is chosen in a multiple-choice single select question, the user will get different questions or skip to the end, here is an illustrative example:

Screenshot from Microsoft forms
To access branching (sometimes referred to as conditional logic or skip logic), click the 3 dots on the bottom right of a choice question and choose “branching”.

You may also add sections to the Form, so users can progress through longer forms in an organised fashion, and branching can be applied to the sections as well. E.g. Auditors fill out section 2, consultants fill out section 3. Google Forms is only capable of branching only if sections are applied, so you may end up with entire sections which just have one question.

More tailored form software such as Survey monkey has more advanced forms of conditional logic though.

Form-wide settings

These settings are possible:

  • Copy an entire form from the Forms home page
  • Add a Form overall name & description
  • Change the theming to an individual colour, a preset design or upload your own
  • Preview the form to see how it looks on mobile or computer, note this the easiest way
  • Via the 3 dot menu on the right:
    • Add translations for multi-language support
    • Print blank forms
    • Edit who can reply in the settings
    • Turn off response acceptance or set a start/end date
    • Customise the message after the form is completed
    • Turn on email notifications for form submissions to the owner of the form
    • Give the responder their response in an email, at this time it isn’t possible to allow responders to change their response later on

Share options

After you’ve created your form, you should share so people can respond. Click “Share” on the top right and copy the uppermost link. You have options to shorten the url, create a QR code, embed code or an email invitation. The middle link is to create a template of the same form and the last one is to collaborate so others (inside or outside your org) can add/amend questions and see Form responses.
Screenshot from Microsoft forms

You may choose whether responses can be done by anyone anywhere, anyone inside your organisation or specific people inside your organisation.

The confusing part: Linking to Excel

From the “Responses” part you can export to Excel, but the way this happens may differ

Google Forms only ever has the one method which is the latter option, but in Microsoft Forms, you get the latter option if you click either:

  • Create a “New Group Form” from an Office 365 Group or
  • Start in Excel Online, click Insert tab then Forms
Screenshot from Microsoft forms

If you start a Form from the most intuitive method though which I describe above), it creates a one-time download option only at the time of writing, which I personally find confusing, but do bear in mind if you need a Form which constantly updates an Excel file.

This video describes how Forms works as whole and linking to Excel explained in this section:

Organising forms

The default personal Forms are created in your personal account but you can alternatively create Forms inside Microsoft 365 groups (these come with an accompanying Excel file as explained above). From Forms, scroll to the group forms at the bottom of the page rather than clicking new form and then create a “New Group Form”. Group forms cannot be moved to personal forms and vice versa, and are hard to find in searches an area which I personally find confusing.

Screenshot from Microsoft forms

You can search through previously made/edited forms by typing search terms into the filter, but the default search scope only includes personal forms which you authored, you need to scroll to other sections to search for forms in those sections.

From October 2021 Microsoft allows you to create collections of Forms, which is essentially folders. To add a form to a collection, click the 3 dots on the Form itself.

Screenshot from Microsoft forms

Excel or Forms for surveys?

My general advice is when you have a lot of entries use Excel, Excel gives people the benefits of being able to drag down an entire column and do multiple entries in one go. If you are going to use Excel though, make sure you apply data validation for data collection to make fewer errors.

Microsoft Forms is best for sending a survey out that other people complete, it's also better for multiselect choice questions which are very difficult to do in Excel, branching is another area that I prefer doing in forms to Excel. Give Forms a try you won't believe how easy it is.

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel