A new Forms feature rolling out this fall in Excel for Office 365 customers enables this functionality. (People without Office 365 can create similar functionality at https://forms.office.com, but they’ll have to take the extra step of clicking “Download to Excel.”)
Look for the Forms icon in the Insert tab in the ribbon. It will be the fourth icon from the left, just to the right of Recommended Pivot Tables and Table. Click on the icon and create a new form. Excel will ask you to name the workbook. This will be the file name of the workbook on your OneDrive folder as well as the name of the survey, so choose carefully.
The tool has a simple interface. At first glance, the only option on the opening screen is Add Question, but many subtle controls hide in plain sight. For example, when you click on the title of your survey, new fields appear that let you upload a logo and enter a survey description in the subtitle.
As you add a question, controls are visible that let you make it a required field, allow multiple answers, and add “Other” as a possible response. If you click the ellipsis icon at the bottom right, you can access more options such as Shuffle Answers randomly, show the answers in a drop-down menu, and add a subtitle for the question. If you allow for an “Other” answer option, then your survey takers will be able to enter text in their answer.
Each time you click Add Question, you can choose from Choice, Text, Rating, or Date. The ellipsis icon offers two additional question types: Ranking and Likert. The Likert scale allows people to indicate the extent to which they agree (or disagree) with a series of statements. As you type a question that begins with “How often,” Excel offers answer choices such as Daily, Weekly, Monthly, Seasonally, Yearly, and Never. You have the option to include some or all of those, or you can write your own categories.
When a question includes an answer option where the respondent enters text, you can click the ellipsis icon to add parameters to the text field. In the Restrictions drop-down menu, you can specify that the answer has to be a number in between an upper and lower limit.
This question type is one of the disappointing features of the Forms tool. Even if you specify that the question has to be a number, Excel saves the answers as text. Someone might enter “7” as their answer, but the column in which that answer will be stored will have a text format rather than a number format. This means that pivot tables and functions such as =AVERAGE() won’t work until you transform the column to numbers. It’s annoying that you can’t force Excel to store the answer in number format. I hope that Microsoft corrects this shortcoming soon.
In the top-right corner of the screen, there are three tabs: Preview, Theme, and Share. The Theme tab lets you set a color theme, add a whimsical cartoon, or upload your own images to be used as the background for the survey. The background only appears when the survey is completed on a computer. When the survey is taken on a mobile device, the screen isn’t wide enough for the theme.
The Preview tab offers the ability to preview how the survey will look on a computer or a mobile device. After looking at both, use the Back link (just above Preview) to continue editing the survey. Note that you want to click the word Back in the web page, not the browser’s Back button.
The Share tab offers several different ways to distribute the survey. By default, surveys are only available to others in your organization. If you leave this default setting, then people will have to sign in to their Office 365 account. The answers to the survey questions won’t be anonymous—for each row in Excel, you’ll see the respondent’s name, email address, time of completion, time to complete, and answers.
You can instead opt to open up your survey so that anyone with the link can take it. With the link to the survey, such as https://mrx.cl/sfsurveysep18, you can generate a 2-D barcode (as shown below) to embed in a web page or email message. You can even place the 2-D barcode on a PowerPoint slide or on a printed page, and people can access the survey by pointing their cell phone camera at the screen and using an app to scan the code.
The responses to the survey will be summarized on the Responses tab of the Forms tool and delivered in real time to your Excel workbook.
SF SAYS
The Forms tool also can be used to create quizzes with points assigned to each question.
September 2018
