Did you know a spreadsheet can accept every number in a class-results table and still calculate the wrong average? A green-looking worksheet is reassuring, but an input rule only answers one narrow question: does this entry meet a specified condition? It cannot tell us whether someone copied the right number from the original record.
Data validation is a rule that limits or warns about the kind of information entered into a cell—for example, whole-number marks from 0 to 100. Data verification is a separate check that the recorded value matches the trustworthy source. To protect a school project’s conclusion, use both: validation to catch impossible entries, and independent source checking to catch plausible but wrong ones.
Quick answer for students, parents and teachers:
- Set a sensible rule for the field: number, range, date or allowed label.
- Keep the original source available and traceable, without exposing other pupils’ private information.
- Independently compare entered values against that source, especially values driving a decision.
- Recalculate totals and investigate discrepancies rather than silently changing figures.
- Record what was checked, what remains uncertain and when a correction was made.
A diagnosis before another spreadsheet tutorial
Ask a learner to consider two entries in a marks column: 107 and 87. The worksheet only accepts whole numbers between 0 and 100. Which one should it reject? Most learners correctly say 107. Now add that the student’s actual mark on the source sheet was 78. Should the worksheet accept 87? It may accept it because 87 is inside the permitted range. Yet accepting it does not make it true.
This is the central diagnostic. If a learner says ‘the spreadsheet approved it, so it must be right’, the gap is not arithmetic. It is a misunderstanding of what the software has tested. Ask them to name the exact test: admissible value, not correspondence with evidence.
How validation works—and what it cannot see
A validation rule is usually applied to an input cell or range of cells. For example, the teacher might specify ‘whole numbers from 0 through 100’. Values such as 34, 78 and 100 satisfy the rule; 101 does not. A list restriction might allow only ‘present’, ‘absent’ and ‘late’. The rule checks membership or type. It does not examine a paper register, a student’s original calculation, or the meaning of a photographed observation.
There is also a technical limit. Microsoft’s official Excel guidance explains that validation alerts are principally for direct typed entries; copying, filling, formulas or macros may produce invalid values without the same alert. Existing invalid values may need a separate check. So ‘I configured a rule’ is not the same claim as ‘all data have been audited’. See Microsoft Support: More on data validation and Display or hide circles around invalid data. Exact controls vary by application and version.
A worked example: a perfectly plausible nine-mark mistake
Imagine an entirely fictional school research group recording four Mathematics quiz scores, each out of 100. The original, checked source records are 78, 64, 91 and 70 marks. The spreadsheet operator mistakenly types 87, 64, 91 and 70 marks. Every number is a whole number within 0–100, so the validation rule has no reason to object.
Check the source total
The correct total is 78 + 64 + 91 + 70 = 303 marks. For four scores, the correct mean is 303 ÷ 4 = 75.75 marks. You can check the addition in two pairs: (78 + 64) = 142 and (91 + 70) = 161; 142 + 161 = 303.
Check the typed total
The mistyped total is 87 + 64 + 91 + 70 = 312 marks. The displayed mean is 312 ÷ 4 = 78 marks. The difference is 312 − 303 = 9 marks in the four-score total, or 9 ÷ 4 = 2.25 marks in the mean. Both means are numerically reasonable. Only a comparison with the source reveals which one is justified.
These are invented teaching figures, not reported pupil outcomes. The choice of four small scores makes the mechanism easy to hand-check. With a larger data set, a single error may be harder to notice in an average and still matter for a cutoff or a school-project claim.
Why a total check alone is not enough
If you re-add the typed numbers and obtain 312, you have verified the arithmetic inside the spreadsheet—not the correctness of the entries. That is still useful: it catches incorrect formulas and omitted rows. But it would not detect that 87 should have been 78. The stronger practice is two independent layers: reconcile each important input to the original record, then independently recalculate the formula.
Another danger is a swapped pair. Suppose an operator enters two wrong values that cancel in the grand total. The sum can be correct while two individual records are wrong. This matters whenever the conclusion concerns individual categories, thresholds or changes over time. Verification should therefore be driven by the question the data are being used to answer.
A calm three-pass workflow that students can actually use
- Pass 1: Define the rule. Decide whether entries are whole numbers, decimal measurements, allowed words or dates. State units and any permitted missing-value code before collecting data.
- Pass 2: Compare with evidence. Read back an entered number against the original paper sheet, measurement notebook or approved digital export. Have a second person check the high-impact entries when practical.
- Pass 3: Test the interpretation. Hand-check one worked total, one mean and any threshold that changes the conclusion. Write down discrepancies and fix them transparently.
For a classroom experiment, preserve measurement units. A length of 12 centimetres typed as 12 millimetres passes many ‘positive number’ rules but changes the physical meaning by a factor of ten. A small field labelled ‘unit’ and a source-backed measurement procedure may be more valuable than another decorative chart.
Guided-to-independent practice
Guided practice: detect the problem
A teacher shows a worksheet with permitted mass values from 0 to 500 grams. The notebook reads 124 g; the sheet reads 142 g. Ask: ‘Which test passes? Which check fails?’ Answer: the range rule passes, because 142 is permitted; the evidence comparison fails, because the recorded observation was 124 g. The discrepancy is 18 g, but that difference should not be described as an experimental change—it is a transcription error in this illustrative scenario.
Supported practice: identify the missing control
A parent and learner inspect a shopping-budget sheet. Unit prices are restricted to positive numbers. A receipt states $3.60, but the sheet states $6.30. Ask the learner to propose two controls. One is comparing the price to the receipt; the other is checking the formula that multiplies price by quantity. They solve different problems.
Independent transfer: build your own checking card
Let the learner create six fictional input cells for a club attendance table, set a permissible range, deliberately insert one within-range mistake, and ask a partner to find it using a separately kept source list. Then switch roles. Success is not simply finding the error: the learner must explain why the rule did not prevent it.
What progress should look like
After one lesson, a learner should be able to name the difference between ‘allowed’ and ‘correct’, identify at least one false-but-allowed input, and independently correct a total. After several short exercises, look for a habit of keeping the source record visible and describing exactly what was checked. A teacher can mark these actions as observed, still developing, or not yet demonstrated. A parent can ask a useful question: ‘What source would show that this number is right?’ rather than ‘Did the computer accept it?’
Common misconceptions and limits
- Misconception: A data-validation alert proves correctness. Correction: it tests a configured condition, not the source record.
- Misconception: Correct formulas imply accurate inputs. Correction: a perfectly formed calculation can process a mistyped value.
- Misconception: One spot-check proves every cell is accurate. Correction: sampling helps but cannot establish that all unchecked records are correct.
- Misconception: More decimal places mean greater certainty. Correction: display precision does not repair uncertain measurements or data entry.
A complete audit may be unrealistic for thousands of rows, and independent source records may themselves contain errors. State the extent of the check and any unresolved doubts. Handle personal marks and attendance privately: use fictional teaching data in public classwork, and apply school-approved access rules to real learner records.
Frequently asked questions
Should a teacher remove validation if it is imperfect?
No. It catches many useful mistakes and makes the data structure clearer. The point is to add verification, not replace one imperfect process with another.
If a cell is within range, why is it still worth checking?
Because being possible does not mean it happened. A plausible transposition, wrong unit or wrong person’s record can satisfy a numeric range.
Does checking the average independently solve the problem?
Only partly. It can catch formula errors. It cannot establish that the underlying observations were recorded faithfully.
What if the source is missing?
Label the entry as unverified or missing according to the project’s documented convention, rather than inventing a value that makes the graph tidy. Seek the original evidence where possible and discuss how the unknown affects the conclusion.
Explore the wider learning method
The same distinction appears in Mathematics: an answer can follow a valid calculation format but still use the wrong starting quantity. Our verified guide to copying spreadsheet formulas without moving fixed references focuses on a different kind of control. Another guide explains why filtered rows change the meaning of spreadsheet totals. Both reward independent checking rather than trusting appearance.
For the broader teaching philosophy, see the eduKate Education learning hub and our Secondary 1 Mathematics learning approach in Clementi: diagnose the exact misunderstanding, explain the mechanism, practise progressively, then ask the learner to check without prompts.
The habit worth keeping
A good spreadsheet is not one that never displays an error message. It is one whose numbers can be traced to evidence, whose formulas can be explained, and whose conclusions survive an independent check. That is what makes a classroom calculation useful outside the classroom.
