Moving a business process off spreadsheets without stopping work

Why it became a spreadsheet

Nobody chose this. Someone needed to track something on a Tuesday, a spreadsheet was open, and it worked. Then a column got added, then a second sheet, then a macro, and now the business runs on it and one person understands the formulas.

That is not a failure of judgement. It is what happens when a tool is available immediately and fits the problem well enough. It is worth saying out loud because the migration goes better when nobody is being blamed for the thing that has been holding the process together.

The four failure modes

Spreadsheets fail in the same four ways, more or less regardless of the business.

• One person understands it. The bus-factor problem. The formulas are undocumented, the layout encodes rules nobody wrote down, and holidays become a risk to operations.
• No audit trail. A figure changes and there is no record of who changed it, when, or what it was before. When a dispute arrives, there is no way to reconstruct what was true at the time.
• Errors are silent. A formula that stopped covering the last twelve rows does not announce itself. Studies of business-critical spreadsheets have repeatedly found error rates that would be treated as a crisis in any other system, precisely because nothing flags them.
• Concurrency and version drift. Two people edit, one saves over the other, and a copy called final v3 (John’s) starts circulating. Shared cloud spreadsheets soften this but do not fix the underlying issue: there is no rule stopping two people booking the same stock.

What spreadsheets are still best at

A system should replace the process, not the thinking. Spreadsheets remain the best tool for one-off analysis, modelling a scenario, and working something out before you know its shape. The rule of thumb: if the same sheet gets used the same way every week by more than one person, it has stopped being analysis and become an application in disguise.

A good system usually exports cleanly to a spreadsheet, so the people who like working that way still can — from data everyone agrees on.

The order of operations

1. Pick one process. The order book, the quote log, the stock sheet. Not all three. Each has its own data, its own users and its own risk.
2. Write down what the columns actually mean. Every spreadsheet has at least one column whose meaning drifted, or that means two things depending on the row. Find those now, not during development.
3. Model the data properly. A spreadsheet is one flat grid. Real work is customers, orders, lines and deliveries with relationships between them. Getting this right is most of what makes the system feel obvious later.
4. Clean before you move. See below. This is the step people skip.
5. Parallel run. Both systems, same data, for a defined period — long enough to cover a month-end. Compare the outputs and investigate every difference.
6. Cut over, then retire the sheet deliberately. Not “stop using it”. Make it read-only, with a date on it.

Cleaning the data first

The traps are consistent enough to check for by name, and every one of them is cheaper to fix in your own time than as billable development:

• The same entity, several spellings. ACME Ltd, Acme Limited and acme are three customers to a computer and one to you.
• Dates. The classic. Mixed day-first and month-first entries in one column, text that looks like a date, and dates stored as serial numbers. A misread date column is the most common cause of a migration that looks fine and is not.
• Leading zeros and codes that look numeric. Part number 00734 arrives as 734, and a code like 3E2 becomes 300. Spreadsheets do this silently on import as well as export.
• Units and currency in the same cell as the number. “1.5 t” and “1500kg” in one column need a decision before, not during.
• Merged cells, colour as meaning, and notes in the margin. If red means urgent, that is a field the new system needs, not decoration to be discarded.

The cutover

Cut over at the quietest point in your cycle, not at month-end. Keep the old spreadsheet as a frozen read-only copy for a good while — it costs nothing and settles arguments. Expect a fortnight where things take slightly longer while people learn the new path, and plan for it rather than being surprised by it.

Above all, resist moving all five processes at once. One live, working process beats five half-built ones, and the second migration always goes faster because the hard thinking about your data has already been done. That is the same logic behind how we phase a build, and it is also what keeps the price honest — see what bespoke software actually costs.