When can respondents vote more than one answer (e.g., Facebook, Instagram, TikTok), the data doesn’t fit neatly in one column. Instead, you should do it restructuring, coding, and summarizing correctly to gain meaningful insights.

Here’s how to do it.

---

Step 1: Enter and Code 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.
   • Response code as 1 = Selected, 0 = Not Selected.
2. Text Limited to One Cell (Not Ideal)
   • Some survey tools export answers such as:
     • Respondent 1: Facebook; TikTok
     • Respondent 2: Instagram; Twitter
   • You have to do it split text into separate columns use Data → Text to Columns or Power Query before analysis.

---

Step 2: Make a Frequency Count

To see how many people chose each option:

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

---

Step 3: Create a Summary Table

Create a clean table like this:

Choice	Count	% 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 sort data:

1. Select your coded dataset → Insert → PivotTable.
2. Drag each response option (for example, Facebook, Instagram) inwards Value field.
3. Set calculation to Amount (to calculate options).
4. Optionally, add demographic variables (e.g., Age, Gender) to it Row or Column to compare groups.

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

• Which platforms are most popular among 18–24 year olds?
• Do men and women differ in their use of the platform?

---

Step 5: Visualize the Results

Make your results more interesting with graphs:

• Use bar chart to show the frequency of each option.
• Use stacked bar chart when comparing between groups (for example, age groups).
• Avoid pie charts for multiple response data (this is misleading because the total exceeds 100%).

---

Final Thoughts

Coding and analyzing multiple response questions in Excel is everything divide answers into separate columns, use the code 1/0Then summarize with amounts, percentages, and PivotTables.

• Code as 1/0 per option (Selected/Not Selected).
• Calculate the total with SUM.
• Calculate % of respondents.
• Use PivotTables for deeper insights.

Once you master this workflow, analyzing multiple-choice survey questions becomes fast, accurate, and visually clear.