Coding survey data in Excel: prepare your dataset for analysis

Your survey responses are in Excel. There are ratings, longer comments and empty cells, but you are not sure how to start preparing the results for your thesis. First, organise the file so that each answer keeps its meaning and can be counted correctly. That is what coding survey data is for. You do not need programming skills, but you do need to make a few considered decisions.

We will work through them using a survey about consultations during thesis writing. You will see what to do with a missing rating, how to separate several selected answers and when to leave a cell empty. You will also learn what to send to the person analysing your results so they do not have to guess what participants were asked. At this stage, we are preparing responses for analysis, not yet drawing conclusions about the people surveyed.

Start with a copy and keep the original responses

After downloading responses from Google Forms or another survey tool, save the original file and leave it unchanged. Make a working copy and add the date to its name. Keep a brief note of what you change and why. Something as simple as “removed h from the hours column, keeping the number entered” is enough. This lets you distinguish a participant's answer from your own correction and check that the correction has not changed what the answer means.

In a survey completed once, each row usually contains one person's responses. Give each questionnaire a permanent number, such as S001, S002 or S003, in a separate column. The row number displayed down the side of Excel is not enough because responses change position when sorted. If the same people answered before and after a class, also keep a record of which occasion each response belongs to. Do not treat two measurements from one person as two separate participants.

Do not include names, email addresses or phone numbers in the file intended for analysis. If you need to know which questionnaire belongs to which person, keep that link in a separate, appropriately protected file. Replacing a name with a number does not make responses fully anonymous if you can still identify who provided them. Check written comments as well: a participant may have included personal details in their own words.

Three questionnaires and three decisions: what should go in Excel?

In the following example, students rate the usefulness of consultations from 1 to 5 and indicate which forms of contact they used. They can select email, an in-person meeting and a video call. We also ask whether they do paid work. Only those who answer “yes” see the question about hours worked during the previous week. Let us look at how to prepare three different responses.

Two empty cells look the same in a spreadsheet but can mean quite different things. Alongside the number itself, it is worth keeping a note of why it is absent. Later, this will help you establish how many people actually answered a particular question.

If you are unsure how to organise the responses you downloaded, you can start by sending the questionnaire and a file without personal details. Through “Analysis and presentation of research results for your thesis”, we can establish what needs preparing before the results are calculated. You do not need to fix every empty cell yourself or reshape the spreadsheet by following an unrelated tutorial first.

Add a short explanation of what each column means

A heading such as “Question 3” may mean very little to you a few weeks later. Add explanations on a separate sheet. This is called a variable dictionary or codebook: here, a variable simply means a piece of information recorded in a column, such as the consultation rating. You do not need lengthy documentation. You need enough detail for you and the person helping with the analysis to understand the answers in the same way.

• Column name: for example, “Consultation rating” rather than just “Q3”.
• Exact question: copy it from the questionnaire participants received.
• Meaning of the answers: explain what 1, 2, 3, 4 and 5 mean, using the scale descriptions in the survey.
• What is being measured: a rating, hours worked or membership of a group, such as mode of study.
• Empty cells: note who was meant to answer and how you identify a missing response.
• Changes made: describe any score conversions or other corrections that were needed.

AGH University Library recommends including descriptions of variables in data documentation. In practice, this prevents a simple mistake: the code 1 must not sometimes mean “yes” and sometimes “no” in the same column. Do not identify groups using cell colours alone either, because colours may disappear when the file is transferred to another program. If the difficulty is deciding what a question actually measures, see “Operationalising variables in a thesis: indicators, measurements and examples”.

When should you turn answers into numbers and when should you keep words?

“Full-time study” and “part-time study” can be recorded as 1 and 2. Here, the numbers are simply labels. They do not mean that the second mode of study is twice as large or twice as good. Similarly, ratings from 1 to 5 have a set order, but numbering the answers does not prove that the distance between each pair of adjacent ratings is equal.

You do not have to turn every word into a number. It depends on the planned analysis and the software being used. What matters is recording answers consistently and explaining what the entries mean. If numeric codes have been agreed, include their definitions. The APS textbook discusses labelling answers separately from identifying missing values. This is a useful distinction: “missing answer” is not another rating on the scale.

Also check answers that are supposed to be numbers. An entry such as “20 h” is best separated into the number 20 in the cell and a note that the column contains hours. However, do not remove leading zeros from a questionnaire number if “001” is used to identify it. Record dates unambiguously, for example as year, month and day. Depending on the software settings, 03/04 can be read as either 3 April or 4 March.

What should you do with empty cells, zero and “I don't know”?

Return to the hours-worked question. Beside the column of numbers, you can add a column called “Notes on the response”. Use it to record whether hours were provided, whether the person left the question unanswered or whether the survey never showed it to them. This keeps a student who does no paid work distinct from a student who works but did not report their hours.

• 0: the participant actually entered zero, and the question allows that answer.
• Missing answer: the person was meant to answer but did not provide a value.
• Not applicable: under the survey's instructions, the question was not intended for this person.
• I don't know or prefer not to answer: keep that information if the person selected that option.

If all you have is an empty cell, do not guess why someone left it blank. Check the wording and order of the questions. If you still cannot establish the reason, record that uncertainty. Before your next survey, it is worth testing which questions each participant will see. The Statistical Office in Poznań also draws attention to links between questions and trying out a questionnaire before collecting responses.

Take care with special codes such as 99 or -9. If they mean “missing”, they must not later be included in the average rating or hours worked. Excel will treat them as ordinary numbers unless the calculation excludes them. Do not fill every empty cell with other people's average answer either. First, establish how many responses are missing, which questions they concern and what the questionnaire's instructions require.

This is where a consultation before running tests can help. Through “Statistical analysis for bachelor’s and master’s theses”, we can establish how to handle missing answers and which responses can be used in the planned calculations. The purpose is to preserve what the data mean, not to adjust them until a result supports your hypothesis.

What if some questions are scored differently?

Some established questionnaires require certain responses to be converted before points are added together. For example, one statement might describe feeling at ease during presentations and another might describe tension. First, check the instructions for that particular questionnaire. Negative wording alone does not mean that a score must be reversed. Do not make that decision only after seeing the results.

If the instructions require reversing scores from 1 to 5, an answer of 1 becomes 5 points, 2 becomes 4 and 3 remains 3. Likewise, 4 becomes 2 and 5 becomes 1. Record the converted points in a new column and keep the original response beside them. If the scale starts at zero, the conversion will be different. Do not copy a scoring rule from another questionnaire.

Manually check at least answers 1, 3 and 5, together with an empty cell. A result of 6 would indicate an error on this scale. You do not need to use the formula if you are preparing a file for someone else to analyse: include the questionnaire instructions and leave the responses unchanged. Adding or averaging several questions is appropriate only where the instrument's scoring rules allow it, including its rules for incomplete responses.

How do you record a question with several possible selections?

In the consultation survey, one person could select both email and an in-person meeting. Both answers matter. To make them easier to count, create separate columns called “Email”, “Meeting” and “Video call”. For a person who answered the question, enter 1 for a selected option and 0 for an option they did not select. Our three questionnaires would look like this:

• S001: email 1, meeting 1, video call 0.
• S002: email 0, meeting 0, video call 1.
• S003: email 1, meeting 0, video call 0.

If someone did not answer the question at all, do not automatically enter three zeros. “I use none of these” does not necessarily mean the same as “I did not answer”. A separate “none of the above” option helps. Kent State University's guide shows how distinguishing these situations affects the subsequent counts. If the survey does not distinguish them, keep a note of that limitation rather than assigning a choice to the participant.

Remember that you can count people or all selections. A student who uses two forms of contact is one person but contributes two selections. Percentages calculated using people as the denominator therefore do not have to add up to 100%. Do not remove a second choice to “fix” the total. If you separate answers automatically, check several rows manually: similar option names or commas within an answer can lead to mistakes.

How can you organise answers written in participants' own words?

Asked “What makes it difficult to attend consultations?”, someone writes: “They clash with work, and travelling there takes an hour.” Keep the complete response. Beside it, you can record two themes: an inconvenient time and travel. Do not shorten it to “lack of time”, because part of the information would disappear. If you plan to count themes, decide first whether a response can belong to more than one group.

Read some of the responses and explain what belongs in each group. Check whether the same rules can be applied to the remaining comments. If you change a group's definition, revisit earlier responses too. You can correct a typing error in a working copy, but do not change the meaning of a participant's words. Before sharing the material, remove details that could identify them, such as a name included in a longer comment.

A final check: does the file still match the questionnaire?

Before calculating results, look for ordinary recording errors. A rating of 7 in a question with a 1-5 scale needs investigating. A very large but possible number of hours, however, is not automatically a mistake. Compare it with the question and the original response. Preparing data is about correcting errors, not removing answers that make the result you expected harder to obtain.

1. Check that the number of questionnaires matches the downloaded file and that each has its own number.
2. Look through the answers in each column for typing errors, extra spaces and entries the questionnaire did not allow.
3. Check permitted ratings and the order of questions, such as hours worked for people who answered “I do not work”.
4. Count missing answers separately for each question. Distinguish situations where the question did not apply.
5. Compare several converted scores and multiple-choice answers with the originals.
6. Make sure sorting moved complete rows, rather than just one column of answers.
7. If a questionnaire will not be included in the analysis, record the reason and keep it in the original file.

Two identical sets of answers do not prove that one person submitted the questionnaire twice. Before deleting anything, check what evidence you have of a repeat submission. You may need submission times or an agreed method of identifying questionnaires. Similar answers alone are not enough. Recording the decision will help you explain why your analysis includes fewer questionnaires than you originally downloaded.

What should you send with the responses so they can be analysed?

It is usually convenient to keep an Excel file in XLSX format, with separate sheets for responses, explanations and notes on changes. If the person analysing the results needs CSV, agree how to save it. CSV transfers data from a single sheet, not the complete set of explanations. Reopen the file and check that columns have not merged, Polish and Ukrainian characters are readable, and numbers and dates are unchanged.

If formulas were used, also keep a version in which the calculations can be checked. Agree whether a file containing only the results of those formulas is needed for the next stage. Check empty cells too: a cell that looks blank can contain a formula, and the next program may not recognise it as a missing answer. Comparing a few such entries after opening the file can prevent later errors.

Include the exact questionnaire, explanations of the columns, scoring instructions for any scales and your research questions. Note what has already been changed and which responses are uncertain. This allows the analysis to begin without having to guess what the numbers mean. The next stage is covered in “How to analyse survey results for a thesis: Excel and examples”.

Sources and how they are used

The following resources help distinguish descriptions of responses, missing-answer rules and file preparation. When converting scores, you also need the instructions for the questionnaire you actually used.

• Firkowska-Mankiewicz A., Kanash T., Tarkowska E. (eds.), Krótkie wykłady z socjologii. Przegląd problemów i metod, Warsaw 2012. Appendix by R. Grzejszczak, pp. 385-387: value labels and missing values. In Polish.
• AGH University Library, RODBUK: documenting a dataset: variable descriptions, file formats and naming conventions. In Polish.
• Statistical Office in Poznań, Jak przygotować i przeprowadzić badanie ankietowe?, 2024: an introduction to piloting and dependencies between questions. In Polish.
• Kent State University Libraries, Working with “Check All That Apply” Survey Data: organising multiple-response answers.