Survey Analysis Sheet

About this template

When to use it

Survey responses stacked one per row, with a summary page in front. Three sheets — Dashboard, Input data, Usage guide — and only Input data takes typing. The intro line in B3 of the Dashboard describes the template rather than your survey, so replace or delete it before circulating.

What the dashboard holds

Two blocks calculate. Key metrics in B7:E7 — rows entered, rating total, rating average, rating max — read a single column: D on Input data, the 1–5 rating. Summary by respondent in B12:F13 groups the same column by the Respondent label in column F. The third block, Response distribution (B17:C20), is typed in by hand and linked to nothing.

Filling order

1. Rows 2–21 of Input data take Response ID, Question no., Question, Rating (1-5), Response date, Respondent, Notes (A–G). Only six sample rows ship; the ranges run to row 21, so there is room for twenty. Note that a row is one answer, not one respondent — five questions answered by twenty people needs a hundred rows.
2. Column D takes scale numbers only, with blanks for no answer (note 5). Words such as “Very satisfied” drop out of every total.
3. The respondent labels on the dashboard are Existing customer (B12) and New customer (B13). The formulas use those cells as their criterion, so retyping the label re-targets them — but it has to match column F character for character.
4. The Response distribution block holds four options — Very satisfied 34, Mostly satisfied 41, Neutral 18, Dissatisfied 12. Use it for one question’s distribution and retitle the heading accordingly.

How the formulas calculate

• Count, sum, average and maximum all run over ‘Input data’!$D$2:$D$21, with IFERROR around the average and maximum.
• The respondent block is =COUNTIF(‘Input data’!$F$2:$F$21,$B12), =SUMIF(…), =IFERROR(AVERAGEIF(‘Input data’!$F$2:$F$21,$B12,’Input data’!$D$2:$D$21),0) and a share of =IFERROR(C12/COUNT(‘Input data’!$D$2:$D$21),0).
• On a five-point scale the rating total means little; the average is the number to read, and the maximum only confirms that a 5 exists.
• =SUM(C17:C20) under the distribution is a valid 105 responses, but =AVERAGE(C17:C20) returns 26.25 — the mean of four option counts, which is not interpretable. For shares, add =C17/$C$21 beside each row.
• The ranges stop at row 21; widen $D$21 and $F$21 on the dashboard if you collect more.
• Nothing breaks the ratings down per question, even though Question no. is captured. Add it yourself: =IFERROR(AVERAGEIF(‘Input data’!$B$2:$B$21,”Q1″,’Input data’!$D$2:$D$21),0).

Examples

• Row 4 — R-003 · Q2 · “Was the response time acceptable?” · 3 · 2026-05-12 · Existing customer · “Mentioned wait time”.
• Twenty ratings averaging 4.1 show 4.1 in Rating average.
• A 34 / 41 / 18 / 12 distribution totals 105, so Very satisfied is 32.4%.

Common mistakes

• Writing “4 points” or a word such as “Satisfied” instead of the number, which puts the cell outside COUNT and AVERAGE.
• Entering 0 for no answer, which drags the average down; leave it blank so COUNT skips it.
• Adding a third respondent group to Input data without adding a row to the dashboard block — it will be missing from the summary while still counting in the shares.
• Typing free-text comments into the rating column — put them in Notes and keep column D numeric.