You've got tickets for the synchronised swimming at the London 2012 Olympics. Then an email asks if you'd like to swap them for another event. The organisers had sold 10,000 more tickets than the sessions held.
The cause was a single keystroke: a member of staff typed 20,000 into a spreadsheet instead of the correct 10,000 remaining tickets (The Telegraph, January 2012).
The mistake surfaced only after the tickets had gone out. The organisers then had to contact about 3,000 buyers and offer swaps for other events, including seats at the 100m final (BBC News, January 2012).
The problem behind it is an everyday one. Most businesses have a spreadsheet the whole operation quietly depends on: the pricing calculator, the payroll sheet, the stock list, the job schedule, the quote template. Someone clever built it years ago, and it's been tweaked ever since. Nobody's sure every formula still does its job.
Try this before you read on: open the spreadsheet your business would miss most, click any total, and check that its range reaches the last row of data. If it stops short, you've found your first problem.
This article explains the most common ways critical spreadsheets go wrong and gives you a risk audit you can run on your own most important file in under an hour.
How business-critical spreadsheets go wrong
Spreadsheet errors are rarely dramatic in the moment. They're quiet. A number is slightly off, and decisions get made on it for months.
Formula errors
- A formula that should cover rows 2 to 500 only covers 2 to 450, because new rows were added below the range.
- A cell was overtyped with a number, replacing the formula, so it no longer updates.
- A reference points to the wrong tab after someone reorganised the file.
- A percentage is applied twice, or not at all.
Input errors
- A typo: 1,000 entered as 10,000.
- A date entered as text, so it doesn't sort or calculate.
- A price updated in one place but not another.
- Data pasted in with an extra column, shifting everything over.
Structure and limit errors
- The file hits a size or row limit, and new rows quietly drop off.
- Sorting one column without the others scrambles the data.
- Hidden rows or columns contain values that still affect totals.
- Links to other files break when those files are moved or renamed.
People and process errors
- Only one person understands how the spreadsheet works.
- Several copies are in use and nobody knows which is current.
- There's no record of who changed what.
- Nobody checks the outputs against reality.
The spreadsheet risk audit
Pick your single most important spreadsheet, the one that would cause the most trouble if it were wrong. Work through these questions with a colleague. Score each one: green (fine), amber (some risk), red (real risk).
Part A: Importance
- What decisions or payments depend on this spreadsheet?
- What would happen if it were wrong by 10% for three months?
- Who would notice, and how quickly?
Part B: Structure
- Is there one master copy, clearly named and stored in one place?
- Is there a "Read me" tab explaining what the spreadsheet does and how to use it?
- Are input cells clearly separated from formula cells (different colour or tab)?
- Are formula cells protected from accidental overwriting?
- Are formulas consistent down each column (no one-off overrides)?
- Do formula ranges cover all the data, including rows added recently?
- Are there any hidden rows, columns or tabs? Do you know why?
- Are there links to other files? Do they still work?
- How close is the file to any size or row limits?
Part C: Inputs
- Do input cells use data validation (dropdowns, date formats, number ranges)?
- Is there a check for duplicates?
- Are imported or pasted data checked for shifted columns?
Part D: People and process
- How many people understand how it works? (One is red.)
- Is there a record of changes, or a way to see who edited what?
- Are outputs checked against an independent source (a bank statement, a stock count, an invoice total) on a regular basis?
- Is there a backup, and has anyone ever restored from it?
- If the keeper left tomorrow, could someone else run it next week?
Fixing what you find
For each red item, take one action this month:
- Add check totals. A cell that should always equal another total (for example, the sum of line items equals the invoice total). If it doesn't, highlight it in red.
- Lock formulas. Protect formula cells and leave only input cells editable.
- Use full-column or table ranges. Convert data ranges into a structured table so formulas automatically include new rows.
- Find overrides. Show formulas and look for cells in a formula column that contain plain numbers.
- Write a "Read me". What it's for, what each tab does, where the data comes from, who owns it.
- Train a second person. Walk them through it and have them run it once while the keeper watches.
- Reconcile monthly. Compare key outputs against an independent source.
- Back it up and test the restore.
When the fix is to stop using a spreadsheet
Some spreadsheets are doing a job no spreadsheet was designed for: a multi-user job tracker, a stock system, a customer database. Signs:
- Several people edit it at once.
- It holds records (customers, jobs, products) rather than calculations.
- It needs a history of changes for accountability.
- People need to use it on their phones.
For these, the best fix is to move the records into an app built for the job, with set fields, permissions and history, and keep spreadsheets for analysis.
The opposite is true too. If one person uses the file, it holds calculations rather than records, and its outputs are checked against the bank or a stock count each month, leave it alone. A tidy spreadsheet like that is the cheapest system you'll ever own, and moving it would cost more than it saves.
Common mistakes in audits
- Auditing a copy. Make sure you're looking at the version actually in use.
- Only checking formulas. Most problems are in inputs and process.
- Fixing everything at once. Start with the red items. Ambers can wait.
- Doing it once. Repeat the audit yearly, or whenever the spreadsheet changes significantly.
A worked example
A Manawatū hardware wholesaler¹ ran its pricing from a spreadsheet that applied supplier costs, freight and margin to each product. During an audit, they found that one supplier's freight surcharge had been typed into a formula column as a fixed NZ$1.20 a unit. Freight had since risen to NZ$1.85, so about 4,000 units a month were selling 65 cents below the intended margin: 4,000 × NZ$0.65 = NZ$2,600 a month, or NZ$18,200 over the seven months before anyone looked. They converted the price list to a table, locked the formula columns, added a check column comparing calculated and expected margin, and moved the supplier cost list into a separate input tab with an owner.
Running the audit in under an hour
Book a meeting room and bring two people: the keeper and someone who uses the outputs but didn't build the file. Spend fifteen minutes on Part A, because knowing what's at stake tells you how hard to look. Spend twenty on Part B with formulas showing on screen. Spend the rest on Parts C and D, which are conversations rather than inspections. Write down every red and amber item with an owner and a date, and you're done.
Then book the next audit for six months' time. Spreadsheets drift, keepers change, and a file that passed in March can be quietly broken by September.
If the audit tells you the spreadsheet is really a database in disguise, more checking won't remove the risk. Look at your reds in Part D: one keeper, no record of changes, several copies in use. A better spreadsheet can't fix those. Moving the records into one shared app, with set fields, a log of every change and one copy everyone works from, clears them in one go. That's what Thogits is for: start from an app in a growing library, keep your spreadsheet for the analysis, and pay NZ$50 per seat per month, incl. GST. Bring your audit sheet to a first conversation, and your reds become the brief.
¹ The Manawatū wholesaler is illustrative. The London 2012 figures are as reported by The Telegraph and BBC News. NZ$ prices include GST.