The consultant's spreadsheet that became critical

A spreadsheet becomes critical the day revenue or payroll depends on one file. A critical spreadsheet replacement means moving that file's logic into a database-backed application with access control, an audit trail, tested backups and a real schema — done as a parallel run, not a big-bang cutover, so the business never stops.

Key Takeaways

• A spreadsheet becomes critical when the business stops without it — at that point it is production infrastructure, whatever the file extension says.
• The failure pattern is predictable: one owner, a shared password, silent formula errors, and backups nobody has ever restored.
• Document behaviour before code: every formula that carries business logic becomes a rule in the new system.
• A replacement must add what the sheet cannot: a real schema, role-based access, an audit trail and a tested restore.
• Run old and new side by side for one full business cycle before you cut over.
• Rebuilding is not always right — for one careful user, versioning and a backup beat a new app.

How does a spreadsheet quietly become critical infrastructure?

It happens through scope creep, not a decision: one person builds a tracker, colleagues start editing it, a report feeds payroll, and within two years a single workbook holds the stock ledger, the client list and the commission maths. Nobody approved it; the business simply stopped having an alternative.

The pattern rarely changes. A consultant builds a workbook to track commission payments for a dozen staff. Someone asks for a stock tab, then a supplier tab, then a month-end report the accountant signs. Formulas start referencing three tabs away; a macro emails the report; the file grows to forty sheets. Each addition is sensible. None comes with documentation, access control or a tested backup. Then the consultant's contract ends, and the business discovers the knowledge left with them. Engineers call this a single point of failure — one component whose loss stops the whole system.

Which signals say you need a critical spreadsheet replacement now?

Count the people hurt if the file corrupts at 9 a.m. If the answer is "everyone in operations", you need a replacement: version conflicts, #REF! errors reaching reports, one person who alone understands the macros and an untested backup are all measurable, decision-ready signals.

A spreadsheet rarely fails loudly. It fails politely, one silent error at a time, until a reconciliation finally disagrees.

The signals we hear about most:

• Two copies of the file disagree, and nobody knows which totals are right.
• A #REF! or #N/A has appeared in a figure a manager has already signed.
• Month-end slips because one person must finish editing before anyone else can open the file.
• The only person who understands the macros has left, or is about to.

Notice the order in which things break. Silent corruption comes first — a sorted range that misses a row, a pasted formula that overwrites a rate. Access fails second. Backups fail last, quietly, because nobody has ever tried a restore.

What should a critical spreadsheet replacement actually include?

Four components carry the load: a relational schema in PostgreSQL or MySQL that makes bad data hard to enter, role-based access control, an audit trail of who changed what, and automated backups with a restore you have actually rehearsed. Reports become queries; the formula buried in row 4,000 becomes one tested calculation.

A schema is the database's structure: tables, columns and rules about what a valid row looks like. An audit trail is a record of every change — who made it, when, and what the old value was. Together they are where bad entry stops: the database refuses a commission rate of 450% instead of silently accepting it.

Backups are the part teams most often fake. A copy on the same drive is not a backup. With PostgreSQL we schedule dumps and, more importantly, rehearse restores:

pg_dump --format=custom --file=commission_backup.dump commission_app
pg_restore --list commission_backup.dump   # rehearse the restore, don't trust the copy

If that second command shows the wrong tables, the backup is decoration. PostgreSQL's backup and restore documentation explains the mechanics; the discipline of testing them is yours. For the application side — screens, roles and reports — we walk through it in our guide to replacing a spreadsheet with a web app.

How do you replace it without stopping the business?

Freeze the scope first, migrate the data second, and cut over only after one full business cycle runs in parallel with matching numbers. The sequence below is the shape we use for a single workbook; every stage ends with a check you can verify.

1. Document before you build. Export every sheet, list the formulas that carry business rules, and interview the current owner. Exit check: a written map of what the workbook does, signed off by whoever depends on it.
2. Model the schema. Turn sheets into tables with keys and constraints, so a duplicated invoice number is rejected at entry, not found at audit. Exit check: deliberately bad rows fail to insert.
3. Build the thin core. Data entry, roles, and the two or three reports the business actually reads. Resist rebuilding every macro; some were one-off fixes.
4. Rehearse the migration. Script the export, clean-up and import, then run it three times into a fresh staging database — never over live data. Reconcile row counts and control totals. Our spreadsheet data migration guide covers the detail.
5. Run both systems for one full cycle. A month-end, a season, an import batch — whatever the workbook's heartbeat is. Disagreements go to the owner, who is usually right.
6. Cut over, then freeze the old file. Make the workbook read-only and archive it; do not delete it. The new system becomes the single source of truth on a known date, not a Friday-night gamble.

Keep, guard or rebuild: which option fits your case?

Rebuild when several teams depend on the file and errors cost money; add guardrails when one small team edits it daily; leave it alone when the worst case is a lost afternoon. Match spend to blast radius — the distance damage travels when the file breaks.

Option	Fits when	What you actually do	Ongoing effort
Keep the workbook	One careful user; failure costs an afternoon	Lock cells, protect sheets, keep a dated copy off the machine	Minutes a week
Add guardrails	A small team edits daily, errors are rare	Move to OneDrive or Google Drive for version history, protect ranges, schedule an off-site backup	An hour a month
Rebuild as an app	Several teams, errors cost money, one owner	Database schema, roles, audit trail, parallel run, cutover	Hosting plus light monthly maintenance

In practice we see mid-sized teams live happily on the middle option for years. A common mistake is jumping straight to a rebuild because an app sounds more professional. A rebuild nobody maintains is worse than the sheet it replaced.

What does running a replacement cost after go-live?

Budget three ongoing costs: hosting for a small database-backed application, a maintenance window each month for updates and backup checks, and somebody on call when month-end runs. In exchange you delete the single-owner risk — knowledge moves from one consultant's head into code, a schema and a runbook.

The cost drivers are ordinary: server size, database storage, backup retention and engineer time. A system replacing a workbook is usually the smallest production system a company runs. Against that, set the spreadsheet's hidden costs — month-end overtime, the reconciliation clean-ups, and the day the file dies. Cloud vendors change their pricing often enough that quoting figures here would mislead you; their own calculators are current, and we can size the system honestly after a review.

What security gaps does a spreadsheet leave open?

Access control tops the list: a shared file password protects nothing once it has been emailed, and download rights put the entire dataset on every laptop that opens it. Add the audit gap — a workbook cannot prove who changed a figure — and the security case writes itself.

Spreadsheets were built for analysis, not authorisation. Everyone who can edit can edit everything: formulas, history, other people's rows. There is no per-role view, no way to let a data-entry clerk add rows without seeing last year's salaries, and no log to settle a dispute about a changed rate. A database-backed system gives you roles, least privilege and a trail you can query — including restricting who can export at all. If the workbook holds client data or staff records, treat that as the deciding argument.

Which mistakes sink replacement projects?

The classic failure is migrating the file instead of the process: teams copy the sheet's columns into a database and faithfully rebuild its quirks. Skipping the parallel run comes second, and treating the consultant's departure as the deadline comes a close third.

• Rebuilding every macro. Some encode rules that died years ago; the parallel run is where you find out which.
• Starting the cutover on month-end day. Choose a quiet date with rollback room, not the busiest one.
• Leaving the workbook's owner out of the build. They hold the undocumented rules, and they will veto the wrong design — better in week two than at go-live.
• No write-off plan for the old file. If it stays editable, someone will edit it, and you have two sources of truth again.

What does a critical spreadsheet replacement look like in practice?

Picture a distribution house where one workbook runs commissions, stock and month-end: fourteen staff, one owner, three days to close the month. A replacement follows the same arc every time — document, build, rehearse, run in parallel, cut over — and the business keeps trading on cutover day.

Here is a composite of the calls we actually get, with details changed. The consultant who built the workbook left two years ago. Commission rates live in a formula three tabs deep; a #REF! error last quarter underpaid two sales staff and nobody could prove the correct figure. The replacement followed the timeline above: two weeks mapping formulas, a small web application on a managed database, three dry-run imports that kept finding dirty data — duplicate customer names, negative stock the sheet silently accepted — then six weeks of parallel runs. The cutover landed on the first of the month; the workbook became a read-only archive; month-end now closes in half a day. We build systems like this — portals, internal tools and analytics platforms, including our analytics platform work for a research institute — and the discipline is the same whether the client is in Kathmandu or abroad.

When is keeping the spreadsheet the right call?

Keep the workbook when one trusted person uses it weekly, the worst realistic failure is a lost afternoon, and nobody outside the team reads its output. Spend the rebuild budget on a tested backup and a one-page how-to instead. Simpler is often the correct engineering answer.

Honesty cuts both ways. If the sheet is one symptom of a wider tangle of legacy tools, a sequenced plan for the whole estate matters more than fixing the first file you found — we set out that thinking in our legacy system replacement plan. And if a rebuild is justified, it should still be the smallest system that removes the risk, not the biggest one the budget tolerates.

In short

A workbook that the business cannot operate without deserves the treatment you would give any production system: documented behaviour, a real schema, roles, an audit trail, rehearsed restores and a parallel-run cutover. Choose keep, guard or rebuild by blast radius, not by fashion — and when you rebuild, build the smallest thing that removes the risk.

People also search for

• How to replace a spreadsheet with a web app
• How to migrate spreadsheet data into a database
• How to plan a legacy system replacement
• Custom software vs off-the-shelf: which fits better?
• Shared hosting, VPS or cloud: where should the new system live?
• What goes into a web development quote?

If a workbook now runs a process you cannot afford to lose, our team can review it, map the risk and scope a replacement your own staff can operate — code, infrastructure and accounts in your name. See the web, mobile and systems work we do, or tell us about the spreadsheet and we will take it from there.