Free text = chaos in the survey
When someone writes Apple, apple, APPLE, the chart has three columns. Data validation offers a drop-down list: only roll, apple, bar, nothing. An error with other text — or a warning.
The Sorter stops dirt before the filter and COUNTIF.
A list of survey answers
-
Sheet
Pruzkumor a newFormular. Column AAnswer. Heading A1. -
On
Ciselnikor in column G1:G4 write four allowed answers (lowercase, no extra spaces). -
Select A2:A50 (where people will write). Data → Data validation.
-
Criterion: List from a range. Range:
G1:G4orCiselnik!A2:A5. Tick Reject input on an invalid value. -
Click A2 — there should be a down arrow. Pick
apple. Try writingbanana— the sheet rejects it or warns.
Validation works when you type into cells by hand or share the sheet to fill in. A Google Form has its own choice — validation in the workbook is for a table you copy into or where a teammate writes.
Validation on grades
Select B2:B6. Validation: Number between 1 and 5 (or 1–3 according to the school). Whoever writes 6 or two gets an error. Grades stay numbers for the average and the chart.
What often goes wrong
A list with an extra space. apple in G3 — validation different from COUNTIF. Trim the text.
Validation on the whole column A:A. Slow, it also validates empty rows under the data. A2:A50 is enough.
A list without an update. You add a fifth answer — expand the range G1:G5 in the validation settings too.
🏫 Grade average — 1 to 5
Validation B2:B6 on whole numbers 1–5. Then the average does not fall on the text “three”. The chart gets only numbers.
💰 Pocket money — positive numbers
B2:C8 validation Number ≥ 0 (or allow minus only in D with a formula). Came in and Spent must not be fifty.
📊 Class survey — clean input
Four answers in the lookup list = four rows in the summary D:E = four columns in the chart. No surprises.
🏆 Mini dashboard — validation at the source
The dashboard does not validate — validation on Znamky, Kapesne, Pruzkum. Bad data does not get into the summary.
Now you 💪
First: a list of four snacks + validation A2:A20.
Second: grade validation 1–5 on B2:B6. Try a wrong value on purpose.
Check: The drop-down list works. An invalid value does not pass. The list matches the COUNTIF categories.
What to take from this lesson
- Validation = a list or a number range in the cell.
- Keep the list on a lookup sheet, keep the range small (A2:A50).
- Fewer text variants = less clean-up for the Sorter.
© 2026 Ing. Martin Polak / AlgoRhino · コンテンツ利用規約