When a spreadsheet becomes a system: signs it is time to replace it

A spreadsheet is the right tool for a calculation one person owns. Problems start when it is shared, when it is the only record of something, and when the person who built it is no longer the only person who edits it. At that point it is a system, with none of a system's protections. These are the signs that it is time to move the logic into software, and a few notes on how to do it without a large project.

Signs

• Multiple copies. "Which version is current?" is being asked in email.
• Manual re-entry. Data is typed from the spreadsheet into another system, or from another system into it.
• Hidden rules. Formulas encode business rules (pricing bands, approval thresholds) that are not written down anywhere else.
• Fear of editing. People add columns at the far right rather than change a formula they do not understand.
• No history. Nobody can say what a value was last month, or who changed it.
• Access by forwarding. The file goes to whoever asks, including people who should see only part of it.

What to keep

The spreadsheet is also the specification. Its columns are the data model, its formulas are the rules, and its tabs are the workflow. Before any software is written, walk through it with the people who use it and write each formula down in plain language. Many turn out to be obsolete; the rest become the acceptance tests for whatever replaces it.

How to replace it in phases

Move the data first: a proper database with the same columns, a form for the entries people make most often, and the spreadsheet rebuilt as a report that reads from the database. People keep their familiar view while the record of truth moves somewhere with history and access control. Then move the rules, one at a time, checking each against the old formula on the same inputs. Then connect the other systems, so the re-entry stops. Each phase is weeks, not months, and each one leaves the business better off even if the next never happens.

What not to do

Do not start by choosing a platform. Do not rebuild every tab. Do not remove the spreadsheet on day one; let it become the read-only report it should have been, and retire it when nobody opens it.