Can we not just move to a shared cloud spreadsheet?

Often yes, and it's the cheapest thing to try first. It stops people overwriting each other and it gives you a version history. It doesn't let you enforce a rule, though, so if your problem is that a number can be wrong and nobody notices, you'll hit the same wall a little further along.

How long does replacing a spreadsheet take?

It depends almost entirely on how messy the data is and how many rules the business really has, and nobody knows either of those for sure at the start. I give a firm estimate once I've seen the real file. A description of it isn't enough, because the file is usually where the surprises are.

What happens to the data we already have?

It gets imported by a pipeline, so nobody retypes it, and that import is written to be run repeatedly and checked before it commits anything. Some rows won't fit the stricter structure. Those should be flagged for a person to decide on, and the import shouldn't guess.

Will we be locked into a system only you understand?

That's a fair thing to worry about. Ask for the structure to be defined in code and not assembled by clicking, ask for an export that gives you your data back in a plain format, and ask what handover looks like. All three are reasonable requests, and I'd pay attention to how someone answers them.

Do we have to replace everything at once?

No, and usually you shouldn't. The part worth moving is the shared record that several people depend on. Personal working files, models and one-off analysis can stay in a spreadsheet indefinitely, as long as they read from the system and aren't what everyone else reads from.

Would a no-code tool do the job instead?

Sometimes, and when it does, it's just the right answer. The limits show up once the business starts leaning on it. There are no tests, no meaningful version history, and changes get made directly against the live configuration. If a business depends on it running correctly unattended every day, those gaps become the main risk.

All guides

How do I know when we've outgrown our spreadsheet, and what do we replace it with?

Replacing spreadsheets with something that scales

When a spreadsheet is the right tool

A spreadsheet hands one person a database, a calculator and a report writer in a file they can email. There's no project, no specification and no developer involved. For an enormous amount of real work it's the correct tool, and it always will be.

Keep the spreadsheet when the work is basically yours, when its layout changes week to week, and when a wrong answer just means you notice and fix it, without a customer finding out. Budgets, models, one-off analysis, anything you're still thinking your way through. For those, the flexibility is the whole point.

Most spreadsheets never need replacing. The ones that do have usually stopped being a document and turned into a system along the way, without anybody deciding that they should.

Signs you've outgrown one

Several people edit the same file. Someone has it open, and someone else's changes vanish.
Copies have diverged. There are three versions with dates in the filename, and nobody can say with any confidence which one is right.
No record of who changed what. A figure is wrong, and nobody can say when it became wrong or what was decided on the back of it.
Formulas nobody dares touch. They were written by someone who's left, and they work, but that's all anyone knows.
The file is all that stands between you and a lost order. If it were deleted tomorrow, work would stop and some of it would never be recovered.
A rule that has to be enforced. You've written the rule down and explained it twice, and it still gets broken.
It's slow to open. Usually that means the file is carrying years of history it was never designed to hold.

You can live with any one of these. If you've got several, you're running a system that nobody designed.

What an application gives you

A spreadsheet trusts everyone to do the right thing. Every cell can be edited by anyone who can open the file, and every rule about what belongs in it lives in somebody's memory, or in a tab called Instructions that nobody reads. The file will happily accept a date in the wrong format, a duplicate customer, or an order with no price against it, because it has no way of knowing any of that's wrong.

An application just won't let the wrong thing in. Combinations that don't make sense can't be entered at all. A search runs before anything offers to create a new record, so reusing one is the easy path and creating a duplicate is the hard one. And the system decides who can see what, so it doesn't rely on everyone being careful.

The Lead Data Platform case study is an example of this.

That's most of what you're paying for. The nicer screen is a small part of it.

Moving the data over

Every project like this has a stretch where you find out what's really in the file, as opposed to what everyone believes is in it. It's part of the job, and it doesn't mean something has gone wrong.

Free-text columns are the usual culprit, and missing identifiers are the other. The Relational Archive System case study ran into both.

Some of it can't be recovered at all. That project had records like this too, so the importer flagged them for a person to fix. I think a migration that guesses is worse than the spreadsheet you started with, because it hands you a system full of mistakes that looks clean and authoritative.

So budget for cleaning, and ask early what the plan is for rows that don't fit.

You don't have to move everything

The part that needs to move is the shared, contested part, meaning the record several people depend on. That's where the cost of being wrong lands.

Personal working files can stay as they are. Exporting to a spreadsheet to build a model or put a board pack together is something spreadsheets are excellent at. What matters is which copy is the record, whatever tool it's in. Export all you like, just don't let the export become what everyone else works from.

A useful test for any given file is whether anything else depends on it being correct. If something does, it belongs in the system. If it's your own thinking, keep the spreadsheet.

Where to start

Don't begin by specifying the replacement. Begin by writing down every rule people currently follow from memory, like what has to be filled in, what must never be blank, what happens when two records look like the same thing, and who may change a price.

That list is the real specification. If it's short and everybody follows it, keep the spreadsheet and save your money. If it's long, or somebody breaks one of those rules most weeks, you've found the case for an application, and you've already written most of the brief.

Questions people ask

Can we not just move to a shared cloud spreadsheet?: Often yes, and it's the cheapest thing to try first. It stops people overwriting each other and it gives you a version history. It doesn't let you enforce a rule, though, so if your problem is that a number can be wrong and nobody notices, you'll hit the same wall a little further along.
How long does replacing a spreadsheet take?: It depends almost entirely on how messy the data is and how many rules the business really has, and nobody knows either of those for sure at the start. I give a firm estimate once I've seen the real file. A description of it isn't enough, because the file is usually where the surprises are.
What happens to the data we already have?: It gets imported by a pipeline, so nobody retypes it, and that import is written to be run repeatedly and checked before it commits anything. Some rows won't fit the stricter structure. Those should be flagged for a person to decide on, and the import shouldn't guess.
Will we be locked into a system only you understand?: That's a fair thing to worry about. Ask for the structure to be defined in code and not assembled by clicking, ask for an export that gives you your data back in a plain format, and ask what handover looks like. All three are reasonable requests, and I'd pay attention to how someone answers them.
Do we have to replace everything at once?: No, and usually you shouldn't. The part worth moving is the shared record that several people depend on. Personal working files, models and one-off analysis can stay in a spreadsheet indefinitely, as long as they read from the system and aren't what everyone else reads from.
Would a no-code tool do the job instead?: Sometimes, and when it does, it's just the right answer. The limits show up once the business starts leaning on it. There are no tests, no meaningful version history, and changes get made directly against the live configuration. If a business depends on it running correctly unattended every day, those gaps become the main risk.

Where this came from

This guide is written from real work rather than from research. These are the engagements it draws on.

Lead Data PlatformOutbound Marketing Agency Relational Archive SystemCultural Archive Business

If this left you with a question it did not answer, that is worth an email rather than another page of reading.

Start a project