Audience Dialogue

Using Excel for survey analysis (2) Data entry

The standard way of entering survey data is one line for each respondent, one column for each question. The first row should have a label for each variable. The file might look like this:

To make sure that only valid values go in each column, you can set up validation, using the Validation command in the Data menu. With this working, you can set up (say) Column C to accept only a y or an n, meaning Yes or No. But do this after you enter the labels in the top row, otherwise it won't accept them.

It's also possible to set up Custom Data Validation - e.g checking that a set of percentages doesn't add to more than 100. This is done by selecting "Custom" from the "Allow" submenu - see this page at for details of how to do it.

If a question is not answered, it's best to enter a blank space - don't leave the cell empty or put in a hyphen or other symbol. (When you get to the page on pivot tables, you'll find out why.)

Multiple response questions do not work well in Excel. Q4 in the above example was a question with about 10 possible answers, and respondents could give any combination of these. Most people gave only one answer.

You can turn the format shown above (faster and more accurate for data entry) into a one-variable-per-column format, by parsing the original column, but you really need to know what you're doing, and it's easy to do it wrong. So it's better to spend a bit more time on data entry, and treat each multiple answer as if it were a separate question, with a Yes or No answer. To avoid errors, use a different code for each Yes answer. For example, the first column could be 1 (if that answer applies), or 0 if it doesn't. The next column could be 2 or 0, and so on.


If you prefer, you can use a form for data entry. Forms need no special setting up, apart from using the first row for column headings, and each other row for one respondent. If you entered the above data as a form, the first respondent's data would look like this:

It's simple to enter data through a form. There are two steps:
First, enter the column headings in Row 1.
Last, click on the Data menu on the top line, then on Form. Using a Form, you enter one line of data at a time. Clicking on the Close box on a form takes you back to the normal spreadsheet style of data entry - moving across each line, instead of down (as inside a form).

You press the Tab key to go down the form (equivalent to going across a row) and the Enter key to start a new form (i.e. a new row). With a form, you can also search for particular answers. You only get about 10 questions on a form before having to scroll down.

Next » Analysing numeric variables