Building an Excel Form or Survey

By Terry McCamish, Accountability Data Analyst, Technical College System of Georgia 

Often, as institutional researchers, we need to gather data from users. A simple and quick way to do this is by using Excel Online. A free Microsoft account gives you access to the online versions of Word, Excel, PowerPoint, OneNote, OneDrive, Mail, People, Calendar, Sway, and Docs.com. The free account comes with 5GB of storage online. I have had my account for years and have not reached the storage limit yet. 

The free version enables you to create forms and surveys that can then be shared by creating a link for the user. Since your account is maintained in the cloud, the default file format is Excel 2013. Older versions will need to be edited on your computer and uploaded where they will be converted to .xlsx. If your institution uses Office 365, Microsoft has created a separate form tab. Of course, by using a cloud solution you have access to all your forms and surveys through any web browser.

Using Excel Online also saves your document in a format that can be easily downloaded to your desktop version of Excel. An add-in for Excel 2016 users allows you to sync your online forms with Excel 2016 on the Desktop. This feature is available with your Office 365 account. The free version online will allow you to download files to Excel.  

As with any free cloud service, be sure to check with your IT department about using it. Also, you should be aware of the service provider’s privacy policies.

1.PNG

Figure 1. Excel Online

2.PNG
Figure 2. The Excel Online Survey Button

On the Excel Online Insert Tab, click the Survey button. The initial selection available is New Survey.

a3.PNG
Figure 3. The Form Dialog Box

Click the New Survey button to bring up the Survey Dialog Box. Here, you can enter a title and a description. Click the “Enter your first question here” to bring up the “EDIT QUESTION” dialog box.

4.PNG
Figure 4. The EDIT QUESTION Dialog Box

Your options on this page are for the Question text, a Question Subtitle, a Response Type, a Required checkbox, and a Default Answer. This is also where you would delete the question. The Response Type choices are Text, Paragraph Text, Number, Date, Time, Yes/No, and Choice.

Response type examples:

  • Text is a freeform textbox and allows one line of text.

  • Paragraph Text is a multiline, freeform text box.

  • Number response type stores a number. Available formats are Fixed Decimal, Percent, and Currency.

  • Date limits the entry to the format: mm/dd/yyyy.

  • The Time format limits entries to the format: H:MM, AM, or PM.

  • Yes/No creates a dropdown list with Yes or No as choices.

  • The Choice response allows the user to create a list of responses that populate a dropdown list.

Missing is a response type that allows multiple choices.

View Survey

This is what others will see when they click on your survey link:

5.PNG

Figure 5. The Form Preview Page

Once you finish entering questions, you can click the Save and View button and see how the form will look to the user. If it is acceptable to you, you can share the form directly from here by clicking the Share Survey button.

Excel Online will create a link you can then post on a website or send by email.  A Microsoft account is not necessary to respond to the survey - just click the link.

6.PNG
Figure 6. Your completed survey as it will appear to the respondent

Responses are collected in Excel Online in a spreadsheet in your Microsoft online account. Need to make changes? Go to the Insert tab on your spreadsheet and click the Survey button. There are four options: View Survey, Edit Survey, Delete Survey, and Share Survey.  By default, the survey and spreadsheet are saved in your Microsoft OneDrive account online. The survey and the spreadsheet are maintained as separate files but are linked together (changes in the survey are automatically updated in the spreadsheet).

7.PNG
Figure 7. Response Collection Worksheet in Excel Online

Sometimes a full-blown survey or form is excessive. In those cases, Excel Online provides a quick and easy way to collect data.

 

 

 AIR Comment Board

 
To add a comment, Sign In
Total Comments: 1
 
Teresa posted on 9/21/2017 3:43 PM
Just a note, the newest version of O365 Excel calls this tool "Forms", and it can be accessed through the O365 page (for licensed users). It is still available via the toolbar in Web Excel, but you will probably need to use Internet Explorer to get it to function correctly. I tried in Chrome, and it would not launch.

For basic surveys, this solution does a good job, and makes it easy to analyze in desktop Excel.