Cleaning a Dataset Without Losing Its Story

Every dataset arrives with problems, and the temptation under deadline is to fix them fast and move on to the chart. That order of operations is how a reporting error slips in. Cleaning a spreadsheet is not a mechanical chore to get through before the real work starts — the choices made during cleaning are part of the finding itself, and they deserve the same scrutiny as anything else in the story.

Start by counting rows before you touch anything, and keep that count somewhere you'll see it again. A dataset that loses or gains rows during cleaning without an explanation is a dataset with a hidden error in it, and the only way to catch that is to know the number you started with.

Deciding what to drop, and writing it down

Duplicate rows, blank entries, and obvious data-entry mistakes usually need to go, but each removal is a decision, not a formality. If you're dropping rows because a field is missing, check whether the missing field clusters around a particular period, office, or category — a pattern there is often more interesting than the rows themselves. Keep a running note of what you removed and why; that note becomes the method section of your eventual story, and it's also what lets you or an editor sanity-check the final numbers.

Totals deserve a second check even when the source document already includes them. Recalculating a stated total from the underlying rows catches transcription errors in the original document, and it also catches errors introduced during your own cleaning — a formula that didn't copy down correctly, a column that got sorted independently of its neighbors. This step is tedious and it is also where a surprising number of reporting mistakes actually live.

Building a table a reader can trust

Once the data holds up, the table you build from it should show a reader roughly what you saw — the categories that matter, sorted in a way that makes the comparison obvious, without collapsing so much detail that the table becomes a claim rather than a record. A short method note next to the table, describing where the data came from and what, if anything, was excluded, does more to build reader trust than any amount of polish on the chart itself.

None of this needs specialized software. A spreadsheet, a bit of patience, and a habit of writing down every decision as you make it will get a dataset from raw export to publishable table without losing track of what the numbers actually represent along the way.