How to Analyse Survey Results in Excel

To analyze survey data in Excel, you count how many people gave each answer, turn the counts into percentages, and compare groups. COUNTIF and COUNTIFS do the counting, and a pivot table can do it all in a few clicks. This guide works through a small staff survey from raw responses to a clear summary, the kind of task you might get in a marketing, HR or analyst role.

How to lay out survey data#

Survey data is easiest to work with one row per respondent and one column per question. Most survey tools export in this shape already. Here is a 10-response example:

Satisfaction is a rating from 1 (very unhappy) to 5 (very happy). Recommend is a Yes/No question. Don't merge cells or add subtotals inside the data; keep the summary somewhere else.

Step 1: Clean the responses#

Clean data first, because COUNTIF only matches exact values. Common problems in survey exports:

• Inconsistent answers: "Yes", "yes " and "Y" are three different values to a formula. Use Find & Replace (Ctrl+H; Mac: Control+H) to make them identical.
• Extra spaces: a trailing space stops "Yes " matching "Yes". Clean a column with TRIM in a helper column, then paste the results as values.
• Ratings stored as text: numbers on the left of the cell are text, and COUNT ignores them. Convert them with VALUE or Data > Text to Columns > Finish.
• Skipped questions: leave them blank. Don't type 0, which would count as a real rating.

Step 2: Count each answer with COUNTIF#

COUNTIF counts the cells in a range that match one condition. Make a small table with each possible answer in F2:F6 (5, 4, 3, 2, 1) and in G2:

=COUNTIF($C$2:$C$11,F2)

Fill it down. The counts are 5: 3, 4: 3, 3: 2, 2: 1 and 1: 1, which adds up to 10. Always check that the counts add up to the number of responses. If they don't, some answers don't match your list exactly.

For the Yes/No question, put the text in quotes: =COUNTIF(D2:D11,"Yes") returns 7. The COUNTIF function guide covers all the criteria types, and the attendance tracker uses the same counting pattern for codes.

Step 3: Turn counts into percentages#

Percentages make results easy to compare, even between surveys of different sizes. Divide each count by the number of responses. In H2:

=G2/COUNT($C$2:$C$11)

Format as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). A rating of 5 is 30% of responses. COUNT counts only numbers, so blank (skipped) answers are left out of the total. For text answers, use COUNTA instead: =COUNTIF(D2:D11,"Yes")/COUNTA(D2:D11) gives 70%.

Step 4: Summarise ratings: average and top-2-box#

Two numbers summarise a rating question well. The average is simple:

=AVERAGE(C2:C11)

That's 3.6 out of 5. The top-2-box score is the share of people who chose one of the two best answers (4 or 5). Many survey reports use it because it's easier to explain than an average:

=COUNTIF(C2:C11,">=4")/COUNT(C2:C11)

The result is 60%: six of ten people rated 4 or 5. Put the operator and number inside the quotes, with no space. The exercise on this page does the same on the first seven responses.

Step 5: Compare groups with COUNTIFS#

COUNTIFS counts rows that meet two or more conditions at once. To count Sales staff who rated 4 or more:

=COUNTIFS(B2:B11,"Sales",C2:C11,">=4")

The result is 2 (responses 1 and 6). Divide by the number of Sales responses, COUNTIF(B2:B11,"Sales"), which is 4, for a Sales top-2-box of 50%. Support and Marketing each score 67% (2 of 3). The COUNTIFS function guide explains the range and condition pairs, and the comparison of COUNTIF vs COUNTIFS shows when you need the second function.

For a group's average rating, use AVERAGEIF: =AVERAGEIF(B2:B11,"Marketing",C2:C11) returns 4, the highest of the three departments.

Be careful with small groups. With only three or four responses per department, one person changes the result a lot. Show the number of responses next to each percentage.

Step 6: Summarise everything with a pivot table#

A pivot table counts and groups data without formulas. It's the fastest way to explore results.

1. Click any cell in the data and choose Insert > PivotTable, then OK.
2. Drag Department to Rows.
3. Drag Satisfaction to Columns.
4. Drag Response to Values. If it shows Sum, click it, choose Value Field Settings and pick Count.

You now have a grid with each department's count of every rating. To see percentages, right-click a number, choose Show Values As > % of Row Total, and each department's row adds up to 100%. When new responses arrive, right-click the pivot table and choose Refresh. Practise pivot tables to learn the layout step by step.

Finish with a chart: select the percentage table and choose Insert > Bar Chart. Bars work better than pie charts when there are five rating levels to compare.

Where these skills are used#

Survey analysis comes up in marketing, HR and research roles. The article on Excel skills for marketing jobs shows where these counts and percentages appear in campaign and customer reports. If you're preparing for an analyst role, read about the data analyst Excel test and try the data analyst mock test to practise COUNTIFS and pivot tables against the clock.

Practise it#

Do the exercise on this page: a top-2-box score with COUNTIF and COUNT. Then practise COUNTIF in a live spreadsheet to count answers of every type, and practise pivot tables to summarise a whole survey by group in a few clicks.