Did you know that a spreadsheet can contain six non-empty lines but only five real survey answers? A class can end up reporting the wrong percentage simply because two files were joined together with a second column-heading row left in the middle. The numbers may look plausible until someone checks which rows are data.
A repeated header row is a copy of the column labels accidentally included among the records when tables or CSV files are combined. Before counting responses, identify which rows represent actual people or observations, keep one header in its proper place, and exclude the extra heading from the data range. Never assume that every non-empty row is a real answer.
The diagnostic that separates counting from understanding
Give a student a seven-line sheet with column headings on the first and fifth lines. Ask, ‘How many students responded?’ A student who says six because there are six rows below the top heading may be counting a second heading as if it belonged to a participant. Another learner may notice the extra heading but remove two identical-looking student answers as ‘duplicates’. The second mistake damages real data.
The aim is to understand a record: one participant ID paired with the response it actually gave. A header describes the meaning of the columns; it does not contain an answer. When an export or copy-and-paste operation mixes those roles, every downstream chart and percentage needs checking.
Worked example: a fictional one-choice class travel poll
Imagine an invented classroom poll asking, ‘Which one mode would you choose for a short trip: Walk or Cycle?’ Every respondent is supposed to choose one option. The data from two class groups are pasted together. Below are the seven visible spreadsheet rows, including headings.
- Row 1: Participant ID | Travel Mode (the intended header)
- Row 2: P01 | Walk
- Row 3: P02 | Cycle
- Row 4: P03 | Walk
- Row 5: Participant ID | Travel Mode (an accidental repeated header)
- Row 6: P04 | Cycle
- Row 7: P05 | Walk
There are five genuine participant records: P01, P02, P03, P04 and P05. Each has one response. The outcomes are Walk: 3 and Cycle: 2, so 3 + 2 = 5 responses. This is a fictional teaching dataset, not a claim about any actual class.
Now suppose a student uses the spreadsheet formula COUNTA(B2:B7) to count all non-empty entries below the original heading. It returns 6, because the second heading ‘Travel Mode’ in B5 is non-empty. The numerator from COUNTIF(B2:B7,"Walk") is still 3. Dividing 3 by the incorrect denominator 6 produces 50%. The correct fraction is 3/5 = 60%. A single copied label has changed the published percentage by ten percentage points.
The change does not mean one student secretly selected a third answer. It means the data range contained a non-response row. For the other option, 2/5 = 40%. The correct categories add to 60% + 40% = 100% under the explicitly single-choice question.
Why simple formulas are not enough
A counting function does what its formula says; it does not decide whether a row is a student, a title or a page break. Likewise, a chart can faithfully display an incorrect range. If the dataset is assembled from several exports, checking the structure of each original file is as important as checking the calculations.
Counting only the recognised choices can help in this simple case: 3 Walk plus 2 Cycle gives five valid responses. But it would be unsafe to generalise that trick to every poll. A real survey might allow ‘Other’, a blank answer, an unanswered item or multiple selections. The correct denominator depends on the actual question and who was eligible to answer. The solution here works because the fictional task specifically permits one of exactly two responses.
A safe data-cleaning routine for a classroom spreadsheet
- Keep the originals: Save the two source exports unchanged. Make cleaning changes in a separate working copy.
- Identify the real header: Check the expected column names and field types, rather than simply removing every row that looks repetitive.
- Scan inside the data: Find rows where several columns suddenly contain label text instead of participant IDs and allowed responses.
- Check against source files: Confirm that the suspected row is a copied heading, not a legitimate free-text answer.
- Remove or exclude only the confirmed extra header: Do not silently delete participant records that happen to share a response.
- Recount and reconcile: Compare the five valid IDs with counts from the source files, then recompute the percentages and charts.
- Record the cleaning step: Note that one repeated heading was removed and why, so another student can reproduce the result.
A real duplicate response is a different problem
Suppose P03 appears twice after the files are joined. That is not obviously a header; it could mean that the same record was copied twice, or that the poll allowed a genuine later response. The teacher’s collection rules determine how to investigate. Do not erase rows simply because both say ‘Walk’. Different participants may reasonably give identical answers.
This is why a general ‘remove duplicates’ command needs care. Microsoft documents that duplicate detection depends on which columns are compared. Using only the response column could collapse all three Walk answers into one, even though three different participant IDs supplied them. For this example the combination of ID, response, source export and survey instructions must guide review.
Practice progresses from recognising a header to auditing a report
Guided: label each row
Show the seven rows above. Ask the student to mark H for header and D for data. The correct sequence is H, D, D, D, H, D, D. Then ask the student to explain why the fifth row cannot represent an answer to the Walk-or-Cycle question.
Supported: compare wrong and right percentages
Let the learner reproduce 3/6 = 50% and 3/5 = 60%. They must state the denominator and its meaning in words. If their explanation is ‘five because we deleted a line’, ask them to be more precise: five is the number of genuine single-choice responses, not just a smaller row count.
Independent: investigate a harder merged sheet
Give two fictional exports, each with a header, some repeated response words and distinct participant IDs. After combining them, ask the child to preserve a raw copy, identify the extra heading and reconcile totals with the originals. The learner should not rely on a software button without explaining what it removes.
Transfer: decide whether a strange row is evidence or an error
Add one free-text answer that legitimately contains the phrase ‘Travel Mode’ in an open comment column. The learner must not delete it merely because those words resemble a heading. Here, checking the full row, schema and original form is necessary. The rule is about record meaning, not matching two words.
What parents, teachers and project leaders can check
A parent can ask, ‘Can you show me the five people represented by that percentage?’ without having to know spreadsheet commands. Teachers can distribute two tiny export files with a deliberate repeated header and ask learners to explain the difference between cleaning metadata and excluding authentic responses. A group leader can require a one-line audit note showing the number of raw rows, rejected structural rows and retained records.
For the next skill, visit how to choose the correct survey percentage denominator and how to code actual open-ended survey responses without changing their words. Those questions become easier once the table’s structure is trustworthy.
A checkable measure of progress
A useful classroom target is for a student to process three different merged sheets without hints: identify any extra heading, justify each excluded structural row, preserve every genuine response and recompute a total that reconciles to the sources. A new file is important; memorising the position ‘row five’ from this example is not learning the transferable method.
Limits: do not use a one-rule cleaning shortcut
Not every dataset begins with a header. Some exports contain title rows, footers, subtotals, explanations, missing values and multiple answers. Some data cells legitimately contain the same words as a column name. If a record’s status remains uncertain after inspecting the original source, flag it for review rather than making up its meaning. The appropriate denominator also changes for non-response or multi-select questions.
Frequently asked questions
Does COUNT ignore the repeated header automatically?
A formula that counts numeric cells may ignore the text heading, but that does not validate the dataset or make every other calculation safe. Functions that count non-empty cells may include it. Inspect the actual formula, field type and allowed values.
Can I delete every duplicate-looking row?
No. Several pupils may choose the same option, and a repeated participant ID could require investigation rather than automatic deletion. Distinguish a copied label from a copied observation and from two authentic answers.
What if the percentages add to 100% even with a repeated heading?
A total of 100% is not sufficient proof of correctness. If the formula excludes unrecognised labels from both numerators and the denominator, the percentages may sum correctly while the underlying file still contains a structural error. Reconcile records against the source data.
The wider lesson: understand what each row represents
Good data literacy is not only entering the right formula. It means being able to explain where the records came from, why a line counts as data and how a result can be independently checked. Explore the eduKate Education Hub and the diagnosis-first mathematics reference for more ways to turn a visible mistake into useful learning.
Reference: Microsoft Support on keeping or removing duplicate rows in Power Query explains why the selected comparison columns matter. The poll, data values and errors here are invented practice examples; no real survey dataset is being reported.