written by: Misty Faucheux•edited by: Tricia Goss•updated: 6/12/2010
Surveys are great tools to use to gather information. Sometimes, though, it is hard to figure out which program to use to create them. Here we discuss how you can use Microsoft Excel to create a survey with ease.
slide 1 of 2
Creating Your Answer Fields
Surveys are great ways to collect data, not only for major scientific inquiries, but also for marketing campaigns, customer satisfaction or even to gather information about students. Whatever your use for a survey, you need to be able to compile the data easily so that you can quickly discern what your information is saying. To do this, you can use Microsoft Excel to create a survey.
The first thing to do is create a list of answers for your questions. To do this, follow the below instructions:
·Open a new Excel Document.
·Create a header for your survey by going to View, then Header and Footer and Customize.
·Add your title.
·Rename Sheet 2 by right clicking the Sheet Tab 2 name and selecting rename. Rename it something like Answers.
·In this sheet, type all the possible answers for your first question in Column A.
·Highlight the cells in Column A. This will be your range of answers for the first question.
·Go to View and Formula Bar.
·Name your Formula Bar. The name should correspond to the question being asked. For this example, we will name it answerone.
slide 2 of 2
Creating Your Questions
Once you have created your answers list, you need to set up your question and data validation settings. You can do this by following the procedures below.
1. Create your list of questions. You can create this in a Word document so that you can keep track of all of your questions.
2. Go into your existing Excel document. Click the first tab and rename it Questions.
3. In Cell A1, enter your first question.
4. Select Cell B1, go to the Data dropdown, and click Validation.
5. Click on the Settings tab.
6. Go to Allow and then List. Here type =answerone in the Source blank.
7. Select the Input Message button. This allows Excel to know which question that you want others to see when they get to this point in the survey.
8. If you click the Error Alert tab, you can add a prompt such as “please select from one of these answers" to the Title section.
Once you have done this for your first question and answer, just repeat the process for all your subsequent questions and answers until you have completely finished your table of questions.
You should remember that surveys are meant to be short questionnaires. Try to keep your survey at ten questions or less. People are likelier to respond to shorter surveys. Really long ones can overwhelm them. When your survey is complete, you can distribute it a variety of ways. Print it out and pass out hardcopies. Or, just send it in an Email attachment.