This article is part of a series: How To Enter Survey Data

Part 1: Three Steps for Painless Survey Data Entry
Part 2: Preventing Mistakes in Survey Data Entry
Part 3: Common Issues with Survey Data Entry (and How to Solve Them)

In a previous article, Three Steps for Painless Survey Data Entry, I shared my system for entering data from paper surveys into a spreadsheet like Microsoft Excel. Here, I share solutions to two challenges you are likely to come across while entering survey data: 1) coding complex question types and 2) dealing with unclear responses. Addressing these challenges will require some advanced coding that I did not cover in my first article.

Entering data from complex survey questions

I recommend setting up the survey codebook in a systematic way because it increases data entry accuracy and speed. As a reminder, your codebook should look something like this:

Moving top to bottom and left to right, simply number the responses sequentially starting at 1. This works when you expect exactly one response to the question (e.g., Yes OR No). However, your survey won’t always be this simple. Below are some examples of how to set up a codebook for more complex questions:

Issue #1: Responses are already numbered

The question options may already be numbered on the survey, and respondents circle the number that applies (like in the image below). In this case, I would recommend following whatever numbering scheme is on the survey for data entry rather than re-numbering the responses. If someone circles 5, enter 5. Simple.

Issue #2: Table or matrix of questions

When you have multi-part questions (like the question matrix below), label each part of the question with lowercase letters starting at “a.” You’ll notice that in this example, there are checkboxes instead of numbers, so I added numbers in red to the codebook from left to right, starting at 1.

For this question, your data entry spreadsheet would be set up like this:

Issue #3: Select all that apply

A common question type is “select all that apply,” for example:

People can check as many options as they like, so the usual system of numbering sequentially will not work. Instead, we treat this question like a question matrix, where each response item is its own “question,” with possible responses being “checked” and “not checked.” The codebook would look like this:

This will make more sense with an example. If our survey comes back like this:

Reading through the responses in order (from top to bottom, left to right), we get:

• Friends or family = not checked

• TV = checked

• Facebook = not checked

• Twitter = checked

• Newspaper = checked

• I don’t remember = not checked

Using the codebook, these responses translate to:

• Q1a = 0

• Q1b = 1

• Q1c = 0

• Q1d = 1

• Q1e = 1

• Q1f = 0

So the data would be entered like this:

Dealing with unclear responses

If you’ve ever conducted a survey before, you’ve certainly seen some wonky responses. People will circle more than one option when you want them to select only one, they’ll skip questions or even entire pages, they’ll write comments beside their answers, they’ll create new answers and circle those instead… So we need a way to deal with these unclear answers that don’t fit into our nice neat data entry sheet. The key to dealing with wonky responses is to decide on a rule, document it, and apply it consistently.

Issue #4: Circled too many answers

When a respondent selects more than one answer (like checking “Very good” and “Good”), you have a few options:

1. Code as “unclear” by entering 98. The advantage of doing this is that you do not make any guesses about what the respondent meant. Instead, you mark it as “unclear” and it is excluded from analysis; or

2. Randomly pick one of the selected answers. You can do this by using a random number generator, or just type “flip a coin” into Google. The advantage of doing this is that you do not exclude as many responses (which may be important if you have a small sample size).

Issue #5: Made up their own answer

Sometimes people will write in their own answer (even when there is not an open-ended question). For example, you might see something like this:

The respondent created their own option (“4.5”) between Strongly agree and Agree (you’d be surprised how common this is). You can’t simply enter 4.5 into the data entry, because that is not one of the allowable responses in the codebook. Instead, you can treat this as if they circled “5” and “4,” and then carry on with the same procedure as when a respondent circles more than one answer. Your options are:

1. Code as “unclear” by entering 98; or

2. Randomly pick one of the selected answers (e.g., flip a coin to decide whether to enter “5” or “4”). 

Another example of a respondent creating their own answer is:

Here, the respondent made an option even higher than “Strongly agree”, which they wrote in as “6” and labeled with “Very!” Your options for data entry are:

1. Code as “unclear” by entering 98; or

2. Assume the respondent would “strongly agree” with the statement and enter “5” since it is the next closest response to their answer.

There are a few considerations when choosing an option for this scenario. On the one hand, we want to be careful to maintain the integrity of the original data – “6” is not the same thing as “Strongly agree,” so you may not want to assume that’s what the respondent meant. On the other hand, we might be fairly sure the respondent meant to indicate their agreement – should we try to capture the spirit of their response in the way we code the data? Either way of treating the data could be justified, so it’s important to decide what makes sense for your survey, document the rule, and follow it consistently.

I’ve covered some strategies you can use to overcome common challenges in entering survey data. If you conduct paper surveys, you’re likely to come across complex question types and unclear responses, but with some forethought and planning, you can make sure you’re prepared to deal with these challenges in a consistent way that makes sense for your data. However you choose to deal with complex questions or unclear responses on your survey, the key is to decide on a rule, document your decision (so it can be discussed later in the methods section), and follow it consistently.

Email Address
Sign Up

We respect your privacy.

Thank you!

This article is part of a series: How To Enter Survey Data

Part 1: Three Steps for Painless Survey Data Entry
Part 2: Preventing Mistakes in Survey Data Entry
Part 3: Common Issues with Survey Data Entry (and How to Solve Them)

When entering survey data, it is important that it is accurate, easy to analyze, and fast. The best way to meet these goals is to set yourself (or your data entry people) up for success from the beginning.

The old cliché “garbage in, garbage out” certainly applies to survey data entry. Our analysis can only be as good as our data, so it’s critical that the survey data is accurately translated from paper to spreadsheet. But being extra careful while entering data can only go so far – we get tired, we forget, and we make mistakes. Here are three tools you can use to prevent errors in survey data entry by making your spreadsheet foolproof: 1) data validation, 2) colour-coding columns, and 3) a “count” formula.

If you haven’t already set up your survey codebook and data entry spreadsheet, check out the previous article in this series for instructions on how to do so.

1. Data validation

Data validation is your first defence against data entry errors, and it is very simple to implement. Data validation just means defining which values are allowed in which cells. After you have made the survey codebook and data entry spreadsheet, you can set the validation on a question-by-question basis. I will use this survey question as an example:

In the spreadsheet for Q1, we want to allow only seven different values to be entered (the five responses 1-5, plus 98 and 99 for “unclear” and “missing/ skipped”). To set up Data Validation in Microsoft Excel, the steps are:

1. Highlight the Q1 column in your data entry spreadsheet

2. Click the “Data” tab in the Microsoft Excel Ribbon

3. Click “Data Validation”

4. Set Allow to “List”

5. Set Source to a list of the allowed values separated by commas (see image below)

6. I choose not to use the “in-cell dropdown” feature because I find it slows down my data entry, but this is up to you.

7. Click “Ok”

Now that data validation is set up, you will receive a pop-up message warning you if you enter a value that isn’t allowed for that question, which will guard against mis-typed data.

2. Colour-code columns

Colour-coding columns is especially helpful for long surveys. I highlight groups of questions in the same colour (e.g., a matrix containing six questions), which gives your eye a visual cue to make sure you’re still entering data in the correct cells of the spreadsheet. In addition to colours, you can also add borders between sections on the survey. For example:

By grouping questions together using colour and lines, you provide a visual anchor that helps you keep track of where you are in the data entry spreadsheet.

3. Count cells to make sure you didn’t miss any questions

It’s easy to accidentally skip a question, especially when it is at the end of the page (or maybe the respondent skipped it and you left it blank instead of entering 99). By adding a “count” column at the end of your data entry sheet, you can prevent this mistake. Simply add a column with the Excel formula =COUNTA(*specify the entire row*), then fill this formula down the entire column. This formula will count the number of cells in the row that aren’t blank:

In the example, the formula for the first row is =COUNTA(A2:P2). As you can see, if every cell is filled in properly (including the ID column), COUNTA will return the value 16 (because there are 16 non-blank cells). I name the count column “Count (16)” so I don’t forget it is supposed to add up to 16. If you accidentally skip a question, like I did on respondent ID#3 Q1b, the COUNTA value will be less than 16. This is a quick way to check when you reach the end of a survey that you didn’t miss any questions.

If your surveys don’t have ID numbers written on them, it can be very difficult or even impossible to go back and find a survey you made a mistake on. For this reason, I recommend checking the Count column at the end of every survey, or at least every few surveys, so it’s easy to flip back in your stack of surveys and find the culprit. Another option is to write the appropriate ID number on the surveys as you go, which gives you the ability to do quality control more easily.

Bonus Tip: Remember to save!

We all know the gut-wrenching feeling of a program crashing and you can’t remember the last time you saved. To avoid this heartbreak, I do a quick CTRL+S or CMD+S (save shortcut) at the end of every page of a survey – if you’re turning the page, save!

Email Address
Sign Up

We respect your privacy.

Thank you!

This article is part of a series: How To Enter Survey Data

Part 1: Three Steps for Painless Survey Data Entry
Part 2: Preventing Mistakes in Survey Data Entry
Part 3: Common Issues with Survey Data Entry (and How to Solve Them)

Arguably the most exciting part about conducting a survey is seeing the results – finally your hard work has come to fruition, and you get to hear what everybody had to say about your program or organization! But before you can get to that step, you need to transform the stack of paper surveys on your desk into useable data.

For some, the thought of survey data entry is a mind-numbing task, but I kind of love it… You get to switch the critical thinking part of your brain off and just focus on one simple task, which isn’t an opportunity we often get in this fast-paced world.

I’m going to share my three-step system for making survey data entry as easy and painless as possible, which comes from my experience designing, entering, and analyzing survey data.

Before you get started entering survey data, you should think about your goals. My priorities for survey data entry are that it is:

• Accurate,

• Easy to analyze, and

• Fast.

The most important job of data entry is that it is accurate. If it isn’t accurate, then forget analysis and speed. Accurate data entry means what ends up in the spreadsheet reflects exactly what was on the survey, every single time.

The next priority is that the survey data is easy to analyze. With some forethought, you can save your data analyst (which might also be you!) a lot of time and headache down the road.

Finally, data entry should be as fast as possible – time is money, after all! But never, ever sacrifice accuracy for speed.

Here are the three steps you can follow to set yourself up for painless survey data entry:

1. Review the survey carefully

Familiarize yourself with the questions on the survey, and the available options. Are there fill in the blanks? Multiple choice? Select all that apply? Most likely there are many question types, and understanding all the different questions is critical to steps 2 and 3. If it’s your first time seeing the particular survey, you might want to sit down and fill out a blank copy as if you were a respondent to get a really good feel for the questions.

2. Create the codebook

You should never be typing out the verbatim responses to each question while entering survey data (e.g., “yes” “yes” “no” “yes”). Instead, assign each response a number (e.g., yes = 1, no = 2) and enter those numbers instead of words. This fulfills all of our data entry priorities: it is more accurate, easier to analyze, and faster.

The codebook is your translator between the survey and the data. It tells you (and the analyst) how to turn survey responses into numbers, and back again. A copy of this codebook should live in the same folder as the data entry sheet and be clearly named. For added convenience, I paste a copy of the codebook into the data entry spreadsheet (Step 3) in a tab called Codebook. Here is what a simple codebook looks like:

The codebook outlines which number should be entered for each response. In this example, if someone answered Yes to Q1, you would enter “1.” If they answered No, enter “2.” You’ll notice I added the question numbers beside the questions – sometimes the paper surveys you receive won’t have the questions numbered, so you should write them into the codebook.

How you assign the response codes is up to you, but I strongly recommend following this system: from left-to-right and top-to-bottom, number the responses sequentially starting from 1. This way, the codes are the same no matter what the question is, which helps you ensure accuracy and speed. By following the same coding system for every question, the data entry person knows that the first response is always “1,” the second is always “2,” and so on. Numbers are faster and more accurate to type than letters because they are all close together on your keyboard’s number pad. Note: there are some exceptions to this rule when it comes to more complex question types, which I will cover in a follow-up article.

When it comes time to analyze the data, you might need to recode the data depending on how it will be analyzed (for example, maybe you want to change all the 1’s back to Yes’s, or change all the 2’s to 0’s). This is quick and easy to do at the analysis stage, and is not very prone to errors as long as you document any changes you make. Trust me, it’s way easier to change all the 1’s to Yes’s at the end than it is to type out “y-e-s” (or even just “y”) during data entry.

You’ll notice that I added “blank = 99 and unclear response = 98.” These are codes you will use when someone skips a question (99) or if they check more boxes than they are supposed to (98). How you deal with missing or unclear responses is up to you – just decide on a rule, document it, and apply it consistently. Entering 99 instead of leaving a blank cell is good practice because then you know for sure that question was skipped by the respondent, and not accidentally missed during data entry. However, do not use 98 and 99 if you are recording a numeric variable like age, because you won’t know if it is supposed to be “99 years old” or “missing data.” In this case, you may want to use 999 for missing data instead. Read more about blanks in data entry in our article “Four Common Data Entry Mistakes (and How to Fix Them)”.

3. Create the data entry spreadsheet

Now that you have the codebook, the data entry spreadsheet is easy to create. Using Microsoft Excel or Google Sheets (or other spreadsheet software) create a new file with one column for each question, plus a column for an identification number (ID#). Each cell will contain one number corresponding to the response to that question. For the above example, the spreadsheet (with some sample data) would look like this:

In a data entry spreadsheet, each row should always contain all the data for one unique individual. I like to add an ID column and fill the ID numbers all the way down the column before starting data entry. Even if there is no ID number on the survey to begin with, it is a good idea to add it to the spreadsheet because some statistical programs require a unique ID for each respondent. You may also want to manually write the ID numbers on the surveys as you enter them — if you don’t put ID numbers on the surveys, it is very difficult to go back and fix mistakes or do quality control.

When entering data, I keep my right hand on the keyboard’s number pad, and my left hand on the Tab key. Hitting Tab moves you to the right in the spreadsheet (to the next question), and when you get to the end of each survey you hit Enter to move down to the start of the next row. Remember to keep an eye on the screen to make sure you are still entering data in the correct cells.

Now that you’ve familiarized yourself with the survey and set up your codebook and data entry spreadsheet, it’s time to start entering data! This is the part where I turn on a podcast or some music, and let my mind focus solely on the task of data entry. If you follow these steps, you might be surprised at how painless (and even relaxing) data entry can be.

In the next article, I will cover some more advanced survey data entry topics, such as entering complex question types and dealing with unclear responses.

Email Address
Sign Up

We respect your privacy.

Thank you!