Understand the sheet before replacing it
The spreadsheet has evolved for years and encodes rules nobody has written down: a conditional format that flags something important, a column used for a second purpose, a formula that handles an exception.
Replacing it without understanding it produces a system that is technically better and practically worse, which is how these projects lose the team.
What to keep
- The view people work from — if a grid is the right interface, use a grid
- The shortcuts and habits that make it fast for the people using it
- The flexibility to handle a case nobody predicted, through a notes field or an override
- Export back to a spreadsheet, because people will want it for analysis
The most common complaint after a spreadsheet replacement is that the new system is slower to use. That is a design failure, not user resistance, and it is preventable by watching someone work before designing.
What to add
- Validation, so bad data cannot be entered rather than being corrected later
- Multi-user access without file locking or version confusion
- An audit trail — who changed what, when
- Permissions, so not everyone can see or change everything
- Reliable backup, which a file on a shared drive is not
Migrate the data carefully
Spreadsheet data is messy in characteristic ways: dates as text, numbers with stray characters, merged cells, several tables on one sheet, and rows at the bottom that are notes rather than data.
Profile before migrating and decide the rule for each problem: fix, exclude with a report, or import and flag.
Transition without a cliff edge
Run both for a few weeks. Let people use the sheet if they need to, and compare. Then set a date, help the stragglers, and close it.
Closing it matters. A spreadsheet left available will continue to be used, and then you have two versions of the truth, which is worse than where you started.
What it costs
Replacing a single business-critical spreadsheet with a proper application typically runs £8,000–£25,000 depending on how much logic it encodes and how many people use it.
Below that, consider whether a shared database tool with a spreadsheet-like interface would do the job — for many cases it will, at a fraction of the cost.