Managing and Analysing Responses – Google Forms (Senior 6 ICT) | Ganzaa

Google Forms

Lessons

Senior 6 · ICT

Google Forms

4 lessons

Lesson 4 of 4

Managing and Analysing Responses

25 min

From answers to a decision

The career-day form from lesson 1 has closed. The prefects now have 600 rows of data and three questions to answer by Friday: how many students chose each session, so each room is big enough; which topics students want next year; and whether last year's career day was worth repeating. Nobody is going to read 600 rows. The answers come from counting, averaging and charting, and from keeping the data safe while you do it.

Definition

Response spreadsheet: a Google Sheet linked to a form, in which each response becomes one new row and each question one column. The first column, Timestamp, records when the response was submitted.

Data cleaning: correcting or standardising data before analysis, for example removing extra spaces or making "s6 mpc" and "S6 MPC" the same value.

Pivot table: a table that groups rows by one column and summarises another, for example the number of students and the mean rating for each session.

The Responses tab

View or control	What it does
Summary	A chart for every question: pie charts for multiple choice, bar charts for checkboxes and scales, and lists for text answers.
Question	All the answers to one question, grouped; useful for reading comments or marking one paragraph question for the whole class.
Individual	One response at a time, as the respondent saw it; used to mark quizzes or delete a single bad response.
Accepting responses switch	Closes the form. You can write the message people see, such as "Registration closed on Friday".
Link to Sheets	Creates a new response spreadsheet, or adds a tab to an existing one. New responses then appear in it automatically.
⋮ menu	Get email notifications for new responses, download responses as a `.csv` file, unlink the form, delete all responses.

Warning

Leave the response tab alone; analyse on another tab. Do not sort, insert columns into or type over the linked response sheet. New responses are written into it by position, and rearranging it can mix up rows. Add a new tab, for example Analysis, and refer to the response columns from there.

From answers to a decision: the responses arrive in a linked tab that you leave alone, formulas on a separate tab count them, and only the summary is shared. The bars show the session counts of Worked example 1.

Step by step: link the responses to Sheets

Open the form and go to Responses. Click Link to Sheets (the green spreadsheet icon).
Choose Create a new spreadsheet, name it Career Day 2026 responses, and click Create. Sheets opens with a tab called Form Responses 1.
Check the columns: Timestamp, then one column per question, in the order of the form. If you collect email addresses, they appear in column B and the questions move one column to the right.
Add a tab with the + at the bottom left and name it Analysis.

Below are the first ten rows of the response sheet, simplified. The data is illustrative, and the email column has been left out.

Row	A: Timestamp	B: Class	C: Session	D: Topics for next year	E: Usefulness (1–5)
2	2026-10-05 08:02	S6 MPC	Engineering	Robotics, Coding	4
3	2026-10-05 08:05	S5 PCB	Medicine	Health	5
4	2026-10-05 08:05	S6 PCB	Medicine	Health, Research	4
5	2026-10-05 08:11	S4 MEG	Banking	Business	3
6	2026-10-05 08:14	S6 MPC	Engineering	Coding	5
7	2026-10-05 08:20	S5 HEG	Law	Business, Research	2
8	2026-10-05 08:31	S4 MPC	Engineering	Robotics	4
9	2026-10-05 08:40	S6 MEG	Banking	Business, Coding	3
10	2026-10-05 08:52	S5 PCB	Medicine	Research	5
11	2026-10-05 09:07	S4 HEG	Law	Health	4

Notice column D. A checkbox question stores all the ticked options in one cell, separated by commas. That matters for counting.

Formulas for analysis

On the Analysis tab, these formulas answer the prefects' questions. A range written C2:C means "from C2 to the bottom of column C", so the results update as new responses arrive. The values after each arrow are the results for the ten rows above.

=COUNTA(C2:C)                          → 10   responses so far
=COUNTIF(C2:C, "Engineering")          → 3    chose Engineering
=AVERAGE(E2:E)                         → 3.9  mean usefulness
=AVERAGEIF(C2:C, "Medicine", E2:E)     → 4.67 mean for Medicine (to 3 s.f.)
=COUNTIF(D2:D, "*Coding*")             → 3    ticked Coding
=COUNTIFS(B2:B, "S6*", C2:C, "Law")    → 0    S6 students who chose Law

The wildcard * in "*Coding*" matches any text before and after, so a cell holding "Robotics, Coding" is counted. Without the stars, COUNTIF would count only cells that hold exactly "Coding" and would give 1 instead of 3.

Cleaning, grouping and charting

=UPPER(TRIM(B2))                       "  s6 mpc " → "S6 MPC"
=SUBSTITUTE(UPPER(TRIM(B2)), " ", "")  "S6 mpc"    → "S6MPC"
=UNIQUE(C2:C)                          lists each session once
=QUERY(A1:E, "select C, count(C), avg(E) where C is not null group by C", 1)

TRIM removes extra spaces; UPPER makes the case the same; SUBSTITUTE removes the inner space as well. QUERY builds a whole summary table in one formula: one row per session, with its count and mean rating. A pivot table (Insert → Pivot table) does the same job without a formula: choose Session as the row, then add Session as a value summarised by COUNTA and Usefulness summarised by AVERAGE.

To chart a summary, select it and choose Insert → Chart. A bar chart compares sessions; a pie chart is only suitable when the parts add up to one whole, as with "one session per student".

Simpletivity: How to Connect Google Forms to Google Sheets (Link Responses). Watch how the link is made from the Responses tab and how a new response appears as a new row; then repeat it with your own form.

Worked example 1: room planning from the data

Suppose the full data gives these session counts out of 600: Engineering 186, Medicine 171, Banking 98, Law 77, Agriculture 44, Tourism 24. The largest room seats 150 and the others seat 120. Which sessions need a change?

Check the total: $186 + 171 + 98 + 77 + 44 + 24 = 600$ , so no response is missing.
Engineering (186) and Medicine (171) are both larger than the biggest room (150).
Each needs two sittings, or a hall. Engineering in two sittings is $\frac{186}{2} = 93$ students each, which fits a 120-seat room.
The other four sessions fit in 120-seat rooms.

The decision comes from one COUNTIF per session, not from reading 600 rows.

Worked example 2: is the mean telling the truth?

The mean usefulness of last year's career day was 3.9 on the ten rows above. The prefects want to report "students rated it 3.9 out of 5". What else should they check?

Who answered? 4 of the 10 rows are S6, 3 are S5 and 3 are S4. S1 to S3 students did not attend last year, so the question should only count students who did. A mean over the wrong group misleads.
The spread. The ratings run from 2 to 5. One rating of 2 (row 7) pulls the mean down; with only ten rows, one person matters a lot.
By group. AVERAGEIF gives 4.67 for Medicine. If one session scored much lower than the rest, the overall mean hides it.

A fair report says: "Of the students who attended last year, the mean rating was 3.9 out of 5 (range 2 to 5, $n = 10$ in the sample)".

Keeping the data safe

Share the summary, not the raw sheet. The response sheet holds names and email addresses. Give the head teacher a chart or a summary tab, not edit access to every row.
Respondents' view. Settings → Presentation → View results summary lets every respondent see the charts and the text answers of others. Leave it off whenever answers are personal.
Close and delete. Switch off Accepting responses after the deadline. When the event is over and the report is written, delete the personal data you no longer need. Rwanda's Law N° 058 of 2021 relating to the protection of personal data and privacy expects personal data not to be kept longer than its purpose requires.
Download a copy before deleting anything you must keep, using Download responses (.csv), and store it where only the right people can open it.

Tip

Write the question you want answered first, then the formula. "How many S6 students chose Law?" turns straight into COUNTIFS(B2:B,"S6*",C2:C,"Law"): one condition for each "who" and "what" in the sentence.

✦

Hands-on: from form to report

Link your Science Club or quiz form from earlier lessons to a new spreadsheet. If it has few responses, ask ten classmates to fill it in.
On an Analysis tab, write formulas for: the total number of responses; the count for each option of one multiple-choice question; the number who ticked one checkbox option; and one mean.
Build a pivot table that answers one question by group, and chart it.
Write a report of at most 120 words for the club patron, giving the numbers and one decision they support.
Share the report tab only, as Viewer, and close the form.

Check: the work is complete when every number in the report can be traced to a formula, and nobody outside the organisers can open the raw responses.

Practice

Name the three views of the Responses tab and say when you would use each.
Why should analysis be done on a separate tab rather than on Form Responses 1?
Column D holds checkbox answers such as "Health, Research". Write a formula that counts the responses that include "Health".
Using the ten rows in this lesson, find =COUNTIF(C2:C, "Banking") and =AVERAGEIF(C2:C, "Law", E2:E).
Explain why =COUNTIF(D2:D, "Coding") gives a smaller answer than =COUNTIF(D2:D, "*Coding*") on the same data.
A form asked "Class" as a short answer. The sheet holds "S5 HEG", "s5 heg", " S5 HEG" and "S5HEG". Write a formula that turns all four into the same value.
A prefect wants to switch on View results summary on a form that asked students about bullying. Evaluate the idea.

Show answersHide answers

Summary: charts of every question, for a quick overview. Question: all answers to one question, for reading comments or marking one question for everyone. Individual: one response at a time, for marking a whole quiz or deleting one bad response.
New responses are written into the linked tab by position; sorting it or inserting columns can break the link between rows and answers. A separate tab can refer to the data without changing it.
=COUNTIF(D2:D, "*Health*")
Banking: rows 5 and 9, so 2. Law: rows 7 and 11, ratings 2 and 4, so $\frac{2 + 4}{2} = 3$ .
Without wildcards, COUNTIF counts only cells that contain exactly "Coding" (row 6 only, 1 cell). With * before and after it also counts cells where Coding is one of several ticked options (rows 2, 6 and 9, 3 cells).
=SUBSTITUTE(UPPER(TRIM(B2)), " ", ""), which gives "S5HEG" for all four. Better still, replace the short answer with a dropdown so the problem never arises.
It should stay off. Respondents would see other students' text answers, which may name victims or bullies and are sensitive personal data. Share a summary with the teacher responsible instead.

1From answers to a decision

2The Responses tab

3Step by step: link the responses to Sheets

4Formulas for analysis

5Cleaning, grouping and charting

6Keeping the data safe

7Hands-on: from form to report