Why your spreadsheet isn't one table: messy-data traps that break totals
By SchemaChat (Data BI LLC) · Published 30 October 2026 · Last reviewed 30 October 2026 · 6 min read
Many exports look like one table but are several stacked together: a data block, a pasted list of mostly-empty rows, then a second header row in a different column order followed by more rows. Tools that read the file as a single table produce quietly wrong totals and rates. This page shows the failure on a synthetic file and how to check your own in five minutes.
Everything on this page uses a synthetic file we generate ourselves for testing. It mirrors a pattern that real exports follow; it is not any customer's data.
The file that looked fine
| Block | Rows | What it is |
|---|---|---|
| 1 | 2,000 | The real sales rows (date, customer, SKU, size, pieces, rate, gross amount) |
| 2 | 110 | A pasted list: only a style and a stock figure filled in; date, customer, SKU, rate and gross amount are blank |
| 3 | 1 header + 1,800 | A second header row with the same column names in a different order, followed by more rows (many repeating earlier ones) |
What goes wrong
1. The second header shifts every column
If a tool maps the later rows by position using the first header, values land in the wrong columns: customer names in the date column, months in the customer column. The fix is to notice that a row whose values are mostly the header names is a new header, then map the rows after it by column name.
2. Pasted list rows inflate rates
The 110 list rows have no SKU. Counted as sales rows, the share of rows with a missing SKU came out at 12.2% (257 of 2,110) instead of the true 7.4% (147 of 2,000). Nothing looks broken — the number is simply wrong.
3. Repeated sections double-count
Because block 3 repeats earlier rows, an unfiltered total across the whole file is far larger than the true total. Which section a row belongs to has to be tracked.
How to check your own file in five minutes
- Search the middle of the file for a row that repeats your column names.
- Count rows where most columns are empty. A run of dozens or hundreds of them is a pasted list.
- Compare the row count with what you expect, and compare totals with and without the suspicious blocks.
- Sort by date: blank dates collect at one end, showing rows that aren't transactions.
- Look for columns that suddenly change meaning partway down (names in a date column).
What SchemaChat does about it
On import SchemaChat looks for a repeated header row and re-aligns the rows after it by column name. It tags every row with the section it came from, and treats a long run of mostly-empty rows as a separate list that is left out of default answers. Asked “is this a single clean table?”, it says what it found and how many rows were affected. Default answers use the first section, with a note saying so.
The detector is deliberately conservative: it only treats a run of 20 or more consecutive rows as a list when most of the columns that are normally filled are empty in those rows, and never when the run makes up most of the table. It can still be wrong, so the structure note tells you what it did.
A checklist before you upload any export
- One header row, at the top.
- No pasted summaries, notes or lists below the data.
- Dates in a single format.
- Blank cells left blank (not “N/A” in some rows and empty in others).
- One row per record; no subtotal rows.