Gathering data can be a chore.
Imagine surveying everyone in an office as to where an office party should be held and what food should be served.
If an email blast to “all staff” was used to solicit feedback, gather, compiling, and analyzing the results can become overwhelming.
Suppose you could send a link to “all staff” that, when clicked, presents an online form with features such as dropdown lists, checkboxes, date pickers, and text fields. The best part is that the results are automatically collected in an Excel spreadsheet that can be used to sort, filter, chart, pivot, or any other amazing Excel machinations you care to run the data through.
Welcome to Microsoft Forms.
With Forms, we can control all aspects of the data collection process, from distribution, access, response methodology, data collection, and analysis. All this without ANY VBA, programming, or add-ons. All that is required is an Office 365 account.
Let’s see how easy this can be while at the same time promising we will never send out another “all staff” questionnaire.
Starting a New Form
The first step in creating a Microsoft Form is to open a Web browser and navigate to office.com.
Once logged in, click the App Launcher (upper-left corner) to reveal a list of authorized apps. You can start the form creation process by selecting either Forms or Excel from the list of apps.
Which one you select depends on how you answer the following questions:
- Do you have a FREE or a PAID Office 365 account?
If you have a FREE account, select Forms. If you have a PAID account, you now must answer another question.
- Do you want an automatic connection between Excel and your Forms result (results are sent directly to an Excel file stored on OneDrive), or do you wish to download the Forms results on-demand when needed?
You will likely take advantage of the automatic connection to Excel feature, so start by clicking the Excel app. Otherwise, if on-demand data exports are to your liking, select Forms.
NOTE: If you choose the Excel option, you will need to manage your data with the online version of Excel. Data can be opened in the installed version of Excel if needed.
For this example, we will create the form using Excel online. Most of the process is identical to starting with Forms.
Starting with a blank workbook in Excel (online), save the file with a name of your choosing, then start a new form by selecting Insert (tab) -> Tables (group) -> Forms -> New Form.
NOTE: It’s important to save the file before creating the form as this tells Forms where to save the results of user input.
If you are creating a new form from the Forms app, click the New Form button at the top of the app to start a new form.
Adding Questions to a Form
To add a question to the form, click the Add New button.
Forms is equipped with several data collection options, such as multiple-choice, fill in the blank, ratings, file uploads, etc.
If you select File Upload, a folder will be created on your SharePoint drive where participants can upload files.
Let’s look at a few of the collection options in greater detail.
Ratings are a great way to gauge satisfaction or dissatisfaction. Providing a simple star rating system or a numeric ranking system is quick for the respondent and easy to analyze by the surveyor.
Features that can be configured include:
- What question is being asked of the viewer
- Rate using stars or numbers
- How many stars/numbers (2 to 10)
- Is the question required or optional
- What do the lowest and highest stars represent
The result when presented appears as follows.
When writing the question, certain topics may automatically provide a set of suggested options by which to place in your choices list.
Clicking an offered item will place it in the list of choices. You can also click the Add Option button to customized choices. An Add “Other” Option button can be selected to provide a “catch-all” at the end of your choices list to accommodate any unpredicted choices.
The responses can be randomized for each respondent or remain in a fixed order.
We can also allow the user to select multiple answers or limit them to only one answer.
Option – Dropdown List
Suppose you have a question that has dozens or even hundreds of possible responses. Listing all the options would require extensive scrolling by the viewer.
When only one item can be selected from an options list, it is more conservative from a screen real estate perspective to provide the viewer with a scrollable dropdown list.
PRO TIP: If you have many options to list, and the list exists in an Excel file, perform the following steps to “import” the options into the response list:
- Select the existing list of options.
- Copy the selected list to memory.
- Switch to Forms.
- Select the Option 1
- Click Paste.
All the copied options are now listed as selectable responses.
To convert the question from a displayed list to a dropdown, click the Options button (lower-right) and select Drop-down.
NOTE: Drop-down is not allowed with multiple response questions.
Allowing the respondents to type a date can be a formatting and regional nightmare. With many different regions and many ways people express dates, aggregating and analyzing the dates can become quite a chore.
By supplying the respondent with a Date Picker tool, we can expedite data entry while at the same time standardizing the format of the stored data.
A text response allows the respondent to type anything they like.
The Text option can be limited to a “short answer” of 4,000 characters. The Long Answer option allows unlimited characters in the answer field.
NOTE: When using the “short answer” option, the entry box does not expand to accommodate the additional text, whereas the Long Answer option will display the entire text entered by expanding the entry box as needed.
You can restrict the response to a number that meets a specific requirement, ex: a number greater than 10.
Likert Scales are used most often in research that employs questionnaires.
When we add the question, we are often offered suggestions on how the rating system should be labeled. We can accept the defaults or create our own labels.
Once labeled, we add the items being ranked.
Net Promoter Score
Net Promoter Scores measures customer experience and predicts business growth.
This proven metric transformed the business world and now provides the core measurement for customer experience management programs the world round.
Previewing the Form
When you would like to see how the form is presented to the respondent as well as test the accuracy of the questions and mechanics, click Preview in the upper-right corner of the window.
You can preview the form as it will appear on a mobile device or a large screen (i.e. PC).
You can return to the development mode of Forms by selecting the Back link in the top-left of the form.
Share the Form with Others
To share the form with others, click the Share button in the upper-right corner of the window.
You are presented with the option of sharing the form with others in your organization, or with anyone on the Web provided they have the supplied link.
We can have the respondents access the survey by one of the following methods:
- QR Code
- Embed code
Saving the survey link as a QR code is a great way to incorporate the survey into a PowerPoint presentation or have printed on a flyer. This can be scanned by a smartphone so the user can take the survey even if they are not at their computer.
Controlling Response Behavior & Notifications
Selecting the Form Settings (upper-right 3-dots button) displays controls for the following settings:
- Scope of respondents; if the respondents are within your organization, are their names recorded, and can they respond multiple times.
- Accept responses or halt response collection
- Define a date to start collecting responses
- Define a date to end response collection
- Shuffle the survey questions
- Display a progress bar
- Send an email notification to the respondent once the survey is complete
- Send an email response to the form author after each submission
Viewing the Survey Responses
To view the survey responses, select the Responses tab at the top of the Forms window.
A summary of the response activity is presented along with the option to view each submission separately or download all responses to an Excel worksheet.
The summary provides rather nice visualizations regarding the different response types.
Addition options when viewing the summary statistics include:
- Deleting all responses
- Print the summary
- Create a link to the summary page for viewing by others
Viewing the Responses in Excel
You can elect to download all responses to an Excel worksheet for a more personalized analysis.
From here, you can sort, filter, slice, pivot, and chart to your heart’s content.
NOTE: If you created the form directly from the Forms app instead of the Forms feature in Excel Online, the data will not be stored in the Cloud. You will be given the option to download the survey results as an Excel file for offline analysis.
The Forms app has other features that deserve exploration, such as conditional branching to allow for certain questions to be asked only when other questions are responded to in a certain way.
Forms also has multilingual support. Granted, you will need to manually supply the alternate language versions of all survey questions, but these translations can be easily performed in Microsoft Word using the Translation feature.
Learn Excel from a Microsoft MVP
Check out my bestselling Excel Courses
Learn anytime that fits your schedule.
Download files. Practice. Apply.