Almost every established business has one. A spreadsheet that has been maintained for years, that runs a significant part of the operation, and that one person understands. It is usually treated as an embarrassment and a problem to be eliminated.
It is also the most valuable document in the business, and treating it as a problem is how modernization projects lose the thing they should have been protecting.
What is actually in it
A pricing spreadsheet corrected over eight years contains the outcome of every argument the business has had about pricing. The base rates, yes, but also the exceptions: the customer type that gets a different multiplier, the job size where the formula changes, the material that is priced differently because a supplier contract says so, the seasonal adjustment somebody added after a bad quarter.
None of that is written down anywhere else. It exists as formulas, as a column somebody added, and as the judgement of the person who knows which cells to touch.
A scheduling spreadsheet contains the real constraints: which crew can do which work, how long jobs actually take rather than how long they are quoted, which customers cannot be visited before ten, and which combinations of jobs are a bad day.
These are business rules that have been tested against reality repeatedly. Software built without them will be technically correct and operationally wrong.
Why the risk is genuine
The same properties that make the spreadsheet valuable make it dangerous.
An audit trail may be missing or incomplete. Check whether the spreadsheet platform records who changed a formula and whether that history is retained long enough to investigate an error.
Validation may cover some cells but not imported data or copied worksheets. A mistyped figure can produce a wrong quote unless the relevant checks still apply.
Access controls may apply to the file rather than individual business actions. Review who can change calculation rules, not only who can open the workbook.
Version history and backups may exist, but somebody needs to verify that the required version can actually be restored.
And there is one person. When they leave, the rules leave with them, because the formulas record what happens without recording why.
Extraction is the work
The valuable phase of a modernization project is not building the replacement. It is reading the file properly.
Go through the formulas systematically and write down what each one does in business terms. Then sit with the person who maintains it and ask about each rule: why is it there, when does it not apply, what was the case that caused it to be added.
That conversation produces the specification. It also produces the surprises. Expect to find rules that contradict each other, rules that were correct in a situation that no longer exists, and rules nobody can explain because the person who added them left.
Resolve those before implementing anything. A contradiction copied into code becomes a defect with a business cause, which is the hardest kind to diagnose.
Design for the sequence people already use
The second common failure is changing the tool and the process at the same time.
People work in a sequence. They have learned where things are and what order to do them in. A new system that is better in the abstract but reorders that sequence will be worked around, and the workaround will be a new spreadsheet.
Build the replacement around the existing sequence. Once it is adopted, process improvements can follow. Bundling them into the same change doubles the resistance and makes it impossible to tell which part failed.
The spreadsheet does not disappear
This is the part people get wrong in the other direction.
Spreadsheets are extremely good at analysis. What they are bad at is being the source of truth for operational data.
The correct end state is that the system holds the data and enforces the rules, and the spreadsheet becomes an export. People can still pivot, model and experiment. The difference is that the export can be regenerated at any time and never has to be corrected by hand, because the correction happens in the system.
Taking spreadsheets away entirely is a fight you will lose and should not pick.
Prove the migration
History matters. Years of records are part of what the file is worth.
Clean it, deduplicate it with a documented rule, import it, and produce a reconciliation report showing counts and totals on both sides. Then run the old and new in parallel on real work until the numbers agree, with exit criteria written down at the start rather than decided under pressure.
Agreement between the systems gives operators a concrete reason to retire the old operational file.
Preserve exceptions without preserving accidents
Take a copy for analysis and keep the original operational file unchanged while the rules are being extracted. Identify formula cells, manual overrides and values imported from another source. A number typed over a formula might be an approved exception, a temporary correction or an accidental edit. The cell alone cannot tell you which.
Ask the operator to demonstrate a normal case and an exception from start to finish. Capture the inputs, intermediate decisions and expected outcome as test fixtures for the replacement. If a rule depends on an external document, record that source instead of treating the current spreadsheet value as permanent policy.
When the old file and the new system disagree, classify the difference. It may be a rounding rule, a date interpretation, an omitted exception or an existing spreadsheet error. Do not change the new implementation solely to make totals match if the old result is known to be wrong. Agree the corrected rule, document the affected records and keep the evidence with the migration.
That process preserves the operating knowledge while allowing the business to retire mistakes deliberately. The goal is not a pixel perfect copy of a workbook. It is a system whose rules the operators can explain and whose results they can check.