Design a survey and evaluate the data with Microsoft Office Excel

Use Excel and Word to easily design, capture, and track survey data.

Video: Design a survey and evaluate the data

Students can design a class survey project to include interviewing many people with a Microsoft Word questionnaire, capturing data in forms, entering results in a database, and tracking/analyzing the data using Microsoft Excel. Watch the video

Create your survey questionnaire with Office Word 2007

Plan your survey on paper. Remember, the success of a survey and the data you collect is only as good as the questions you ask.
Open a new, blank document in Office Word 2007, and type a title and any other heading information that you need, such as your name, the date, or the name of the survey. Also, if people will be taking the survey on their own, add instructions explaining how to fill out the form.
Type your survey questions and the multiple-choice answers.

Tip: On the Home tab, click the Numbering button to automatically number questions and choices. By default, when you type a number followed by a period and space, Office Word 2007 starts numbering for you.

Tip: Try using a table like the example below. By using tables, you can fit your questionnaire onto fewer pages. To create a table, on the Insert tab, click Table, and then choose how you want to insert the table.
Have your teacher approve your survey. Then, save and print it.

Conduct your survey

Hand out your survey questionnaires for others to complete, or interview people in person. After all of the questionnaires have been turned in, enter the data into a database in Office Excel 2007?

Create your database

This is your header row.

Open a new workbook in Office Excel 2007.
In row 1, type field names in each column that correspond to the survey questions.
In the rows beneath the header row, type the answers from the questionnaires, so they line up with the proper field names in the header.
Select all the cells in the database, and, on the Insert tab, click Table. Select My table has headers, and then click OK. Office Excel 2007 turns your data into a table. Now you can click the arrows in the header row to sort or filter the table.

Use a formula to analyze the data

Click an empty cell next to the table.
In the formula bar, type: =countif(
Select the data in the first column of the table, excluding the field name, and then type: ,"1") The formula will look like this: =COUNTIF(A2:A21,"1")
Press ENTER. The cell displays the results of the formula—a number showing how many 1s are in the column, or, in other words, the number of people whom like vanilla ice cream best.
Repeat these steps to add formulas for the rest of the questions and choices.

Create a chart

Select the results (the labels and the counts) from one of the questions.
On the Insert tab, in the Charts group, click Pie, and click a simple pie chart layout. Office Excel 2007 creates a pie chart from the data.

Advanced users:

Figure out what percentage of people selected certain answers.
Calculate averages for questions.
Discuss what might have influenced people to answer the way they did (age, where they were raised, gender, etc.).