Blog · How-to guides · Customer service
Analyze structured survey responses in Excel with explicit valid-response denominators. Build rating distributions and segment summaries, then reconcile every total to the response rows.
Survey analysis in Excel turns structured response rows into rating counts, valid-answer averages, and segment comparisons. Define the denominator for each question before calculating a percentage. A blank answer is missing information, and a submitted survey is not automatically a complete response to every question.
Export response ID, submission date, customer segment, question ID, and coded answer. For this walkthrough, put one rating question in a wide table with response ID in A, segment in B, and rating in C. The scale runs from one through five, with four or five defined as positive before examining the results.
Confirm what an ID identifies: a person, an account, or a survey submission. If repeat submissions are allowed, decide whether to retain them or use a documented selection rule. Do not silently remove a second submission just because its rating differs.
Use a fixed survey version and reporting window. Preserve segment labels as they stood when the response was recorded, or disclose that you are applying current labels. Changes to question wording or segment definitions can move the result without any change in customer experience.
For a satisfaction-specific measure, CSAT analytics covers the business interpretation. The worksheet below focuses on checking a general coded-rating export.
A US service company receives the following eight submissions. The word "blank" below means an empty worksheet cell, not text entered in C5. D is a calculated validity flag; E is the positive flag, and F retains only valid scores.
| Response | Segment | Rating | Valid flag | Positive flag | Valid score |
|---|---|---|---|---|---|
| R-A | SMB | 5 | 1 | 1 | 5 |
| R-B | SMB | 4 | 1 | 1 | 4 |
| R-C | SMB | 3 | 1 | 0 | 3 |
| R-D | SMB | blank | 0 | 0 | 0 |
| R-E | Enterprise | 5 | 1 | 1 | 5 |
| R-F | Enterprise | 2 | 1 | 0 | 2 |
| R-G | Enterprise | 4 | 1 | 1 | 4 |
| R-H | Enterprise | 4 | 1 | 1 | 4 |
| Total | 7 | 5 | 27 |
In D2:F2, enter the following expressions and fill down through row 9:
D2 =IFERROR(--AND(ISNUMBER(C2),C2>=1,C2<=5,MOD(C2,1)=0),0)
E2 =IF(D2=1,--(C2>=4),0)
F2 =IF(D2=1,C2,0)
The validity rule accepts only numeric whole-number answers on the stated scale. It rejects blanks, text, decimals, and out-of-range codes. If an export stores numeric answers as text, convert and verify those cells before applying the rule. Keep the original export for comparison.
The zero in F for an invalid row is a helper value, not an assigned survey rating. Never average F directly. Divide its total by the valid flags instead.
Enter SMB and Enterprise in H2:H3. Set up I for submitted records, J for valid answers, K for positive answers, L for score total, M for mean rating, and N for positive share:
I2 =COUNTIF($B$2:$B$9,H2)
J2 =COUNTIFS($B$2:$B$9,H2,$D$2:$D$9,1)
K2 =COUNTIFS($B$2:$B$9,H2,$D$2:$D$9,1,$C$2:$C$9,">=4")
L2 =SUMIF($B$2:$B$9,H2,$F$2:$F$9)
M2 =IF(J2=0,"",L2/J2)
N2 =IF(J2=0,"",K2/J2)
Fill down one row. Microsoft documents COUNTIFS as counting a row only when all its criteria match. Here both the segment and validity criteria must hold, so an invalid answer cannot enter the positive numerator. Covirage is not affiliated with Microsoft.
| Segment | Submitted | Valid | Positive | Score total | Mean rating | Positive share |
|---|---|---|---|---|---|---|
| SMB | 4 | 3 | 2 | 12 | 4.00 | 66.7% |
| Enterprise | 4 | 4 | 3 | 15 | 3.75 | 75.0% |
| All | 8 | 7 | 5 | 27 | 3.86 | 71.4% |
Means are rounded to two decimals and percentages to one. Enterprise has the higher positive share, while SMB has the higher mean. These summaries answer different questions: retain both the counts and the distribution before deciding which segment needs attention.
List the five allowed ratings in P2:P6. Count each in Q2 with the following formula, then divide by the total valid flags in R2:
Q2 =COUNTIFS($C$2:$C$9,P2,$D$2:$D$9,1)
R2 =Q2/SUM($D$2:$D$9)
Fill down through row 6.
| Rating | Count | Share of valid answers |
|---|---|---|
| 1 | 0 | 0.0% |
| 2 | 1 | 14.3% |
| 3 | 1 | 14.3% |
| 4 | 3 | 42.9% |
| 5 | 2 | 28.6% |
Distribution counts sum to seven. Multiplying each rating by its count gives 0 + 2 + 3 + 12 + 10 = 27, matching the valid-score total. Counts for four and five give 3 + 2 = 5, matching the positive numerator. Rounded percentage displays may not sum exactly to 100%.
The combined mean is 27/7 = 3.86, and the combined positive share is 5/7 = 71.4%. Use combined numerators and denominators, not a simple average of segment percentages. Question completion among submitted records is 7/8 = 87.5%; that is not the invitation response rate.
Bring the workbook or Google Sheet with structured ratings, segment labels, and any manually coded response fields. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for customer service teams for more on reviewing your team's files.
A blank means the question was unanswered, unless the survey explicitly defines another meaning. Exclude it from that question's score denominator and report the missing count separately. Entering zero creates a score outside a one-to-five scale and depresses the average.
You also need the eligible invitation population and rules for duplicates, partial submissions, and exclusions. Submitted response rows alone cannot provide that denominator. Keep invitation response rate separate from the share of submitted records that answered a particular question.
Only after people code them into documented categories or numeric indicators. Keep the original comment, coding definitions, and review decisions together. The workflow here analyzes structured and manually coded fields; it does not imply automatic sentiment classification or text coding.