Analyse a Google Forms Likert survey without misleading averages
In this article (7 sections)
Related programme: the Data Analytics with Generative AI course (3–4 months; ₹90,000 (inclusive of GST) · EMI from ₹4,000/month).
To analyse a Likert survey from Google Forms, reshape the grid columns into one row per response and statement, map the answer labels to 1–5, reverse-code any negatively worded statement, and report for each statement the number who answered and the percentage favourable (4–5), neutral and unfavourable. Show averages only as a secondary figure, and hide any department cut with too few responses to be anonymous.
The request as it usually arrives
HR ran a pulse survey with a five-point agree/disagree grid, and the department heads want "the scores" by Friday. The response sheet has a timestamp, a department and one column per statement, each full of words such as "Agree" and "Neutral". The quick answer is to replace the words with numbers and average each column. That gives one number per statement, and it can mislead in three ways, all fixed below.
From export to a tidy table
Google Forms writes a multiple-choice grid as one column per grid row, with the statement inside square brackets in the header. The script takes that bracketed text as a short item name and melts the sheet from wide to long. Every later step then works the same way for three statements or thirty. The data is synthetic: thirteen invented responses from three departments.
import io
import pandas as pd
# Synthetic export in the shape Google Forms gives a multiple-choice grid:
# one column per grid row, named "Question [row]".
csv = """Timestamp,Department,How far do you agree? [My manager gives useful feedback],How far do you agree? [I have the tools I need],How far do you agree? [I often think about leaving]
26/09/2026 10:02:11,Sales,Agree,Disagree,Strongly disagree
26/09/2026 10:05:40,Sales,Strongly agree,Neutral,Disagree
26/09/2026 10:11:02,Sales,Agree,Agree,Neutral
26/09/2026 10:20:18,Sales,Neutral,Disagree,Agree
26/09/2026 10:31:55,Sales,Agree,Strongly disagree,Disagree
26/09/2026 11:00:09,Accounts,Disagree,Agree,Agree
26/09/2026 11:04:47,Accounts,Strongly disagree,Agree,Strongly agree
26/09/2026 11:12:30,Accounts,Neutral,,Agree
26/09/2026 11:40:02,Operations,Agree,Agree,Disagree
26/09/2026 11:41:15,Operations,Strongly agree,Strongly agree,Strongly disagree
26/09/2026 12:15:00,Operations,Agree,Neutral,Disagree
26/09/2026 12:20:44,Operations,Agree,Agree,Neutral
26/09/2026 12:21:03,Operations,Agree,Agree,Neutral
"""
raw = pd.read_csv(io.StringIO(csv))
# 1. Short item names from the bracketed part of each header.
items = {c: c.split("[")[1].rstrip("]") for c in raw.columns if "[" in c}
long = (raw.rename(columns=items)
.melt(id_vars=["Timestamp", "Department"], var_name="item", value_name="answer"))
# 2. Map labels to scores. Unknown labels raise instead of silently becoming blank.
SCALE = {"Strongly disagree": 1, "Disagree": 2, "Neutral": 3, "Agree": 4, "Strongly agree": 5}
unknown = set(long["answer"].dropna()) - set(SCALE)
assert not unknown, f"labels not in scale: {unknown}"
long["score"] = long["answer"].map(SCALE)
# 3. Reverse-code the negatively worded item so that 5 is always the good end.
NEGATIVE = {"I often think about leaving"}
neg = long["item"].isin(NEGATIVE)
long.loc[neg, "score"] = 6 - long.loc[neg, "score"]
# 4. Per item: answered n, favourable (4-5), neutral, unfavourable (1-2), and the mean.
answered = long.dropna(subset=["score"])
summary = answered.groupby("item")["score"].agg(
n="size",
favourable=lambda s: round((s >= 4).mean() * 100),
neutral=lambda s: round((s == 3).mean() * 100),
unfavourable=lambda s: round((s <= 2).mean() * 100),
mean="mean").round({"mean": 2})
print(summary.to_string())
# 5. Department cut, hiding any group with fewer than 4 answers for that item.
MIN_N = 4
by_dept = answered.groupby(["item", "Department"])["score"].agg(
n="size", favourable=lambda s: round((s >= 4).mean() * 100)).reset_index()
by_dept.loc[by_dept["n"] < MIN_N, "favourable"] = pd.NA
print(by_dept.pivot(index="item", columns="Department", values="favourable").to_string())
assert summary.loc["I have the tools I need", "n"] == 12 # one blank, not a zero
assert summary.loc["I often think about leaving", "favourable"] == 46Three decisions in the script
Refuse unknown labels. If the form was edited halfway through and "Somewhat agree" appeared, a plain map would turn it into a blank without complaint, and those people would silently drop out of every figure. The assert stops the run and names the label, so someone decides where it belongs.
Blanks are not zeros. One Accounts respondent skipped "I have the tools I need". That item's n is 12, the others' 13. Filling the blank with 0 or 3 would invent an opinion. Every percentage is computed over people who answered that statement, and n is printed beside it so a reader can see the base.
Reverse-code the negative statement. On "I often think about leaving", 5 is the worst answer. The score becomes 6 minus the answer, so "Strongly disagree" scores 5. After this, favourable means the good outcome on every row: 46% of respondents said they do not often think about leaving.
Reading the results
| Statement | n | Favourable | Neutral | Unfavourable | Mean |
|---|---|---|---|---|---|
| My manager gives useful feedback | 13 | 69% | 15% | 15% | 3.62 |
| I have the tools I need | 12 | 58% | 17% | 25% | 3.33 |
| I often think about leaving (reversed) | 13 | 46% | 23% | 31% | 3.23 |
The means of the last two differ by only 0.10, which looks like two similar results. The favourable figures differ by 12 points, and on "leaving" nearly a third answered on the unfavourable side. This is why the percentages lead and the mean follows. Percentages can also be explained to anyone in one sentence, while "3.23 out of 5" invites the question "is that good?"
Rounding means the three percentages in a row can add to 99 or 101. That is expected; note it under the table rather than adjusting a figure.
The department view, with small groups hidden
Accounts has three respondents, so its column is blank for every statement in the second printout. The threshold of four is used here only because the example is small. Choose the real minimum with HR before the survey opens, write it in the survey invitation, and apply it to every cut, including combinations like department and location, which shrink groups quickly.
Operations shows 100% favourable on manager feedback from five answers. With a group that small, one person changing their answer moves the figure by 20 points. Put n in every cell or column header of the department table.
In Google Sheets or Excel
With labels in B2:B200 for one statement, the same figures come from:
Answered =COUNTA(B2:B200)
Favourable % =(COUNTIF(B2:B200,"Agree")+COUNTIF(B2:B200,"Strongly agree"))/COUNTA(B2:B200)
Unfavourable% =(COUNTIF(B2:B200,"Disagree")+COUNTIF(B2:B200,"Strongly disagree"))/COUNTA(B2:B200)
By department =IF(COUNTIFS(A2:A200,"Sales",B2:B200,"<>")<4, "n<4",
(COUNTIFS(A2:A200,"Sales",B2:B200,"Agree")+COUNTIFS(A2:A200,"Sales",B2:B200,"Strongly agree"))
/COUNTIFS(A2:A200,"Sales",B2:B200,"<>"))For a negatively worded statement, swap the favourable and unfavourable labels instead of rescoring. Check that the label spelling in the formula exactly matches the form's options.
What not to conclude
A pulse survey tells you what the people who replied chose to say that week. Report the response rate alongside the results, avoid comparing a 13-person team with last quarter's 40-person survey as if they measured the same thing, and treat a single survey's movement of a few points as noise until it repeats.
Survey analysis is a compact exercise in the judgement analysts use everywhere: choosing the base, handling blanks honestly and refusing to overstate small numbers. We work through it with real-shaped, synthetic HR data in our Data Analyst course, taught live online with optional in-person labs at our Noida centre. If you are not sure which of these skills you already have, the free skill check gives a quick read.
Frequently asked questions
Should I report the average of a Likert item?
You can show it as a secondary figure, but lead with the percentage who agreed (4 or 5 on a five-point scale). A mean of 3.3 hides whether most people were neutral or the team was split between strongly agree and strongly disagree, and a manager acting on the survey needs to know which.
Why hide departments with only a few responses?
In a department of three, a percentage can point to one person's answer. Hiding small groups protects respondents who were promised anonymity, and it also stops a single answer from swinging a figure by 33 points.
Do I have to reverse-code negative statements?
If you combine items or compare them, yes. For "I often think about leaving", agreeing is the bad outcome. Scoring it as 6 minus the answer puts every item on the same direction, so a higher score always means a better result.
What does Google Forms export for a grid question?
The linked response sheet or CSV gives one column per grid row, with headers like "Question [row text]", and the answer label as text in each cell. The script turns those into a long table with one row per person and statement.
Pankit Kumar has 10 years in Data Science & AI, building and shipping production systems in regulated pharma and clinical environments. He is a freelance trainer at Boston Institute of Analytics, AnalytixLabs and Scaler, and has taught this material to thousands of working professionals.
This article is part of our Data Analytics with Generative AI programme — 3–4 months. The full analyst stack — Excel, SQL, Power BI and Python pipelines — then a generative-AI layer you can prove is right.
Explore Data Analytics with Generative AI