How to Code a Questionnaire in Excel

When you collect survey responses (paper-based or online), you’ll often have text answers (“Strongly Agree”, “Male”, “Bachelor’s degree”) that aren’t ready for analysis. Coding transforms these into numbers or standardized categories that Excel (or SPSS, R, Python, etc.) can process.

---

Step 1: Set Up Your Spreadsheet

1. Open Excel.
2. Put questions as column headers (Q1, Q2, Q3, or short labels).
3. Put each respondent as a row.

Example:

ID	Gender	Q1_Satisfaction	Q2_Frequency	Q3_Comments
1	Male	Agree	Weekly	Good service
2	Female	Strongly Agree	Daily	Too crowded

---

Step 2: Decide Coding Rules

For each type of question, assign numbers to responses.

1. Nominal (categories, no order)
   • Example: Gender
     • Male = 1, Female = 2, Other = 3
2. Ordinal (ranked, Likert scales)
   • Example: Satisfaction (Strongly Disagree → Strongly Agree)
     • Strongly Disagree = 1
     • Disagree = 2
     • Neutral = 3
     • Agree = 4
     • Strongly Agree = 5
3. Interval/Ratio (numeric answers)
   • Example: Age, Income → keep as numbers.
4. Multiple-response (check-all-that-apply)
   • Code each option in a separate column with 1 = selected, 0 = not selected.
   • Example: “Which apps do you use?” RespondentFacebookInstagramTikTok11012011

---

Step 3: Apply the Coding in Excel

You can:

• Manually replace text answers with numbers.
• Or use Excel’s Find & Replace (Ctrl+H) to swap quickly.
• Or use formulas like:

=IF(B2="Male",1,IF(B2="Female",2,3))

(This would code Male = 1, Female = 2, Other = 3.)

---

Step 4: Keep a Codebook

Always create a separate sheet in your Excel file as a codebook:

Variable	Question	Coding Rules
Gender	What is your gender?	1 = Male, 2 = Female, 3 = Other
Q1	I am satisfied with the service (Likert)	1 = Strongly Disagree … 5 = Strongly Agree
Apps	Which apps do you use?	1 = Selected, 0 = Not selected

This ensures transparency and reproducibility.

---

Step 5: Test and Clean Your Data

• Check for missing values (leave blank or code as 99/NA depending on your analysis tool).
• Ensure consistency (e.g., no mix of “Male” and “male”).
• Verify by running quick counts using =COUNTIF() for each code.

---

Final Thoughts

Coding a questionnaire in Excel is all about:

1. Structuring your sheet properly (respondents in rows, questions in columns).
2. Converting text into numbers using clear rules.
3. Keeping a codebook for reference.
4. Using formulas or find/replace to speed things up.

Once coded, your dataset is ready for analysis (descriptive stats, charts, regression, factor analysis—you name it).