Did you know that two groups can share their science results correctly and still produce an inflated class total? It happens when the same recorded observation appears in more than one file. A tidy spreadsheet full of valid numbers is not necessarily a tidy collection of unique events. Before adding a class’s observations, students must decide what counts as one observation.
A duplicate record is a second copy of an observation that has already been recorded, not merely a row with the same numerical result. When merging class data sheets, keep original source files, define an observation identity key—such as sample ID, date, trial time and measurement type—compare records by that key, resolve conflicting copies, and count each real observation once. Two different trials can legitimately produce identical values, so deleting every repeated number destroys valid data.
The educational aim is traceable evidence: learners should explain how a total was constructed and how records were checked before drawing a conclusion. The following dataset is fictional, uses anonymous sample codes, and does not represent real children or class results.
An invented plant study with five legitimate measurements
Imagine a class tracking the height of three labelled seedlings at two different times. Because two teams were asked to exchange their files, some measurements appear in both worksheets. Each height is in centimetres, measured from a common soil reference point to the top of a seedling using the class’s agreed method. For our worked example, assume all readings are accurate and the local timestamps refer to the same day.
- Team A, record R01: seedling P1, 09:00, height 8 cm.
- Team A, record R02: seedling P2, 09:00, height 9 cm.
- Team A, record R03: seedling P1, 15:00, height 9 cm.
- Team B, record R02: seedling P2, 09:00, height 9 cm—a copy of the exact R02 record.
- Team B, record R04: seedling P3, 09:00, height 9 cm.
- Team B, record R05: seedling P2, 15:00, height 10 cm.
Six spreadsheet rows are visible, but they describe five distinct recorded events because R02 is repeated. Three measurements equal 9 cm but do not all describe the same event: R02 is P2 at 09:00, R03 is P1 at 15:00, and R04 is P3 at 09:00. The repeated value 9 is not the duplicate rule. The same record identity appearing in two source files is the evidence for the duplicate copy.
Hand-check how double counting changes the reported average
If someone averages all six rows, the height total is 8 + 9 + 9 + 9 + 9 + 10 = 54 cm. Six rows give 54 ÷ 6 = 9.0 cm. But the duplicated R02 observation must not be counted as a second independent reading if the question is about the five unique recorded measurements.
The five unique records R01, R02, R03, R04 and R05 sum to 8 + 9 + 9 + 9 + 10 = 45 cm. Their mean is 45 ÷ 5 = 9.0 cm too. Both averages happen to match here. That is intentional: an error can remain invisible in the final average. It is wrong to conclude the merge was safe just because the displayed mean did not change.
Now suppose the duplicated record had been R05, the 10 cm measurement, instead. Six row values would sum to 55 cm, giving 55 ÷ 6 ≈ 9.17 cm to two decimal places; the correct five-unique-record mean would still be 9.0 cm. In this second hypothetical case, the duplicate changes the mean. The correct record count is therefore essential whether or not the summary number visibly moves.
Design an identity key before pressing Remove Duplicates
A useful observation identity answers: what was measured, which object or sample, and when was that observation made? For a school experiment, a deliberately assigned trial ID can be the simplest key, provided it is globally unique across groups. If teams reuse IDs independently, add a source prefix or a documented combination of fields rather than pretending the same short code always means the same event.
- Record ID: a persistent identifier for one observation, assigned under a shared class rule.
- Sample ID: which seedling, water container, or test specimen the result belongs to.
- Observation time: the agreed local date and time or elapsed experiment time.
- Measured quantity and unit: height in cm, temperature in °C or another explicitly named measure.
- Source file: where the row originally came from, so disputes can be resolved without guessing.
- Quality note: whether a value is provisional, corrected, missing or repeated for a documented reason.
Do not publish pupil names, contact details or private identifiers just to create unique keys. Anonymous sample codes often provide everything a class science project needs. If identities are necessary in a school-authorised study, handle them under the school’s data procedures and keep sensitive data out of public reports.
A duplicate copy is not the same as a repeat measurement
Consider P1 at 09:00 measuring 8 cm, and P1 at 15:00 measuring 9 cm. The sample ID is the same but the times differ, so the events are different. Even if both readings were 8 cm, a genuine new observation at 15:00 would remain part of the time series. Conversely, a later spreadsheet import of the same P1 09:00 source event is not a new biological measurement.
Sometimes two observers measure the same object independently at the same time as part of an inter-observer agreement check. Those should be labelled as two readings by different observers under a planned protocol, not silently merged as duplicate copies or treated as new independent plants. Define the unit of analysis before calculating a class mean: observations, plants, trials and student groups are different denominators.
The safe merge workflow with an audit trail
- 1. Preserve both originals. Save read-only or backup copies so a correction never destroys the submitted evidence.
- 2. Standardise columns. Agree on ID, time, sample, measurement, unit and source. Standardising names is not permission to change observations.
- 3. Check for extra header rows. A repeated header pasted into data is not a measurement; do not count it as one.
- 4. Flag duplicate identity keys. Highlight suspected copies, but do not immediately delete every row with the same height.
- 5. Compare all matching fields. Confirm the key, timestamp, sample, reading, units and source history before merging or correcting.
- 6. Resolve conflicts visibly. If two copies of R02 disagree, check original notes and log the decision; never quietly select the more convenient value.
- 7. Build the clean analysis table. Keep one approved representation of each observation, with a separate audit log or archived original.
- 8. Recount and recalculate. State raw row count, duplicate copies identified, legitimate unique observations and any unresolved records.
Microsoft’s guide to identifying and removing duplicates in Excel recommends highlighting duplicates before removal and keeping a copy of the original data. Its unique-values guidance explains that the columns selected determine which rows are considered duplicates. These software features assist a judgement; they cannot decide by themselves what constitutes an independently collected scientific observation.
A learner diagnostic: three rows all say 9 cm
Show the learner P1 at 15:00 = 9 cm, P2 at 09:00 = 9 cm and P3 at 09:00 = 9 cm. Ask, ‘How many observations?’ Correct response: three, given the stated protocol. Then show two copies both labelled R02, P2 at 09:00 = 9 cm and ask again. Correct response: one original observation represented twice in the merged files. A student who wants to delete every repeated value is confusing a measurement result with record identity.
Ask a tougher follow-up: ‘What if R02 appears twice but one says 9 cm and the other says 9.5 cm?’ Do not simply retain both as two trials or choose the first. Pause and inspect the original notes, timestamps and version history. The two copies might be an untracked correction, a transcription error or two distinct observations sharing a mistaken ID. Until resolved, the correct classification is conflict requiring verification.
Guided-to-independent practice
Stage one: recognise a repeated copy
A fictional temperature table has source IDs T01, T02, T03, T02. Assume both T02 rows have the same date, time, sample and reading. There are four imported rows but three distinct observations. Do not count the second T02 as additional measurement evidence.
Stage two: identical readings can still be different events
Suppose T01 = 24 °C at 09:00 and T02 = 24 °C at 09:15, recorded with the same sensor and under a defined measurement plan. Both are valid separate timestamps. There are two observations even though the numbers match. The common value does not prove a duplicate copy.
Stage three: calculate an independent clean mean
Three fictional records have height values 6 cm, 9 cm and 12 cm. An import repeats the 12 cm record under the same ID, with no new observation. The unclean mean would be (6 + 9 + 12 + 12) ÷ 4 = 9.75 cm. The clean mean of three independent records is (6 + 9 + 12) ÷ 3 = 9 cm. Students should show both totals and explain why the denominator changes from four rows to three observations.
Stage four: unseen transfer
A new merged log shows Q01 at 10:00 = 5 units; Q02 at 10:00 = 6 units; Q02 at 10:00 = 6 units copied from the other file; Q02 at 11:00 = 7 units; and Q03 at 10:00 = 6 units. Assume units and samples are consistent and the 11:00 measurement is genuine. Identify the unique observations and calculate the mean. The answer is four unique records: Q01 10:00, Q02 10:00, Q02 11:00 and Q03 10:00. Their total is 5 + 6 + 7 + 6 = 24 units, so the mean is 6 units. The extra Q02 10:00 row is one duplicate copy.
Progress checks for a real class project
A small class can measure improvement without collecting sensitive information. On the first merge task, record whether each student can state a valid key and recognise one true duplicate. On a later unfamiliar dataset, check whether they protect the originals, identify a repeated header, distinguish same-value measurements and explain an unresolved conflict. A learner who completes that process independently is showing transfer beyond one spreadsheet application.
Parents can ask, ‘What does each row represent?’ and ‘How do you know that this one was not copied from another file?’ Teachers can ask learners to justify the clean observation count before calculating the mean. Group leaders can keep a short changes log identifying which copied row was excluded and on what evidence. None of these steps requires disclosing student names.
Limits: some duplicate decisions remain uncertain
If both teams have used the ID R1 for their first record, matching IDs alone are insufficient. If timestamps are rounded to the nearest hour, two separate observations could share the same recorded time. If files were edited after collection, there may be no clean way to tell an updated value from an original without source history. Do not invent an answer. Flag the collision and describe how it affects the class result.
Similarly, a calculation based on multiple measurements of the same plant does not necessarily estimate the same thing as a calculation using one measurement per plant. This issue is about both duplicates and the unit of analysis. Explain what was counted before claiming a finding about the whole class garden.
Frequently asked questions
Should I click Remove Duplicates on every column?
Not until you define what makes one record the same observation as another. The selected columns change the software’s matching rule. Keep a copy and verify a small sample manually.
What if two readings are equal to the last decimal place?
Equal numerical readings can arise from different objects or times. Check source identity, not only numeric equality.
What if the same observation has been corrected?
Keep the correction history, investigate the authoritative original record and mark which value is used in analysis. A revised reading is not automatically a new independent observation.
Will the average always change after removing duplicates?
No. The first worked dataset showed the same mean before and after removing a duplicate. Counts, evidence integrity and uncertainty still matter even when the average happens to remain unchanged.
The next useful reading route
For avoiding another common spreadsheet error, see how to sort without separating labels from results. To recognise copied headings as non-data, continue with how to catch repeated header rows. The eduKate Education Hub and the explanation-first Clementi Mathematics reference connect these habits to broader independent checking.
When a group combines data, ask not only ‘How many rows?’ but ‘How many real observations do the rows represent?’ That question turns a spreadsheet exercise into trustworthy research practice.