Analyze coded survey ratings with valid-response counts, distributions, and segment comparisons. Follow a complete Excel example that keeps missing responses separate and shows exactly how the percentages reconcile.
To analyze Likert scale data in Excel, count the valid responses for each ordered rating and compare their percentages by customer segment. Keep missing and not-applicable responses separate. A distribution shows whether customers cluster around neutral, agree consistently, or split between opposite ends of the scale.
This workflow analyzes one survey statement. Combining several items into a validated scale requires additional measurement decisions; a tidy workbook alone does not establish that those questions measure the same thing.
Suppose a US business services company asks: “Our onboarding process met my needs.” It uses the same response options for Standard and Premium customers:
| Code | Response label |
|---|---|
| 1 | Strongly disagree |
| 2 | Disagree |
| 3 | Neither agree nor disagree |
| 4 | Agree |
| 5 | Strongly agree |
Keep these labels in the report. The codes preserve the order; they do not establish equal distances between adjacent opinions. For an individual item, show counts and percentages before considering a single summary score.
Use one row per respondent per question. Include a respondent reference, segment, and response code. If a form exports labels, map them consistently before counting; do not assign a code to an unrecognized answer just to fill the column. The general Excel analysis workflow covers preparing a consistent source table.
Put this illustrative export in A1:C19. The respondent references are synthetic. “Blank” below means an actually empty cell in the workbook; N/A is text, not a rating.
| Respondent | Segment | Response code |
|---|---|---|
| R01 | Standard | 1 |
| R02 | Standard | 1 |
| R03 | Standard | 2 |
| R04 | Standard | 3 |
| R05 | Standard | 4 |
| R06 | Standard | 4 |
| R07 | Standard | 4 |
| R08 | Standard | 5 |
| R09 | Standard | Blank |
| R10 | Premium | 2 |
| R11 | Premium | 3 |
| R12 | Premium | 4 |
| R13 | Premium | 4 |
| R14 | Premium | 5 |
| R15 | Premium | 5 |
| R16 | Premium | 5 |
| R17 | Premium | 5 |
| R18 | Premium | N/A |
In D1 enter Valid. In D2 enter the following and fill down:
=IFERROR(AND(ISNUMBER(C2),C2>=1,C2<=5,MOD(C2,1)=0),FALSE)
This accepts only numeric whole-number codes within the defined range. It rejects blanks, text, fractional codes, and out-of-range values. Investigate rejected values rather than silently converting them into neutral responses.
For the valid-response control total, use:
=COUNTIF(D2:D19,TRUE)
Microsoft's COUNTIF documentation explains counting cells against a criterion. Here, 16 rows are valid. The 18 input rows reconcile to 16 valid ratings, one blank, and one not-applicable response. Rejected values should have a reason recorded during review.
Build a summary with rating codes 1 through 5 in E2:E6, Standard in F1, and Premium in G1. In F2 enter:
=COUNTIFS($B$2:$B$19,F$1,$C$2:$C$19,$E2,$D$2:$D$19,TRUE)
Fill right and down through G6. Microsoft documents COUNTIFS as counting rows where all specified criteria are met. This formula requires the correct segment, rating, and validity flag together.
| Rating | Standard count | Premium count | Combined count |
|---|---|---|---|
| 1 | 2 | 0 | 2 |
| 2 | 1 | 1 | 2 |
| 3 | 1 | 1 | 2 |
| 4 | 3 | 2 | 5 |
| 5 | 1 | 4 | 5 |
| Valid responses | 8 | 8 | 16 |
In F7 use =SUM(F2:F6) and fill right to G7. These totals must match an independent count of valid rows within each segment:
=COUNTIFS($B$2:$B$19,F$1,$D$2:$D$19,TRUE)
Use the same approach for each survey item, with that item's own validity denominator. If the survey includes written comments, analyze their themes separately with the qualitative Excel workflow.
Divide each count by its segment's valid total. For Standard rating 1, use =F2/F$7; format as a percentage and fill down. Repeat for Premium.
| Rating | Standard share | Premium share |
|---|---|---|
| 1 | 25.0% | 0.0% |
| 2 | 12.5% | 12.5% |
| 3 | 12.5% | 12.5% |
| 4 | 37.5% | 25.0% |
| 5 | 12.5% | 50.0% |
| Total | 100.0% | 100.0% |
The displayed percentages are exact to one decimal. Each column must sum to 100%. Use a 100% stacked bar with the same rating order and colors for each segment; the chart-selection guide covers choosing a display that fits the question.
If the business defines favorable responses as codes 4 and 5, calculate Standard's favorable share with:
=(F5+F6)/F7
Standard is (3 + 1) / 8 = 50%; Premium is (2 + 4) / 8 = 75%, a difference of 25 percentage points. Combined favorable share is (4 + 6) / 16 = 62.5%. These are descriptive findings from small groups, not proof of a population difference or the reason behind it.
Covirage is not affiliated with Microsoft.
Bring your consistently coded survey workbook to Covirage, including segment labels and missing-response indicators. Covirage maps the columns; its tools compute and check the summaries, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See Excel analysis with Covirage for the workbook workflow.
Start with the full ordered distribution. Numeric codes identify response positions, but do not establish equal distances between them. If your organization also reports a mean, explain that assumption and show the distribution and valid-response count beside it.
No. Neutral is a selected response with its own label and code. A blank means the item was unanswered, while not applicable means something different again. Report those counts separately and exclude them from the valid-rating denominator.
Calculate each group's rating percentages using that group's valid responses. Show the counts beside those percentages so a small group is visible. For the combined result, divide combined rating counts by combined valid responses rather than averaging group percentages.