Established onExcel 2016 and later: append matches on column nameGoogle Sheets, web: same, via query toolsManual paste, any version: matches on position
Combining twelve monthly exports into one table is among the commonest things anybody does with a spreadsheet, and the columns are rarely identical across all twelve.
What changes between exports
A column is added when somebody asks for a new field. A column is renamed when a term changes. Columns are reordered when the underlying query is edited. A column is removed when a field is retired. And the header text acquires or loses a space, a capital or a unit in brackets.
None of these is announced, because the export is a report rather than an interface, and nobody promised stability.
The two ways to combine, and how each fails
By position, which is what pasting one block below another does. The reader has no names to match, so the third column of one file lands under the third column of another, whatever they hold. A reordered export therefore mixes dates into an amount column, silently, and the totals still compute.
By name, which is what the query tools do when appending. Columns are matched by header text and anything present in one source and absent from another is filled with empty values. A renamed column therefore becomes two columns, each populated for part of the period and empty for the rest.
The second failure is much better because it is visible in the shape of the result. It is still silent in the sense that nothing reports it: you have to look at the columns.
The check that costs one column
Add the source to every row. A column naming the file or the period each row came from turns every later question into an answerable one: how many rows came from each source, whether any source contributed none, whether the totals per source look plausible.
Without it, a combined table is an undifferentiated block in which a missing month is invisible.
The check that costs one cell
Count the rows per source and compare against what each source contained. The general answer of this whole site, applied here: a count carried alongside the data is the only thing that notices an absence.
Comparing headers before combining
Where the sources are files, list the header row of each one and compare the sets. Any difference is a decision to make deliberately: is this a rename, an addition, or a removal, and what should happen to the rows on the other side of it.
Doing this before appending takes minutes and is the only point at which the question is cheap. Afterwards it is archaeology.
What a renamed column really needs
A mapping, written down: the old name, the new name, and the date the change happened. Both query tools can rename a column as a step, so the mapping becomes part of the import and applies to every future run.
Keeping it in someone's memory instead is how a combined table acquires columns called amount, Amount and Amount (GBP), all of which are the same field.
The failure that looks like success
Appending twelve files and getting a row count equal to the sum of the twelve does not mean the columns aligned. Row counts are preserved by both failure modes above: mixing values keeps every row, and filling gaps keeps every row.
So the count check catches missing sources and does not catch misalignment. For that, compare a total per source against the total the source itself reported, which is cross-footing and is the subject of the final part.
What we cannot verify
The append behaviour of both query tools was established by testing on current builds; both document that appending matches on column names. The description of how exports change over time is our characterisation of common practice rather than a measured claim.
The version of this problem that is not files
The same failure appears when combining sheets within one workbook, and it is worse there because the sheets look uniform. Twelve tabs named for the months, built by copying the previous one, drift apart as somebody adds a column to October and not to the others.
The remedy is the same: match on names rather than positions, and add a column recording which sheet each row came from before combining anything.
What to do when a source disappears
A month that contributed nothing is invisible in a combined table and obvious in a count per source. Keep the count somewhere permanent rather than checking it once: a small block listing every expected source and its row count, refreshed with the data, turns a missing export into a zero somebody sees.
In short
- Exports gain, lose, rename and reorder columns, and none of it is announced.
- Appending by position mixes values; appending by name creates half-empty columns.
- The second failure is visible in the shape of the result and still nothing reports it.
- A source column on every row makes every later question answerable.
- Row counts are preserved by both failure modes, so counting does not catch misalignment.
- A rename needs a written mapping applied as an import step, not somebody's memory.