Outgrown your spreadsheets? The signs you need a proper business system

Almost every business system we have ever replaced started life as a spreadsheet. That is not a criticism. A spreadsheet is the fastest way to get a process out of someone’s head and into something a team can share. The trouble starts when the spreadsheet stops being a tool and becomes the system, and nobody notices the moment it happens.

This piece is about spotting that moment, being honest about what it is costing, and choosing between the three sensible responses: tidy the spreadsheet, buy a tool, or build something around the process. All three can be right. The mistake is drifting along without deciding.

The signs

More than one person edits it. The first copy appears, then “the real one”, then a column someone added that nobody else understands. Two people answering the same question from the same sheet get different answers.

It has a name. When a file is called “Master tracker v7 FINAL (use this one)”, it is a system.

Someone owns it, and they are nervous about holidays. One person knows how the formulas hang together. Everyone else is afraid to touch it. When that person is away, the process stops or goes back to email.

You re-key the same thing twice. A customer’s details are typed into the spreadsheet, then into the accounts package, then into an email. Every re-keying is a chance for the three to disagree.

Reporting means rebuilding. The monthly figures take a morning because the sheet was never designed to answer the question being asked now.

It holds information it should not. Personal data about customers or staff, sitting in a file that has been emailed around, copied to laptops and shared with people who no longer need it.

Nobody trusts it, but everybody uses it. The sheet is checked against email before anyone acts on it. That checking is the system’s real cost, and it is invisible because it is spread thinly across everyone’s day.

What it is costing

The visible cost is time: the re-keying, the reconciling, the morning spent on the report. The bigger cost is usually invisible. Decisions made on figures that are a week stale or subtly wrong. Opportunities that fell between two tabs. The thing you cannot do because nobody can get the data out in the shape you need.

There is also risk. A spreadsheet has no audit trail. Nobody knows who changed a number or when, and there is no way to put it back. If it holds personal data, that is a data-protection problem as well as an operational one, and “it was in a spreadsheet” is not an answer anyone wants to give.

The options

Tidy the spreadsheet. Sometimes the right answer. One copy, one owner, locked formulas, a clear sheet for input and another for reporting. Cheap, quick, and it buys time. It does not fix the audit trail or the re-keying.

Buy a tool. An off-the-shelf CRM, project tool or accounts add-on. Fast to start and well understood. The question to ask is how much of your process you will have to bend to fit it, because a subscription you work around is a spreadsheet with a monthly fee. We have written more on how to make that call.

Build the right thing. When the process is genuinely yours, when it is the thing that makes the business work, a system designed around it pays back. The brokerage CRM we built for ABA Commercial Insurance replaced prospecting spreadsheets and a renewal tracker with views that follow how the team actually sells, from prospecting through new business to renewals, with history that cannot be rewritten and sign-in through the Microsoft accounts the firm already had.

What moving the data across actually involves

This is the part people worry about most, and with reason: the spreadsheet is the only record there is. In practice it breaks into four predictable steps, and only one of them is technical.

Agree what the data means. The column called “Status” has eleven values in it, three of which mean the same thing and two of which only one person understands. Before anything is loaded anywhere, somebody writes down the real list and decides where each stray value goes. This is the slow step, and it is slow because it is a business conversation, not a programming one.

Clean it. Duplicates, half-typed email addresses, dates in two formats, rows that were “parked” years ago and never picked up. Most of this can be found automatically once someone has decided the rule for each case. Fixing it by hand, row by row, is the trap.

Rehearse. Load the data into the new system, let the people who use it every day poke at it for a week, then throw that load away and do it again with what they found. The real load should be boring, because it has already happened twice.

Switch over once. There is a short window where the old sheet is read-only and the new system is live, and one person owns that window. Running both in parallel for months feels safer and is not: it doubles the re-keying and nobody knows which one is true.

Keep the spreadsheet for what it is good at

None of this is an argument against spreadsheets. A spreadsheet is still the best tool in the building for a one-off analysis, a quick model, a what-if, or a list that lives for a fortnight and is then forgotten. The point is to stop asking it to be the record.

A useful test: if something is written in the spreadsheet and nowhere else, and somebody would be upset if it were lost, it belongs in a system. If it is a scratchpad for thinking, leave it alone. The best systems we have built export cleanly to a spreadsheet for exactly this reason, so people can carry on doing their thinking there while the record lives somewhere safe.

How to decide

Write down the process the spreadsheet runs, as steps. Mark the steps where information is typed in twice, where two people need to see the same thing, and where a decision depends on a figure. If most of the marks fall in one place, that is the part of the process worth building properly. Start there, not with everything at once.

If you are not sure which of the three options fits, that is a normal place to be. Tell us what the spreadsheet does and we will tell you honestly which it is, including when the answer is to tidy it and leave it alone.