Did you know that seven rows in a class survey need not mean seven students answered? A second form submission, a pasted row or a student choosing multiple allowed options may all appear as an extra line. Those three situations should not be treated as the same problem.
To find duplicate responses without deleting real data, first define what one row is supposed to represent, then check a safe response identifier and the question design, review suspected matches, and keep the original records. Only remove a row from the counting view after establishing that it is genuinely a duplicate under the survey’s rules. Distinct valid responses must stay.
Start with the unit of counting
A survey can count students, submitted forms, selected options or events. These are not interchangeable. ‘How many students chose reading as their favourite activity?’ usually asks for one valid choice per participating student. ‘Which activities do you do each week? Select all that apply’ permits several choices from one person. Counting the second survey as if one choice equalled one student creates a false total.
Before using spreadsheet tools, read the form instructions. Did the teacher intend one response per student? Were students allowed to edit previous answers? Was the same activity assessed at two different times? A class identifier may be useful in an approved classroom dataset, but names and other personal information are unnecessary for learning how to audit rows. Do not publish raw student-level data.
Worked example: seven rows, six fictional students
Imagine a made-up classroom questionnaire asking each participant to choose exactly one favourite activity. For teaching purposes only, the response records are: A01–Reading; A02–Sports; A03–Art; A02–Sports; A04–Reading; A05–Science; A06–Reading. The student codes are invented and do not identify real children.
The uncorrected sheet contains seven rows. The repeated A02–Sports entry could be a double submission because both the code and answer match. After the teacher checks the form settings, response time and original entries, suppose it is confirmed that A02 submitted the same answer twice for the same survey. The corrected one-response-per-student view contains six students: Reading 3, Sports 1, Art 1 and Science 1. The counts add to 3 + 1 + 1 + 1 = 6.
Now compare the claim ‘half the respondents chose reading’. With the verified six student responses, 3 ÷ 6 = 0.5 = 50%. If someone had divided the same reading count by seven rows, they would have reported about 42.9%. The calculation was not the root error: the wrong denominator came from treating entries as distinct people.
An important twist: if the second A02 row had said ‘Art’ instead of ‘Sports’, the teacher could not simply delete it. It might be an intentional correction, a response from a different time or a record error. Check the collection rules and an authorised audit trail before deciding which answer, if any, should count.
A safe six-step audit before drawing the chart
- Write the counting rule. For example: one final valid response per student for one named class survey.
- Preserve the original. Duplicate the worksheet or work in a separate analysis tab; do not destroy the source.
- Check the proposed key. An anonymous response code may help; two students can share a name, and identical answers alone are not duplicates.
- Inspect candidate matches. Compare form version, time, edit history and allowed number of responses, where authorised and available.
- Mark the decision. Use an audit column such as ‘keep’, ‘duplicate confirmed’ or ‘needs teacher review’, with a short reason.
- Recount the retained records. Verify category totals and the denominator before calculating percentages or drawing a chart.
In a simple spreadsheet, filtering rows by code can help surface matching entries. An apparent duplicate flagged by conditional formatting is a candidate for review, not permission to delete. If codes are missing or two students have the same code, stop the automatic merge and ask the teacher how to resolve identity safely.
Three common lookalikes that are not necessarily duplicates
Identical answers from different students: A03 and A07 both selecting ‘Art’ represent two students when their response identifiers are genuinely different. Removing one because the activity matches would erase legitimate data.
Multiple selections from one student: In a select-all-that-apply survey, A01 might choose Reading and Science. That can be two valid option selections but still only one participating student. The numbers answer different questions, and option counts can add to more than the participant count. See our earlier guide to survey percentages over 100%.
Repeated measurements: Three temperature readings from one experiment are not three different experimental units. Keep the readings but label their role. The same principle applies to attendance observations on three different days: the event can repeat without the student being duplicated as a person.
What should students and teachers check before trusting a graph?
Students can put three figures under any chart: number of rows received, number of distinct valid participants and number of options counted. These may match in a one-choice survey but diverge in other designs. Give the axis and caption the correct wording—’students’ if counting people, ‘selections’ if counting ticks.
Teachers can model a disputed row and ask pairs to defend a keep-or-exclude decision from the survey instructions. A useful assessment is not ‘Which spreadsheet button did you use?’ but ‘Why is this one observation counted as one learner?’ Parents can ask their child to explain the denominator rather than pressing for a tidy-looking pie chart.
Professional data-cleaning practice also begins by defining a key and inspecting suspected matches. For a formal illustration, see the CDC’s description of its duplicate-record review tool. A classroom does not need specialist software to learn the central habit: flag, investigate, document, then count.
Observe progress with a fresh dataset
Give the learner a new, fabricated eight-row dataset containing one genuine repeated submission, one identical answer from a different respondent and one changed response requiring teacher review. Ask for three outputs: the untouched original, a reasoned audit table and a correctly labelled total. Improvement means the learner no longer deletes legitimate records or confuses row totals with people.
This method has limits. A response code may be unreliable, anonymous forms may not allow distinct-person counts, and survey consent or privacy rules may restrict any linking of records. In such cases report the uncertainty rather than claiming an exact unique-participant count.
Frequently asked questions
Can I just click ‘Remove duplicates’ in a spreadsheet?
Not before defining what a duplicate means. The feature may remove legitimate repeated measurements or select-all-that-apply options, and it may not detect a second submission that contains slightly different text. Work on a copy and review candidates.
Do matching answers prove two rows belong to one student?
No. Two people can give the same answer. The key is evidence that both entries refer to the same intended response unit under the form’s rules.
Why do the percentages change after removing one confirmed duplicate?
Because both counts and the denominator may change. In the fictional example, Reading stays at three, while the denominator changes from seven rows to six unique students; 3/6 is 50%.
Good conclusions begin with an honest count
A reliable survey tells readers not merely what appears on the screen, but what was counted. Our Core Aim guide to checking tables before graphing continues from here. The broader eduKate education library connects careful checking to independent learning, while the unchanged Clementi tutorial benchmark models why explanations and verified working matter more than a quick answer.