When a Spreadsheet Is Not Enough

Why spreadsheets are everywhere, and where the risk is

Spreadsheets are the most widely used analysis tool there is, for good reasons: everyone has one, the data is visible while you work, a formula answers a question in seconds, and nobody has to file a request with IT. The previous pages show how to use them well. This page is about the point where the same strengths become weaknesses.

End-user computing (EUC) is the name for applications that business users build and run themselves, outside IT's development and change processes: spreadsheets above all, but also small databases and macros. In banks and insurers EUC is a recognized governance topic, because important figures often pass through such files. Spreadsheet risk is the chance that a decision, report or payment is wrong because of an error in a spreadsheet, or because the spreadsheet process itself fails: the file is lost, the only person who understands it is away, or nobody can show how a number was made.

The risk grows with four things: how much depends on the number, how often the process is repeated, how large and complex the data is, and how many people have to trust a result they cannot check. Kestrel's monthly KPI pack scores high on all four.

The KPI pack in October 2026

Every month Lucía Romero, a billing clerk at Kestrel Energy, builds the key performance indicator (KPI) pack for Margriet de Wit, the chief financial officer: active contracts, kWh delivered per tariff, the amount billed, overdue invoices, new customers and churn. She opens the nightly 02:30 export of KBS (the Kestrel Billing System), looks up the tariff for each connection, builds a pivot table and pastes the result into a copy of last month's workbook. The file for September is called KPI-pack-2026-09-final-v3.xlsx.

On Monday 5 October 2026, Joost Bakker compared Lucía's September figures with the first test output of the automated pipeline he is building under CHG-0772 (see Data Pipelines). Three numbers disagreed:

• Billed per tariff. The export carries an 18-digit EAN code per connection. Opened by double-clicking, the column became numbers with 15 significant digits, and the codes of two connections ending in 017 and 024 both turned into 871687400000000000 (the mechanism is on CSV, JSON and Parquet). The tariff lookup returned the first match for both, so the bakery's €640.10 dynamic-tariff invoice was counted as fixed: in the pack, dynamic showed €125.75 for September instead of €765.85, and fixed €1,012.60 instead of €372.50.
• Overdue amount. While pasting the overdue invoices into the pack, one row was pasted twice. The pack said €253.05; KBS says €184.80.
• Active contracts. The pack counted every contract without an end date: 13. Femke Jansen's approved definition gives 11 (see Define the measure before you count).

None of this is about Lucía being careless. Each step is a normal thing to do in a spreadsheet, the process gave her no way to notice, and nobody else reviewed it. That is how a blameless postmortem would describe it, and it is where the rest of this page looks: limits, silent conversions, copying, people and review.

Well-known spreadsheet failures

Spreadsheet errors have made the news many times. The stories are often retold with the details wrong, which is ironic for a lesson about accuracy, so here they are with their sources' own wording.

The pattern is the same in every case, and in Kestrel's: a manual step, a limit or conversion nobody noticed, and no independent check before the number was used. EuSpRIG, a European interest group on spreadsheet risk founded in March 1999 by the University of Greenwich, UWIC Cardiff and HM Customs & Excise, has collected such cases for decades on its "horror stories" page.

Row limits

A row limit is the maximum number of rows a worksheet or file format can hold. Data beyond it is cut off on import or cannot be added. The limits as of October 2026:

When Excel opens a CSV file with more rows than fit, it loads the first 1,048,576 and warns that the file was not loaded completely. Click the warning away and everything that follows is computed on part of the data, without any sign in the totals. What that means for Kestrel's meter data:

  | A                             | B
1 | Rows per worksheet            | 1048576
2 | Smart meters                  | 400000
3 | Readings per meter per day    | 96
4 | Readings per day              | =B2*B3
5 | Sheets needed for one day     | =ROUNDUP(B4/(B1-1),0)
6 | Minutes of one day that fit   | =ROUND((B1-1)/B4*24*60,0)
7 | Days of daily totals that fit | =ROUND((B1-1)/B2,1)

  | A                             | B
1 | Rows per worksheet            | 1048576
2 | Smart meters                  | 400000
3 | Readings per meter per day    | 96
4 | Readings per day              | 38400000
5 | Sheets needed for one day     | 37
6 | Minutes of one day that fit   | 39
7 | Days of daily totals that fit | 2.6

One row of each sheet is the header, hence B1-1. Kestrel's roughly 400,000 smart meters each send a reading every 15 minutes: 38.4 million rows a day, 37 full worksheets. One sheet holds the readings of about 39 minutes, or two and a half days of daily totals. Long before the hard limit, a workbook with hundreds of thousands of rows and lookups becomes slow to recalculate and to open. Excel's Data Model (Power Pivot) can hold far more rows than a sheet, but then you are already using a columnar database inside Excel, and the questions on this page about review and history still apply.

Silent conversions

A spreadsheet decides what you meant when you type or import a value. That is convenient for 9.13 and dangerous for an identifier. Excel calls its version Automatic Data Conversion: the automatic changes a spreadsheet makes to typed or imported text, such as removing leading zeros, storing long digit strings as 15-digit numbers, reading digits around an "E" as scientific notation, and turning text that looks like a date into a date.

Excel for Microsoft 365 (since about late 2023) and Excel 2024, on Windows and Mac, let you switch four of these off under File ▸ Options ▸ Data ▸ Automatic Data Conversion: removing leading zeros, truncating to 15 digits, converting to scientific notation, and converting continuous letters and numbers (such as MARCH1) to dates. The settings belong to the user's Excel, not to the file, so the next person to open the same CSV may still get the damage.

Other programs have their own rules. This is what LibreOffice Calc 25.2 (with English, United States locale settings) did when each value in column A was typed in:

  | A                   | B             | C
1 | Typed               | Number?       | Stored as
2 | SEPT1               | =ISNUMBER(A2) | =A2
3 | MARCH1              | =ISNUMBER(A3) | =A3
4 | 871687400000000017  | =ISNUMBER(A4) | =TEXT(A4,"0")
5 | '871687400000000017 | =ISNUMBER(A5) | =A5
6 | 00417               | =ISNUMBER(A6) | =TEXT(A6,"0")
7 | '00417              | =ISNUMBER(A7) | =A7
8 | 1E5                 | =ISNUMBER(A8) | =TEXT(A8,"0")
9 | 2026-10-01          | =ISNUMBER(A9) | =TEXT(A9,"0")

  | A                  | B       | C
1 | Typed              | Number? | Stored as
2 | SEPT1              | FALSE   | SEPT1
3 | MARCH1             | FALSE   | MARCH1
4 | 8.716874E+017      | TRUE    | 871687400000000000
5 | 871687400000000017 | FALSE   | 871687400000000017
6 | 417                | TRUE    | 417
7 | 00417              | FALSE   | 00417
8 | 1.00E+05           | TRUE    | 100000
9 | 2026-10-01         | TRUE    | 46296

• Calc left SEPT1 and MARCH1 alone in this test; Excel without the new options converts them to dates. Do not rely on either: the result depends on the program, the version and the locale.
• The 18-digit EAN became a floating-point number. The stored value ends in 000, so the last digits are gone for good, exactly as in Lucía's pack. A leading apostrophe (row 5) tells the spreadsheet to keep the text as typed; the apostrophe itself is not stored.
• 00417 lost its leading zeros, and 1E5, which could be a product code, became 100,000.
• The ISO date is stored as the number 46296: spreadsheets count days from a starting date and only display them as dates. That is harmless here, and is why a date column can be summed by accident.

The defense is to say what each column is before the data arrives: import a CSV through the import dialog (Excel: Data ▸ From Text/CSV; Calc: the Text Import dialog) and set code columns to Text, or read the file with a tool where you declare the types (see Schema inference). And check files that have already been through a spreadsheet. Joost now runs a check like this on every export Lucía sends back:

import csv
import io
import re
from collections import defaultdict

# The September invoice export after a round trip through a spreadsheet
export = """invoice_id;ean;period;amount_eur
INV-40026;871687400000000000;2026-09-01;86,35
INV-40030;871687400000000000;2026-09-01;640,10
INV-40035;8,71687E+17;2026-09-01;452,90
"""

EAN = re.compile(r"\d{18}")
by_ean = defaultdict(list)
for row in csv.DictReader(io.StringIO(export), delimiter=";"):
    if not EAN.fullmatch(row["ean"]):
        print(f"{row['invoice_id']}: ean {row['ean']!r} is not 18 digits")
    by_ean[(row["ean"], row["period"])].append(row["invoice_id"])

for (ean, period), invoices in sorted(by_ean.items()):
    if len(invoices) > 1:
        print(f"{ean} has {len(invoices)} invoices for {period}: {', '.join(invoices)}")

INV-40035: ean '8,71687E+17' is not 18 digits
871687400000000000 has 2 invoices for 2026-09-01: INV-40026, INV-40030

The shape check catches the code that was saved in scientific notation. The truncated codes still have 18 digits and pass it; only the business rule "one invoice per connection per month" exposes them. A useful data check tests what the data means, not just what it looks like.

Copies, versions and people

Copy-paste and file names

A spreadsheet process that runs every month usually starts from last month's file: copy it, paste in the new data, check that the formulas still point at the right ranges. Each step is a chance for a silent error: a pasted range one row longer than the old one, a row pasted twice, a formula that still refers to the old sheet, a total typed over a formula "just this once". The London Whale model failed in exactly this kind of copy-and-paste chain.

The file names tell the rest: KPI-pack-2026-09-final.xlsx, …-final-v2.xlsx, …-final-v3.xlsx, and an attachment in someone's mailbox that may or may not be the same. There is no history of who changed which cell and why, no way to compare two versions line by line, and no review before a version becomes the one Margriet reads. Those are exactly the problems version control solves for code, and a reason to move recurring logic into code. A shared workbook with version history in Microsoft 365 or Google Sheets helps with "who changed what", but not with review.

One person who knows the steps

A key-person dependency is a process that only one person knows how to run, so that it stops or degrades when that person is absent or leaves. It is the human version of a single point of failure. Lucía knows which columns to delete from the export and which cell to overwrite when a tariff changes. None of it is written down. When she was ill in the first week of a month earlier this year, the pack reached Margriet three days late, and the person who stepped in got the overdue figure wrong.

The first fix costs nothing: Lucía writes the steps down, in order, with the reasons ("delete the columns KBS adds for its own use: not needed for the pack"), and a colleague runs the next pack from that description while she watches. Every step that needs a judgment becomes a question for Femke, who owns the KPI definitions. That document is also the specification for automating the pack, and belongs in the team's knowledge base.

Auditing a spreadsheet

A spreadsheet audit is a systematic review of a spreadsheet's inputs, formulas and outputs by someone other than its author, to find errors and check that it does what it claims. Research on such audits is sobering. Raymond Panko, summarizing seven field studies since 1995, reported that 88% of the 113 spreadsheets audited in them contained errors. That is 88% of 113 audited spreadsheets, not "88% of all spreadsheets", and they were not a random sample, but the direction is clear. In field audits he summarized, the share of cells containing an original error was around 1 to 2.5%: small per cell, but a workbook has thousands of cells.

You do not need special software to start. For a workbook that feeds a decision:

1. Show the formulas. Excel: Formulas ▸ Show Formulas; Calc: View ▸ Show Formula. Look for numbers typed where a formula is expected, and formulas that differ from their neighbors in a column.
2. Trace the inputs. Trace Precedents (Excel) or Tools ▸ Detective (Calc) shows where a total gets its values. Ask where each input comes from and when it was last refreshed.
3. Check the ranges. Does every SUMIFS and lookup cover all the rows, including the newest ones?
4. Reconcile with the source. Every total in the output must match a control total from the system it came from.
5. Recompute one number another way. In SQL, in a different sheet, or by hand for one tariff.

A control total is the cheapest of these and catches the pasted row. Put the figure from KBS next to the pack's own sum, with a check cell that cannot be overlooked:

  | A             | B          | C       | D                                     | E
1 | invoice_id    | period     | status  | amount_eur                            | copies
2 | INV-40013     | 2026-07-01 | overdue | 66.70                                 | =COUNTIF($A$2:$A$5,A2)
3 | INV-40021     | 2026-08-01 | overdue | 49.85                                 | =COUNTIF($A$2:$A$5,A3)
4 | INV-40025     | 2026-08-01 | overdue | 68.25                                 | =COUNTIF($A$2:$A$5,A4)
5 | INV-40025     | 2026-08-01 | overdue | 68.25                                 | =COUNTIF($A$2:$A$5,A5)
7 | Total in pack |            |         | =SUM(D2:D5)                           |
8 | KBS control   |            |         | 184.80                                |
9 | Check         |            |         | =IF(ROUND(D7-D8,2)=0,"OK","MISMATCH") |

  | A             | B          | C       | D          | E
1 | invoice_id    | period     | status  | amount_eur | copies
2 | INV-40013     | 2026-07-01 | overdue | 66.7       | 1
3 | INV-40021     | 2026-08-01 | overdue | 49.85      | 1
4 | INV-40025     | 2026-08-01 | overdue | 68.25      | 2
5 | INV-40025     | 2026-08-01 | overdue | 68.25      | 2
7 | Total in pack |            |         | 253.05     |
8 | KBS control   |            |         | 184.8      |
9 | Check         |            |         | MISMATCH   |

The check says MISMATCH and the copies column points at the duplicate invoice number. ROUND keeps tiny floating-point differences from causing a false alarm (see rounding errors). A review by a second person, the four-eyes principle, would have caught the other two problems as well: the EAN column and the definition of "active".

Moving a spreadsheet process

The goal is a single source of truth: one agreed, maintained place where each piece of data and each definition lives, so that every report that uses it gives the same answer and a correction is made once. At Kestrel that place will be the analytics store Joost is building, separate from the production database (see Kestrel's choice), with the KPI definitions written down by Femke.

In SQL the whole September KPI row is one query on the source data, with the definitions in the code where anyone can read them:

SELECT
  (SELECT count(*)
     FROM contracts c JOIN customers cu USING (customer_id)
     WHERE c.start_date <= DATE '2026-09-30'
       AND (c.end_date IS NULL OR c.end_date >= DATE '2026-09-30')) AS active_contracts,
  (SELECT sum(amount_eur) FROM invoices
     WHERE period = DATE '2026-09-01') AS billed_september,
  (SELECT count(*) FROM invoices WHERE status = 'overdue') AS overdue_invoices,
  (SELECT sum(amount_eur) FROM invoices WHERE status = 'overdue') AS overdue_eur,
  (SELECT count(*) FROM customers
     WHERE signup_date BETWEEN DATE '2026-09-01' AND DATE '2026-09-30') AS new_customers;

┌──────────────────┬──────────────────┬──────────────────┬───────────────┬───────────────┐
│ active_contracts │ billed_september │ overdue_invoices │  overdue_eur  │ new_customers │
│      int64       │  decimal(38,2)   │      int64       │ decimal(38,2) │     int64     │
├──────────────────┼──────────────────┼──────────────────┼───────────────┼───────────────┤
│               11 │          1964.35 │                3 │        184.80 │             1 │
└──────────────────┴──────────────────┴──────────────────┴───────────────┴───────────────┘

It runs on Kestrel's sample data in DuckDB 1.5. The query uses no copied rows, no lookups by EAN and no pasted totals, and it gives the same answer every time. "Overdue" here is the status KBS sets, and two of the three overdue invoices belong to the contract with an unknown customer: both facts belong in the written definitions, not in someone's memory.

Steps that work

1. Write down the current process, every step and every manual exception, as Lucía did above.
2. Define each number with its owner: what is counted, at which date, what is excluded and why (see Defining KPIs).
3. Rebuild the logic in the right tool: SQL for joins and totals over many rows (Analytical SQL), a Python script for messy files and cleaning rules (Data Analysis with Python), a scheduled pipeline to run it (Data Pipelines), and a BI tool to present it (Business Intelligence Tools). Keep the code in version control.
4. Run old and new side by side for a few months and explain every difference. Each one is either a bug in the new version or a mistake or a definition in the old one, as on 5 October.
5. Switch over as a change, with the people who use the numbers informed. Joost has drafted change CHG-0775, "Retire the manual KPI pack", to follow once the nightly load (CHG-0772) and the dashboard run reliably.

When a spreadsheet is fine

Most spreadsheets never need to become anything else. Ask four questions:

A quick analysis of a list someone sent you, a budget draft or a one-off comparison is exactly what spreadsheets are for. Use the habits from Spreadsheets Done Right, and move the work only when the right-hand column starts to describe it.