A pharmaceutical distributor came to us with a problem they did not think of as a software problem. Their entire operation, the regulated parts included, ran on Excel and paper. Which batch went to which customer, and whether it had stayed cold on the way. One person held the master stock file, someone else held a slightly different copy, and reconciling the two took the better part of a week.
You hear "we just need to get off spreadsheets" and it sounds like a small job. It is not. A spreadsheet that has run a business for a decade is not a mess waiting to be cleaned up. It is the specification. The formulas, the colour coding, the little conventions everyone follows without discussing them, all of it encodes years of decisions that were never written down anywhere else. Nobody sat down and designed this. It accreted. And that is exactly what makes it hard, because accreted rules are still rules, they just have no author left to ask.
The colour coding is a state machine nobody drew
Open one of these files and the first thing you notice is the colours. Yellow rows, green rows, a few angry red ones, some greyed out and struck through. That is a state machine. Rows move from one colour to the next in a particular order, certain transitions are allowed and others simply never happen, and somewhere there is a person who applies those rules in under a second and could not tell you what they are.
There is no legend. There never is. When we asked what yellow meant, the answer started with "oh, yellow just means, you know" and then trailed off into a demonstration rather than a definition. That is your specification talking, and it is talking in a language it does not know it is speaking.
The maintainer is the real spec, and she will not tell you the rules
The person who keeps the sheet alive is the actual specification for the system you are about to build, and the most important thing to understand is that they will not hand you the rules. Not out of protectiveness. They genuinely do not experience them as rules. To them it is just how you do it, the same way you do not experience the grammar of your first language as a set of rules while you are speaking it.
The clearest example we found was buried inside a formula. One column worked out whether a batch could be offered to institutional buyers, the large hospital and government tenders. It looked like an ordinary date calculation until you clicked into the cell and read a nested IF three levels deep. Sitting in the middle of it was a condition that quietly excluded any batch with less than four months of shelf life left from that customer category, while still happily allowing the same stock to go to retail pharmacies. Nobody had mentioned this to us. When we asked, the reply was "oh, hospitals reject anything that close to expiry, they'll send the whole consignment back". That was a hard commercial rule with real money attached, and it existed in exactly one place on the planet: a spreadsheet cell the auditor never opened and we very nearly rebuilt without.
This is why, on that engagement, we made the batch and not the product the unit of stock. A quantity on hand tells you almost nothing in pharmaceutical distribution. Which lot, expiring when, having been where, is the whole game. Half the rules we recovered turned out to be rules about lots wearing the costume of rules about numbers.
You migrate the history, or you quietly start a second business
There is a strong temptation to launch clean. Open the new system with today's stock count, move forward, leave the past behind in the old files. Do not do this. If day one of the new system does not reconcile against the last day of the spreadsheet, you have no way of telling whether the new system is even right. So we migrated the historical records instead of starting from a zero balance, and we treated the first reconciliation as the acceptance test. The new system had to reproduce the closing position of the old one, down to the batch, or it had not passed.
The other half of this is harder, because it is political rather than technical. You have to retire the spreadsheet at go-live. Not soon after. At. A spreadsheet kept running "just in case" is not a safety net, it is a competing system of record, and inside a month it will have quietly become the real one again, because it is faster for the person who already trusts it. Then you are running two businesses and reconciling between them by hand, which is precisely the problem you were hired to delete, now with extra steps.
How to interview a spreadsheet
You cannot get the rules by asking someone what the rules are. You get them by reading the file the way you would read a reluctant witness. These are the things we now go looking for before we ask a single person a single question:
- The exceptions. Any row that breaks the visible pattern is a rule you have not learned yet. Work out why it is different before you build anything on top of the pattern.
- Cells where a formula has been typed over with a hard number. Somebody overrode the model on purpose, in a hurry, and that override is a rule the formula got wrong. It will recur.
- The conditional formatting rules, read directly rather than by eye. The colours are the state machine and the format rules are its transition table, written out in full for once. Open them first.
- The tab everyone forgot. There is always one, usually named
Sheet3or a supplier's surname, and it is either completely dead or quietly load-bearing. You want to know which before go-live, not after. - The maintainer's weekly rhythm. Whatever they do every Friday is a batch job in disguise. Whatever they do at month end is a reconciliation routine you are going to have to reproduce exactly.
The thing we got wrong
We got that first reconciliation wrong, and it cost us the better part of a week. The master sheet had a balance column, and we took it to mean stock physically on hand. It did not, quite. The maintainer added incoming stock to the balance the moment a supplier confirmed dispatch, not when the warehouse actually received it, so on any given day her balance silently included a quantity of goods that were still on a truck somewhere on a highway. Our new model counted stock at receipt, which is correct, and so our day-one figures came out "wrong" against a sheet that was itself doing something undocumented. We burned days chasing a discrepancy that was really a disagreement about definitions. The lesson was not about goods in transit. It was that even the column headings lie, and the only authority on what a number means is the person who has been typing it in for years. Ask them before you trust the label.
Let some of them live
After all that, it would be easy to walk away deciding the spreadsheet is the enemy. It is not, and believing it is is how you end up building a rigid system around a question that had not finished changing shape. A spreadsheet is genuinely better than your platform at some things: a one-off analysis you will run once and throw away, or a shape of data that has not yet stopped moving. On the contract-farming platform we built for an agribusiness group, we were strict about digitising the operational record, every field operation logged against a real plot, because that record had stabilised and farmers were being paid off it. But we deliberately left the edges open, so field and office staff could still pull data out and think in a spreadsheet, because the moment you force every ad hoc question through a form, people stop asking the questions.
The rule we work to now is short. If a spreadsheet is running the business, it is a specification: you migrate it, history and colours and buried formulas and all, and then you switch it off. If it is only helping someone think, leave it well alone. The expensive mistake is treating the first kind like the second, and finding out in an audit which one you actually had.