How to Code and Analyze Multiple Response Questions in Excel

When respondents can select more than one answer (e.g., Facebook, Instagram, TikTok), the data doesn’t fit neatly into a single column. Instead, you’ll need to restructure, code, and summarize it properly to get meaningful insights.

Here’s how to do it.

---

Step 1: Enter and Code the Data

There are two common ways to set up multiple response questions in Excel:

1. Multiple Columns (Preferred Method)
   • Create one column for each possible option.
   • Code responses as 1 = Selected, 0 = Not Selected.
2. Delimited Text in One Cell (Not Ideal)
   • Some survey tools export answers like:
     • Respondent 1: Facebook; TikTok
     • Respondent 2: Instagram; Twitter
   • You’ll need to split text into separate columns using Data → Text to Columns or Power Query before analysis.

---

Step 2: Create Frequency Counts

To see how many people selected each option:

1. At the bottom of each column, use: =SUM(B2:B100)
2. This gives you the total number of times each option was selected.
3. To calculate percentages: =SUM(B2:B100)/COUNTA(A2:A100)

---

Step 3: Build a Summary Table

Create a clean table like this:

Option	Count	% of Respondents
Facebook	120	60%
Instagram	90	45%
TikTok	70	35%
Twitter	50	25%

This makes your results easy to read.

---

Step 4: Use PivotTables for Flexible Analysis

PivotTables in Excel let you slice and dice the data:

1. Select your coded dataset → Insert → PivotTable.
2. Drag each response option (e.g., Facebook, Instagram) into the Values area.
3. Set calculation to Sum (to count selections).
4. Optionally, add demographic variables (e.g., Age, Gender) into Rows or Columns to compare groups.

Tip: With PivotTables, you can quickly answer questions like:

• Which platform is most popular among 18–24 year-olds?
• Do men and women differ in platform usage?

---

Step 5: Visualize the Results

Make your results more engaging with charts:

• Use bar charts to show frequency of each option.
• Use stacked bar charts if comparing across groups (e.g., age groups).
• Avoid pie charts for multiple response data (they’re misleading since totals exceed 100%).

---

Final Thoughts

Coding and analyzing multiple response questions in Excel is all about breaking answers into separate columns, using 1/0 coding, and then summarizing with sums, percentages, and PivotTables.

• Code as 1/0 per option (Chosen/Not Chosen).
• Count totals with SUM.
• Calculate % of respondents.
• Use PivotTables for deeper insights.

Once you’ve mastered this workflow, analyzing multiple-choice survey questions becomes quick, accurate, and visually clear.