How to Create a Free Survey and Collect Data with Excel
Are you tired of having to manually collect and consolidate data, from various people, into your Excel spreadsheet? If so, chances are you have yet to discover Excel Survey.
Microsoft introduced Excel Survey a few years ago along with Office Online. However, you may not have noticed it if you haven’t ventured outside of the desktop version of Office. The survey feature is only available in the online version, which makes sense considering you will need your survey to be available to users via the Internet.
What Is an Excel Survey?
An Excel Survey is a Web form, that you design to collect and store structured data, into an Excel spreadsheet. You have many options when it comes to Web surveys or forms. Alternatives such as Google Forms and Survey Monkey may have more robust features, but when you have a need for collecting simple datasets from several people, this tool will do the job seamlessly in the Microsoft ecosystem.
Create an Excel Survey
1. Create a New Survey from OneDrive
From the menu, select New > Excel Survey
2. Add a Survey to an Existing Excel Spreadsheet
From within an existing Excel Online spreadsheet, select Home > Survey > New Survey
Design Your Survey Form
You will want to give your survey a good title and description, so your responders will understand quickly why they are being asked to provide data. Next, you will add a question for each piece of information you will require.
Questions from your survey will correspond to columns in your spreadsheet, and each response will be represented as a row.
Explore Your Options
It’s important to be clear how you will want to use the results before setting up the question types. If you have data that you will need to sort or filter, you most likely should use a “choice” field in order to control the input. A “text” field should be used for dynamic information that cannot be predefined, such as a person’s name.
Look through the available types of fields and select the best one for each column. Then decide if you want the question to be required or optional, and if you want a default value to be displayed automatically on the form.
- Text – A short text field.
- Paragraph Text – A long text field.
- Number – Numerical data.
- Date – Date values.
- Time – Time values.
- Yes/No – Dropdown box that only allows a “Yes” or “No” choice.
- Choice – Dropdown box with choices defined by you.
Know the Limitations
As you can see, Excel Survey is a very basic tool that will likely get the job done for a majority of situations. However, it has some limitations to take into consideration. If any of these are a deal breaker, then you might want to look at some alternative solutions .
- Anonymous Access – You have no way to require a login or limit the access to a survey once it’s shared. If you have the link, you can submit a response.
- Branching aka Skip Logic – What you see is what you get. You cannot present different information to user based on the answers they provide to a question.
- Input Validation – You cannot validate data that is entered into the form outside of using one of the “choice” field types.
Preview Your Survey
Now that you have completed the design, you should preview your work to see how it will display when you send it. To do this, select the Save and View button on the bottom of the form.
This is what your survey recipients will see when you share the link with them:
Share Your Survey
Once you are happy with how everything looks, it’s time to share! Click the Share Survey button and you will be presented with a link that you can send to your target audience.
You have the option to shorten the link. This is optional, but if you want to do this, just click on Shorten link.
View Your Responses
The survey in this example is gathering data to help plan an office party, but the possibilities for how you can use it are endless. You could create a survey to have your team track their time on a particular project, gather feedback on performance, or even to have your family help decide on the location of your next reunion!
After your survey link has been sent, you can start monitoring your spreadsheet for responses and analyzing the results. The data will be stored in the spreadsheet that the survey is linked to. If you want to combine datasets to help with data analysis, you can do that with the Power Query tool built into Excel.
Image Credits:Market Research Survey by Pixsooz via Shutterstock