Totals and notes rows inside the data
Rows that are not records, such as subtotals, grand totals, and footnotes, sit among the rows that are.
What it looks like
Before
| region | sales | note |
|---|---|---|
| North | 120 | · |
| South | 95 | · |
| Total | 215 | · |
| * South excludes returns | · | · |
After
| region | sales | note |
|---|---|---|
| North | 120 | · |
| South | 95 | excludes returns |
How it happens
- The file is a printed report saved as data, and reports end with totals.
- Someone added a sum at the bottom of a sheet to check their work.
- Footnotes, sources, and export timestamps were written into the cells below the table.
What it breaks
- Summing the column counts everything twice, once in the rows and again in the total.
- Counting rows overstates the number of records.
- A footnote in a numeric column makes the whole column text.
How to spot it
- Read the last rows of the file and the rows around any blank line.
- Search the first column for words such as total, subtotal, sum, and average.
- Look for rows where most columns are empty, or where one value equals the sum of those above it.
How to fix it
- Before removing a total, check that it matches the sum of its rows. A mismatch means rows are missing or wrong, and that is worth knowing.
- Remove total and subtotal rows from the data. They can be recalculated at any time.
- Move notes out of the table: into a note column if they describe particular rows, or into the file's documentation if they describe the whole.
- Merged and multi-row headersThe column names are spread over two or more rows, often with merged cells, so no single row names every column.
- Exact duplicate rowsThe same row appears more than once, identical in every column.
- Partially filled rowsA row has some of its fields and lacks others, so it is usable for some purposes and not for others.
TermsRecord, Tidy data, Metadata, Reconciliation