The short answer
The spreadsheet is not the problem. It is a working specification of a process nobody wrote down, maintained by someone who understands the business. Treat it as the requirements document, not as a mess to be swept away.
Projects fail here by rebuilding what the spreadsheet appears to do rather than what it actually does, and discovering the difference after go-live.
Why it survived
- It was changed the day the business changed, without a ticket
- The person who owns it understands the process completely
- It handles the exceptions that a system would reject
- Nobody had to be trained on it
- It produces exactly the number the business argues about
Any replacement has to be better on at least a few of these. Software that is more rigid and slower to change is a downgrade, whatever else it offers.
Extract the rules first
- Take a copy and go through every formula, including the ones that look wrong.
- List the rules in plain language, and mark which ones surprise people.
- Sit with whoever maintains it and ask why each unusual rule exists.
- Note every manual step: what gets pasted in, what gets checked by eye.
- Find the exceptions, because the exceptions are where the real logic lives.
Step three is the one that pays. Most odd-looking rules exist for a reason that was never written down, and the reason usually still applies.
Bring the owner with you
The person who maintains the spreadsheet is the single most valuable contributor and often the most resistant, for understandable reasons. It is their work, their expertise, and occasionally their job security.
Involve them as the authority on the process rather than as someone being replaced. If they are not on board, they will be the one who finds every flaw in the replacement, and they will usually be right.
Run both for a while
| Approach | Risk |
|---|---|
| Switch over on a date | Any missed rule is discovered in production |
| Run both and compare outputs | Slower, and worth it |
| Replace one section at a time | Good where the sheet is modular |
| Keep the sheet as a check | Sensible for a period after go-live |
Comparing outputs for a full cycle finds the rules you missed. Every difference is either a bug in the new system or a rule nobody had documented, and both are worth knowing before you rely on it.
Keep what made it good
Flexibility. If changing a rate in the new system needs a developer and a release, people will export to a spreadsheet and you will be back where you started with an extra system.
Put the things that change often in configuration that a business user can edit, with a record of who changed what. That single decision determines whether the replacement lasts.