A form export is not a finished project
A Google Form sends answers to a sheet. Columns Timestamp, Answer, sometimes Name. In between duplicates, empty rows, Apple next to apple. Today a clean-up pipeline: import → trim → lowercase → duplicates → COUNTIF → SUM check.
The Sorter finishes the work the Chart maker needs for the chart.
Clean-up pipeline
-
Open the sheet with the form answers (or
Pruzkum). The column with the answer — say B. Copy the sheet →Pruzkum-cisty(a safety net). -
Helper column C
Clean answer. C2:=MALÁ(B2)(LOWER). Drag down. Spaces:=OŘEZ(B2)or TRIM before MALÁ. -
By hand unify obvious mistakes (
applevsapples→ one variant). Validation from the last lesson helps next time, not backwards. -
Duplicates by the respondent column (email or ID), not by the answer. Delete the second vote from the same person.
-
Summary D:E with
=POČET.KDYŽ(C:C;D2)(range C2:C500). SUMA(E) = number of respondents. Only now a chart from D1:E5.
Leave the raw sheet. Do the clean-up on a copy or in new columns C, D, E. When the teacher wants an audit, they will see original B and clean C.
Check questions
- How many rows in the raw export? How many after duplicates?
- SUM of counts = the second number?
- Is there an empty answer? Filter on empty — fix or throw the row out.
- Do the chart and the sentence under the chart use the same numbers from E?
What often goes wrong
Clean-up by deleting column B. You lose the audit. C = a clean copy, B stays.
COUNTIF on B instead of C. After MALÁ count C.
A chart before the SUM check. A wrong summary = a pretty lying chart.
🏫 Grade average — a different sheet
Do not mix the survey with Znamky. Two sheets, two clean-ups. Grades validate 1–5, the survey snack categories.
💰 Pocket money — not a survey
You do not import pocket money from a form — you write it yourself. Clean-up = checking empty days and duplicates of Mon.
📊 Class survey — the main project
This lesson is the whole survey story. When you collect for real, go through the pipeline live. Sentence: “Most common: … (X of Y).”
🏆 Mini dashboard — summary only
On the dashboard put the winner from D2 or MAX from E — not 22 names. The raw export stays hidden in the workbook.
Now you 💪
First: on a copy of the survey column C = MALÁ+TRIM. Count unique answers before/after.
Second: D:E + SUM. Compare with a handmade filter on one answer.
Check: SUM fits. Raw column kept. Chart from D:E matches the sentence under the chart.
What to take from this lesson
- Clean-up = trim, lowercase, duplicates by person, COUNTIF.
- Never delete the raw export — a copy or a new column.
- Chart only after the SUM check.
© 2026 Ing. Martin Polak / AlgoRhino · Content usage terms