Why Spreadsheets Break Down

Excel is a remarkable piece of software. Hundreds of millions of people use it daily, and for the first 80% of its job — interactive exploration, quick what-ifs, ad hoc summaries — it remains the right tool. But every data engineer learns the same hard lesson: spreadsheets are not a substrate for serious, reproducible, automated data work. Here's why, in the order the wounds typically arrive.

The seven failure modes

No reproducibility. A spreadsheet is a frozen snapshot of the inputs at the moment someone hit save. Re-running the same logic against next month's data means manually clicking through the same chain of operations and praying you do them in the same order. Pandas code is reproducible by design — git checkout, python pipeline.py, identical output.
Scale. Excel's row limit is 1,048,576. In practice it's slow well before that. Pandas handles tens of millions on a laptop; Polars and DuckDB push that to hundreds of millions; Spark or BigQuery handles billions. The transition isn't optional once your data grows.
Hidden errors. Inserting a row in cell range A2:A100 can silently break a formula in D5 that referenced A50. Nothing flags it. The number is just wrong now.
Manual steps don't version-control. Drag-fill, copy-paste, manual filtering — none of it shows up in git diff. "What changed between this report and last week's?" becomes unanswerable.
Collaboration. Merging two analysts' edits to the same sheet is, charitably, a nightmare. Pandas pipelines merge as Python diffs.
No tests. You can't unit-test a spreadsheet's logic in any reasonable way. You also can't refactor it confidently — there's no test suite to catch the regression.
Type confusion. Excel auto-detects types and is aggressive about it. Dates in non-US formats become text. Long IDs get rendered in scientific notation and lose precision. Gene names get parsed as dates (this one is the canonical horror story).

The MARCH1 / SEPT4 / DEC1 disaster

In 2016 a study in Genome Biology found that ~20% of the genomics papers it sampled contained errors caused by Excel auto-converting gene names — MARCH1, SEPT4, DEC1 — into dates. The problem was so persistent that in 2020 the Human Gene Nomenclature Committee actually renamed the genes to symbols Excel wouldn't eat. That is to say: humanity changed its biological vocabulary because we couldn't change a spreadsheet's auto-detect behavior. Read that twice.

The right line to draw

Use spreadsheets for human exploration, sharing one-off summaries, and as output destinations for finished analyses (executives still want a tidy XLSX). Don't use them as the substrate for the analysis itself. The substrate is Python code, version-controlled, tested, scheduled, observed.

Code

When you must read Excel — read it defensively·python

import pandas as pd

# Read every column as string first to disable Excel's type guessing
df = pd.read_excel(
    'sales_2026.xlsx',
    sheet_name='Q1',
    dtype=str,            # bring everything in as text
    keep_default_na=False  # don't auto-convert blanks to NaN
)

# Now you cast deliberately, with explicit error surfaces
df['order_date']   = pd.to_datetime(df['order_date'], format='%Y-%m-%d', errors='raise')
df['amount_usd']   = pd.to_numeric(df['amount_usd'].str.replace(',', ''), errors='raise')
df['gene_symbol']  = df['gene_symbol'].astype('string')   # MARCH1 stays MARCH1

# Anything weird raises now, not at 3 AM in production
assert df['order_date'].notna().all(), 'rows with bad dates leaked through'

Exercise

Take any Excel file with date columns. Save it as CSV from Excel. Open the CSV in a text editor. Compare the date format you see in the cells vs what's actually written to disk. You'll often find that Excel's display format and its underlying serialization disagree — that disagreement is where data corruption lives.