Dirty Data Horror Stories and Costly Spreadsheet Mistakes

When a Single Cell Can Cost Millions

A spreadsheet error rarely looks like an error at the moment it is made. It looks like a number in a cell. It looks like a file that opened without a warning. It looks like a total that is plausible enough to paste into a report.

That is what makes dirty data expensive. The mistake does not fail loudly at the point of entry. It survives the review, travels through a handoff and only surfaces when a spacecraft misses its orbit or a trading desk cannot explain its positions.

This article takes six failures in the order the error travelled. Four are documented incidents with public records. Two are composites drawn from the patterns that keep appearing in client exports, and they are labelled as such. For each one, the article sets out what happened, the data defect underneath it and the check that would have caught it before the cost arrived.

One rule of thumb links them. Quality practitioners describe a 1-10-100 ratio: a defect costs one unit to prevent at entry, ten units to correct downstream and a hundred units to live with after it reaches a customer or a regulator. The incidents below sit at the hundred end, and each one began with a step that would have cost almost nothing.

The $125 Million Unit Mismatch

On 23 September 1999, after a nine-month journey, NASA's Mars Climate Orbiter reached the planet and disappeared. The spacecraft was lost as it approached the atmosphere. The mission had cost $125 million, and the failure had nothing to do with a meteor or a mechanical fault.

The navigation model expected thruster data in newton-seconds, the metric unit of impulse. A ground software file produced by the contractor, known as the Small Forces file, supplied the same measurements in pound-force seconds. The numbers were internally consistent and arrived in the agreed format. Only the unit label was wrong, and the trajectory calculation treated every value as metric.

The Mishap Investigation Board identified the root cause as the failure to use metric units in the coding of that ground software file, and it also recorded the wider organisational gap: no process required the two teams to verify the units at the interface. That is the part worth copying into a spreadsheet review. The defect was not a bad calculation. It was a missing assertion about what a column meant.

NASA's own account of the mission records ground software in English units against onboard software in metric, which is the same defect in plainer words. Nearly three decades later the pattern is familiar in spreadsheet work: two exports, identical columns, different conventions.

What would have caught it: a unit column that travels with the data, a range check that rejects an impulse value an order of magnitude outside the expected band and a single owner for the interface specification. In Google Sheets, the range check is a conditional formatting rule or a validation column, and it costs a few minutes to add.

$460 Million Lost in 45 Minutes

Shortly after the market opened on 1 August 2012, Knight Capital's equity order router began sending orders it could not reconcile. In 45 minutes it released more than 4 million orders while trying to fill 212 customer orders, traded more than 397 million shares and accumulated several billion dollars of unwanted positions. The SEC put the eventual loss at more than $460 million, and the firm agreed to pay $12 million to settle charges that it had failed to maintain adequate market access controls.

The SEC order describes two technology missteps. A section of code had been moved in 2005 to an earlier point in the sequence, disabling a function that was left in the router. In late July 2012, while preparing for a new exchange programme, Knight deployed new code to the same router and the old function came back to life. The router lost the ability to recognise when orders had been filled, so it kept sending.

The detail that matters most for anyone who owns a data pipeline: an internal system sent 97 automated emails to staff before the market opened, each referencing the router and identifying an error. Nobody acted on them. The alerts existed, the signal was present and the process had no owner to read it.

What would have caught it: a pre-deployment reconciliation that compares orders entering a system with orders leaving it, alert thresholds tied to a named person and a rollback that does not depend on someone noticing an email at 09:20.

The Excel Row Limit That Hid 16,000 Test Results

In September 2020, Public Health England was aggregating laboratory results from commercial testing firms. The laboratories supplied CSV files, which were pulled into Excel templates and then uploaded to a central system. The developers had chosen the older XLS file format for those templates.

XLS supports about 65,000 rows rather than the million-plus available in XLSX. Because each test result occupied several rows, a template could hold roughly 1,400 cases. When the limit was reached, later cases were simply left out of the file. Nothing failed. Nothing alerted. The BBC reported that nearly 16,000 positive results were left out of England's daily counts, and the agency's fix was to split the data into smaller batches.

This is the quietest failure in the article and the most ordinary. No code was wrong. The format had a ceiling, the pipeline had no row count and the output looked complete because every row that survived was correct.

What would have caught it: a row-count comparison between the source file and the uploaded table, run every time and alerting on any difference. Importing into Google Sheets or XLSX removes the format ceiling, but the count check is what detects truncation, including truncation caused by something else.

The Formula That Stopped Five Rows Short

In 2010, Carmen Reinhart and Kenneth Rogoff published a paper finding that average economic growth falls sharply once public debt passes 90 per cent of GDP. The finding travelled into austerity debates across Europe and North America, and it rested on a spreadsheet among other sources.

In April 2013, Thomas Herndon, Michael Ash and Robert Pollin published a replication. They found three problems: a coding error in which an AVERAGE formula covered the wrong range and excluded five rows, a pattern of selective exclusion of available data and inappropriate weighting across countries. Correcting the range and the weighting raised average growth for high-debt countries from the reported minus 0.1 per cent to 2.2 per cent. The threshold story did not survive the correction.

The spreadsheet behaved exactly as instructed. It returned a number with a decimal point, and the number was plausible, which is why it passed every review that looked at the output rather than the formula. A range that excludes five rows produces no error message, no colour change and no warning, and the same is true of a SUM range that stops one row above the last record.

What would have caught it: range auditing as a review step, whole-column references or named ranges instead of hand-dragged blocks and a second person rebuilding the calculation from the raw data rather than reading the result.

The Duplicate Records Nobody Counted

The remaining three failures are composites. They are assembled from recurring shapes in client exports rather than from one named organisation, and they are included because they are the failures most readers will meet this quarter.

Take a CRM export of 10,000 customer rows. The same person appears two or three times under slightly different names, an old email domain or a phone number with a country code. Nobody has counted the duplicates because the export reports 10,000 rows and every row looks like a customer.

The damage arrives in four places at once. Marketing sends the same promotion to one person several times and pays for each send. Revenue per customer is understated, because the same revenue is divided across duplicate rows. Sales representatives contact the same prospect from different records and contradict each other. Reports that count customers overstate the base by the duplicate rate.

The arithmetic is worth doing once. If three in ten rows are near-duplicates, then 7,000 real customers are being counted as 10,000, and revenue per customer is understated by about 30 per cent before any other cleaning issue is considered. The calculation is counted rows / real rows - 1, and it turns a vague worry about duplicates into a number a manager can act on.

What would have caught it: a duplicate-rate check before every send or report, using fuzzy matching rather than exact matching, because exact deduplication misses "Jon Smith" against "John Smith" and "Acme Corp" against "Acme Corporation".

The Clinic Merge That Billed the Wrong Patients

Three clinics merge their patient lists into one spreadsheet. Each clinic has recorded names in its own convention: one uses "Last, First", another "First Last", a third abbreviates middle names. The merged sheet is sorted by name and joined to a billing export on the name column.

The join produces matches, and some of them are wrong. Two patients with similar names become one record, or a patient is linked to another person's invoice. Bills reach people who never attended the clinic, and the merged record now carries clinical detail attached to the wrong identifier. The billing error is visible within weeks. The record error can persist for years, and in a regulated setting it becomes a data protection problem rather than an administrative one.

The root cause is structural rather than careless. Names are not identifiers, and a join on a name column treats them as if they were.

What would have caught it: join on a stable identifier such as a patient number, and use name similarity only to propose candidate matches for human review. Standardise the name formats before the merge so the review list is short enough to finish.

The Campaign Sent to People Who Had Moved On

A B2B list is built carefully, segmented by job title and company size, and then used 18 months later for a campaign that depends on both fields. In that time people change roles, companies are acquired and email domains lapse. The spreadsheet still reports the segments as they were, because nothing in it records when a value was last verified.

This failure has no dramatic single moment. The campaign sends, the bounce rate rises, replies come from people who left the role a year ago and the segment that looked precise turns out to describe a previous organisation chart. The cost is the campaign budget plus the opportunity cost of the window it occupied.

What would have caught it: a last-verified date on every record, a rule that blocks a segment when more than a set share of its records are older than the review window and a scheduled quality pass that flags records which have not been confirmed.

The Common Threads Behind Every Data Disaster

These stories span aerospace, trading, public health, economics, healthcare and marketing. They involve different tools, different industries and wildly different budgets. Four conditions appear in every one.

• No validation at the point of entry: The orbiter data arrived without a unit check. The template accepted rows until it could not. The merged clinic list accepted three name conventions into one column. In each case a rule was possible and absent.
• A check that could not see the failure: Reviews looked at outputs, not at the process that produced them. A plausible number passes a plausibility review, which is why the formula error and the row limit both survived.
• Automation carrying the error forward: A wrong unit became a wrong trajectory, a wrong configuration became four million orders, a truncated file became a national undercount. Automation does not create the error, it compresses the time available to notice it.
• No named owner for the warning: Knight Capital's 97 emails had no reader. The interface specification had no owner. The row count had no checker. Warnings without an owner are not controls.

None of these conditions requires an enterprise budget to fix. Each one maps to a column, a formula or a scheduled check inside the spreadsheet where the data already lives.

How to Bulletproof Your Spreadsheets Against These Mistakes

The prevention list is short, and every item corresponds to a failure above.

1. Standardise Data at Entry

Enforce consistent formatting as data enters the sheet rather than after it is merged. Strip punctuation, remove diacritical marks, standardise case and remove stop words in a single pass. This is the layer that would have made the three clinic name conventions comparable, and the one that keeps a unit or a date format from drifting between exports.

2. Deduplicate with Fuzzy Matching

Google Sheets' built-in remove duplicates tool catches exact matches only. Smart Deduplicate combines phonetic, fuzzy, exact-match and punctuation-normalised strategies, so variants such as "Jon Smith" against "John Smith" and "Acme Corp" against "Acme Corporation" reach a review list instead of staying invisible.

Pick the threshold on a sample where the right answers are known. A range of 85 to 90 per cent suits automatic merging for most name data, while 70 to 85 per cent is better treated as a review queue. The numbers are a starting point, not a universal setting.

3. Reconcile Counts Before Automation

Before data feeds a report, a merge or an external system, compare the counts at both ends: rows in the source against rows in the destination, distinct keys against total keys and the sum of a numeric column against the source total. The Public Health England failure is a count mismatch, and a single comparison would have exposed it on the first day.

4. Audit Ranges, Not Just Results

Open the formulas behind any number that will inform a decision. Check that each range covers the last row, that no block excludes hidden data and that a total agrees with a second method. Whole-column references and named ranges reduce the chance of a range that stops short.

5. Schedule Recurring Data Audits

Data decays whether or not anyone is watching. Run deduplication, standardisation and quality checks on a cadence: weekly for fast-moving operational data and monthly for stable reference data. The value is not the individual run but the fact that the check happens without anyone remembering to start it.

6. Give Every Warning an Owner

A conditional formatting rule that nobody reads is decoration. Name the person who acts on each check, decide in advance what they do when it fires and record what happened. Knight Capital had the alert and lacked the owner, which makes it the cheapest failure in this article to prevent.

The Cost of Prevention Is Always Less Than the Cost of Disaster

These incidents are not anomalies. They are the predictable output of a process that accepts data without validation, reviews results rather than rules and lets automation move faster than oversight. The size of the organisation changes the number attached to the failure, not the shape of it.

What separates a $125 million loss from a clean sheet is not luck. It is a set of checks placed where the error enters: standardise at entry, match with fuzzy logic, reconcile counts, audit ranges and schedule the audit so it does not depend on memory.

None of those steps is expensive. All of them are cheaper than the alternative, and each one is available inside the spreadsheet where the data already sits.

Sources and Further Reading

• NASA, Mars Climate Orbiter mission page, on the English and metric unit mismatch between ground and onboard software.
• NASA, Mars Climate Orbiter Mishap Investigation Board findings, which identify the failure to use metric units in the ground software as the root cause.
• US Securities and Exchange Commission, SEC charges Knight Capital with violations of market access rule, 16 October 2013, with the order figures for orders, shares and losses.
• BBC News, Excel: why using Microsoft's tool caused Covid-19 results to be lost, 5 October 2020, on the XLS row limit and the batching fix.
• Thomas Herndon, Michael Ash and Robert Pollin, Does high public debt consistently stifle economic growth, Political Economy Research Institute, April 2013.

Frequently Asked Questions