The spreadsheet only one person understands
Most companies have one spreadsheet that runs a whole process, built and understood by one person. It works until that person is out. How to tell if yours is a real risk, what the research says about spreadsheet errors, and three ways to fix it without throwing it away.

In brief
A spreadsheet only one person understands is a risk for two reasons. Nobody else can run it when that person is out, and nobody else can check it. Research on spreadsheets keeps finding errors: inspection studies summarized by Raymond Panko found errors in 94% of the spreadsheets examined. You usually don't need to replace it. Write down what it does and train a second person, move it off one laptop to a shared place, and replace the copy-and-paste steps with a feed from your ERP. Replace it only when many people need to work in it at once or it has become your system of record.
- A sheet one person understands has two risks: nobody can run it when they're out, and nobody can check it.
- Inspections summarized by Panko found errors in 94% of spreadsheets examined; inspectors find about 60% of errors on average.
- JPMorgan's London Whale report and a famous economics paper both traced problems to spreadsheet logic nobody else had checked.
- Fix it without replacing it: document it and train a second person, move it to shared storage, and replace copy-and-paste with a feed.
- Replace it only when many people need to work in it at once or it has become your system of record.
Every company has one
It might be the production schedule. It might be the pricing sheet, the commission file, the inventory reorder list or the month-end close. Someone built it years ago because the ERP couldn't do what the business needed. It grew. Now it has 14 tabs, a few macros, and formulas that pull from other files on a shared drive.
It works. That's the problem. Because it works, nobody looks at it until the person who built it is on vacation, out sick, or two weeks from retiring. Then the owner finds out that a process the whole company depends on lives in one person's head.
This isn't a story about careless people. The people who build these sheets are usually the most capable people in the office. They solved a real problem with the tool they had. The risk comes from the setup, not the person.
Why it's a risk, even when it's right
There are two separate risks, and owners usually only think about the first one.
The first is coverage. If one person runs it, the process stops when that person stops. Orders don't get scheduled. Prices don't get updated. Commissions don't get paid on time.
The second is checking. If one person understands it, nobody else can tell whether it's right. This is the bigger risk, and the research on it is clear.
Raymond Panko of the University of Hawaii studies spreadsheet errors. In a 2015 paper summarizing that research, he reports three findings. Errors are rare cell by cell, but in a large spreadsheet at least one wrong bottom-line number is very likely. Errors are very hard to find. And the people who build spreadsheets, and the companies that rely on them, are overconfident about how accurate they are.
The numbers behind that, from the same paper:
What spreadsheet research has found
From Panko's 2015 summary of published studies. Each bar measures something different: read them as separate findings, not a comparison.
Source: Panko, "What We Don't Know About Spreadsheet Errors Today," EuSpRIG 2015. Most of the 85 inspections were done by commercial spreadsheet auditing firms.
A 3.9% cell error rate sounds small. But a sheet with hundreds of formulas that feed each other only needs one wrong cell to make the bottom line wrong. And when trained people look for errors on purpose, they find about 60% of them on average. A single owner checking their own work, with no second reader, is the weakest version of that.
The risk isn't that the person who built it made a mistake. It's that nobody else could catch one.
Two famous examples
Big organizations with smart people have the same problem.
JPMorgan, 2012. In its own 2013 task force report on the trading losses known as the London Whale, JPMorgan described errors in a risk model run in spreadsheets. As quoted by the European Spreadsheet Risks Interest Group, the report says one step divided by the sum of two rates instead of their average, as the modeler intended. The report says this likely muted volatility by a factor of two and lowered the risk estimate. EuSpRIG lists the causes as logic that wasn't reviewed and manual copy-and-paste steps.
A famous economics paper, 2013. When three researchers at UMass Amherst tried to reproduce a widely cited Harvard paper on public debt and growth, they found a coding error in the working spreadsheet that left five countries out of the analysis entirely: Australia, Austria, Belgium, Canada and Denmark. To be fair to the original authors, the spreadsheet error was one of three problems the critique raised, alongside data exclusions and a weighting choice. But it was the one a second reader could catch only after getting the actual file.
Both cases share the pattern that matters for a 100-person company: the logic lived in one file, and a second person didn't check it until it was too late.
How to tell if yours is a risk
Not every personal spreadsheet is a problem. A sales manager's call list is fine. Ask these questions about the sheet you're worried about:
Six questions for the spreadsheet you're worried about
| Question | If the answer is yes |
|---|---|
| Would a process stop if this person was out for two weeks? | Coverage risk. Fix this first. |
| Does it decide money: prices, pay, purchase quantities, invoices? | An error costs real dollars. It needs a second reader. |
| Does someone paste data into it from the ERP every day or week? | Copy-and-paste is where rows get missed. A feed can replace it. |
| Does it live on one person's laptop or desktop? | One dead hard drive away from gone. |
| Does it pull from other files through links? | A renamed file or moved folder can break it quietly. |
| Has anyone but the builder checked the formulas in the last year? | If not, nobody knows if it's right. |
Two or more yeses on a sheet that runs a core process means it's worth an afternoon of attention. You don't need to start over.
Three ways to fix it without throwing it away
The person who built the sheet should lead all three. Frame it as protecting their work, not taking it away. They know why every odd formula is there, and some of those reasons are good.
1. Write down what it does, then have a second person run it
One page is enough: what goes in, where it comes from, what comes out, who uses the result, how often it runs, and what "looks wrong" means. Then have a second person run it once while the builder watches, and once alone.
Excel has tools that help map a sheet you didn't build. Trace Precedents and Trace Dependents draw arrows showing which cells feed a formula and which formulas use a cell. If Microsoft 365 is installed, Excel's Inquire add-in can analyze a workbook's formulas and warnings, compare two versions cell by cell, and draw a map of the other files a workbook links to. That map alone often surprises people.
For capturing the judgment calls that never make it into a formula, see our guide on capturing what a veteran employee knows.
2. Move it off one laptop
Put the file somewhere shared, backed up, and controlled. If you use Microsoft 365, Microsoft's co-authoring guide says the workbook needs to be on OneDrive, OneDrive for Business or a SharePoint Online library. It also notes that on-premises SharePoint sites don't support co-authoring. Shared storage means a second person can open the current file, not a copy emailed last month.
While you're at it, decide who may edit it and who only reads it. The more people who can change formulas, the more you need a simple rule: changes get noted on a "changes" tab with a date and a name.
3. Replace the copy-and-paste, keep the sheet
Most of these sheets have a weekly ritual: export from the ERP, paste into the "data" tab, check that the row count looks right, refresh. That's the step most likely to go wrong and the easiest to automate. A scheduled feed can pull the same data into the same tab, every time, with a check that the totals match the ERP. The person keeps the sheet they know. They just stop typing into it. Our guide on connecting spreadsheets to your ERP covers the options.
Then add a test. Panko's paper notes that software teams check programs against cases where the right answer is known. Do the same: keep one past month where you know what the answer should be, and rerun it after any change. If the result moves, something changed that shouldn't have.
When to replace it instead
Here's the other side: spreadsheets are a good tool, and replacing one with custom software has its own cost and its own risks. Keep the sheet if it's used by one or two people, the logic is stable, and the three fixes above make it safe.
Replace it when:
- Several people need to enter data at the same time, like order entry or a shared schedule.
- It has become your system of record, meaning the "real" inventory or price list lives there and not in the ERP.
- It needs a record of who changed what, for an audit or a customer.
- Fixing it takes the builder more hours each month than it saves.
Even then, the spreadsheet is the best requirements document you'll ever get. It shows exactly what the business needs, in the order it needs it.
Where AI fits, and where it doesn't
An AI assistant can help a second person read a sheet they didn't build: explain what a long formula does, list where a tab gets its data, or draft the one-page description from step 1. That's useful, and it's faster than reverse-engineering it alone.
But it doesn't replace the second person. AI can explain a formula confidently and be wrong, and the research above says people already miss errors. Use AI to speed up the checking, then have a person confirm each answer against the sheet. And before any AI work runs on the numbers themselves, clean up the data underneath.
A simple plan for this month
- Ask each department head: what spreadsheet would hurt most if its owner were out for two weeks?
- Run the six questions on the top two or three answers.
- For each one that scores two or more, book an afternoon: the builder writes the one-page description and a second person runs it.
- Move those files to shared storage.
- Pick the one with the worst copy-and-paste ritual and replace that step with a feed.
None of this needs a big project. It needs an owner to ask the question before the vacation, not after.
Common questions
Why is a spreadsheet only one person understands a risk?
Two reasons. The process stops when that person is out, and nobody else can check whether it's right. Research summarized by Raymond Panko found errors in 94% of spreadsheets examined in 85 intensive inspections.
How common are spreadsheet errors?
Panko's 2015 summary reports an average cell error rate of 3.9% across 14 lab studies with 967 people, and errors in 94% of spreadsheets in 85 intensive inspections. Errors are rare cell by cell, but large sheets very likely have at least one wrong bottom-line number.
Do we need to replace the spreadsheet with software?
Usually not. Document what it does and train a second person, move it to shared storage, and replace the copy-and-paste steps with a feed from your ERP. Replace it when several people need to enter data at once or it has become your system of record.
What Excel tools help someone understand a spreadsheet they didn't build?
Trace Precedents and Trace Dependents show which cells feed a formula. With Microsoft 365, the Inquire add-in can analyze a workbook, compare two versions cell by cell, and map the other files it links to.
Where should a shared spreadsheet be stored?
Somewhere shared and backed up. For Excel co-authoring, Microsoft says the file must be on OneDrive, OneDrive for Business or SharePoint Online; on-premises SharePoint doesn't support co-authoring.
Can AI explain a spreadsheet nobody understands?
It can help a second person read formulas and draft a description faster. It can also be wrong with confidence, so a person should confirm each explanation against the sheet.
How do we test a spreadsheet after changes?
Keep one past period where you know the right answer and rerun it after any change. If the result moves when it shouldn't, something broke.
Sources
- Raymond Panko, What We Don't Know About Spreadsheet Errors Today (EuSpRIG 2015, arXiv)
- EuSpRIG, Spreadsheet horror stories (JPMorgan Task Force Report entry, quoting pp. 131-132)
- Herndon, Ash and Pollin, Does High Public Debt Consistently Stifle Economic Growth? (PERI working paper 322, April 2013)
- Microsoft Support, Display the relationships between formulas and cells
- Microsoft Support, What you can do with Spreadsheet Inquire
- Microsoft Support, Collaborate on Excel workbooks at the same time with co-authoring
How to connect spreadsheets to your ERP so nobody retypes
How to capture what your veteran employees know
Do you need clean data before AI? Usually not a data lake