Research Guides
Keep Total Rows Out of Your Research Sum
A research spreadsheet can contain correct numbers and still produce an incorrect total. The risk is especially easy to miss when an export combines individual categories with subtotals and an overall summary. Before adding a column, decide what each row represents.
This is a manual review method for data you are authorized to analyze. It does not describe an automatic Colquhoun.shop validation feature or certify the accuracy of an external export.
1. Write down the level of each row
Record the metric, reporting period, filters and row definition before calculating. Add a working classification such as detail, subtotal or overall total. Keep the original file untouched and make these annotations in a review copy.
The European data portal's visualisation guide warns that aggregate values mixed with underlying rows can lead to double counting. It also advises investigating repeated identifiers or identical rows rather than proceeding without understanding them. Data.europa.eu: duplicates, aggregates and totals
2. Try a small arithmetic check
Imagine an invented website report with three mutually exclusive landing-page groups for the same period: guides have 120 sessions, product pages have 80 and other pages have 50. The export also includes an overall total of 250. Summing all four rows gives 500, even though the underlying report still describes only 250 sessions.
Use the three detail rows to calculate 250, then compare that result with the supplied total. Keep the total as a reconciliation check, not as a fourth category. If the result differs, investigate filters, missing groups, rounding and definitions instead of forcing the figures to match.
3. Do not mistake different labels for separate populations
Removing a row named “Total” is not enough. A report could show mobile sessions and paid-search sessions, with some sessions belonging to both. Those labels describe different dimensions, so their counts are not automatically additive.
Ask whether every counted item belongs to exactly one included group under the source's definitions. If that cannot be established, show the groups separately and explain the overlap. Do not invent a combined audience or subtract an assumed overlap just to produce one headline number.
4. Investigate repeated rows before deleting them
Two rows with the same visible label may refer to different properties, dates or filters. Conversely, the same export may have been imported twice. Compare the full identifying fields and provenance before deciding that a row is a duplicate.
Keep an exclusion log with the original row reference and reason. “Excluded supplied overall total from detail sum” is reproducible. “Cleaned the spreadsheet” does not tell the next reviewer which evidence was removed.
5. Attach a reconciliation note
- Question: the total or comparison the calculation should answer.
- Scope: source, period, filters and metric definition.
- Included rows: the mutually exclusive groups being added.
- Excluded rows: totals, subtotals or confirmed duplicate imports, with reasons.
- Check: calculated result beside any comparable supplied total.
- Unresolved limits: overlapping categories, omitted groups or unknown definitions.
Publish the result only at the level the evidence supports. If the source gives several overlapping views, an honest set of separate observations is more useful than one inflated total. A calculation can reconcile perfectly and still use the wrong scope, so retain both the arithmetic and the definition of what was counted.