A spreadsheet has been outgrown when the business depends on it more than anyone can check. That does not make the spreadsheet a mistake. Most firms start in Excel for good reasons: it is fast, everyone can use it, and it costs nothing extra. The question is what to do once it has become load-bearing, and the answer is often smaller and cheaper than a new system.

This post lists the signs, then four fixes in order of cost, then a short way to choose between them.

Six signs the sheet has outgrown its job

One or two of these is normal. Three or more is the point at which we would stop adding to the sheet and look at a fix.

1. One person understands it. The formulas were built up over years, the colours mean something, and one person is the only one who dares to touch it. If they are away for a fortnight, the monthly figures wait. If they leave, you inherit a file nobody can explain.

2. Versions travel by email. There is a “final”, a “final v2” and a copy someone saved to their desktop. Two people update different versions and someone merges them by hand. Because nobody is sure which one is right, every decision starts with a check.

3. The numbers are typed in again somewhere else. A job is priced in a sheet, then the same figures are keyed into Xero, then into the job system, then into a weekly report. Every re-key is a chance for a slip, and every one costs time whether or not it goes wrong.

4. There is no record of who changed what. A price is different from last month and nobody knows why. Excel can show who changed which cell and when, but only for a workbook kept in OneDrive or SharePoint, and a Microsoft Q&A answer says it does not record formatting changes or edits made to a copy someone has downloaded. A sheet that lives in an inbox has no trail at all.

5. Customers find the errors. A quote goes out with the wrong total, an invoice carries last year’s rate, or a delivery date slips because a row was sorted out of order. When the first person to notice is the customer, nothing inside the business is catching the errors.

6. Simple questions take an afternoon. “What did we quote on roofing jobs last quarter?” or “Which enquiries have we not answered?” should take a minute. If answering means opening four files and filtering by hand, the data is in the wrong shape for the question.

How real is the error risk?

Spreadsheet errors are common, and the field evidence goes back decades. In 2000, Raymond Panko of the University of Hawaii reviewed the field audits of real company spreadsheets in a paper for the European Spreadsheet Risks Interest Group. Of the 54 spreadsheets audited from 1997 onwards, 91 per cent contained errors.

Read that with care. The audits defined an error differently (some counted only major errors, one counted totals that were off by 5 per cent or more) and 54 is a small sample, so it is a warning and not a forecast for your sheet. Panko’s wider point is that this is ordinary human error: people get roughly 2 to 5 per cent of the steps in a complex task wrong, and a spreadsheet is a great many steps.

A quick way to price the problem

Before choosing a fix, put a rough cost on the sheet. Ask each person who touches it how many hours a week go on re-keying figures, chasing the latest version and checking numbers they do not trust. Add those up, multiply by what an hour of that person’s time costs you, and multiply by 48 working weeks. That is what the sheet costs each year before any error reaches a customer. It will not be exact, but it gives you a figure to compare with the cost of each fix below, and it often settles whether the problem is worth solving at all.

Four fixes, cheapest first

1. Tidy the sheet

Often the sheet is fine and the habits around it are the problem. A day or two of tidying often fixes the worst of it:

  • One live copy in one shared place such as OneDrive or SharePoint, with everyone working in it and nobody keeping their own. This also gives you the change history described under sign four.
  • A named owner and a second person who can edit it. Put a one-page note on how it works on a tab inside the workbook, so it travels with the file.
  • One row per job or order. No merged cells, dates stored as dates, and dropdowns for any field with fixed choices, so “Complete”, “complete” and “Done” stop being three different statuses.
  • Locked formula cells, so a stray keystroke cannot overwrite a calculation.

This costs a day or two and no software. It deals with signs one, two and four and reduces five. It does not touch three or six.

2. Buy an off-the-shelf tool

If what you track is common (jobs, stock, bookings, customers), someone has built software for it, and it may well do the job better than a sheet can. The cost is not the subscription. It is moving the data and changing how people work. A useful test: if you would have to change how you operate to fit the tool, the tool is the wrong one.

Our build versus buy framework goes through the hidden costs on both sides. Its short version is to buy for the commodity parts of the business and build only where the work is genuinely yours.

3. Connect the tools you already have

Many firms already pay for the pieces: an accounts package, a job or project system, a mailbox, a CRM. What is missing is the link, so people copy numbers from one to the next. Connecting is often cheaper than replacing, because each system stays as it is and the data moves between them without anyone typing it.

Xero, for example, has its own app marketplace with sections for invoicing and jobs, inventory and time tracking, so check whether someone has already built the link before you commission one. Where nobody has, a small automation can do it. For a construction firm in Hertfordshire, we built an invoice dashboard linked to Xero: supplier bills are filed from the mailbox, matched to purchase orders and coded, instead of being keyed in by hand. Our automation work includes this kind of connecting.

4. Build a small custom tool

Sometimes the process is yours. How you quote, schedule or price is how you win work, and no product fits without heavy bending. Then a small, single-purpose tool that you own can replace the sheet: a form, a database and a few screens. For the same construction firm we built an online quoting tool.

Keep it small. One process, one tool. This is usually the most expensive of the four, and like the others it needs someone to look after it afterwards, so it earns its place only when the first three have not solved the problem.

A short way to decide

Tidy the sheet first whatever else you decide, because it costs little and shows you what you actually need. Then:

  1. Is the main pain re-keying between systems you already pay for? Connect them.
  2. Does software exist for this job that fits how you work with little bending? Buy it.
  3. Is the process your own, and part of how you win work? Build small.
  4. Is none of that true? Keep the sheet, now tidy, and revisit in a year.

A typical path

Take a job tracker. The sheet holds jobs, prices and dates, two people edit it, and the figures are typed into Xero when a job is invoiced. Step one is a single shared copy with dropdown statuses and a note on how it works. Step two is connecting it to Xero, so the invoice figures arrive without typing. If quoting is where the time still goes, step three is a small quoting tool. At the end the original sheet may still exist, doing far less and no longer holding the business up.

Where to start

If you are not sure which of the four applies, book a 30 minute call and bring the sheet with you. We will tell you plainly which fix we would choose, including when the answer is to tidy it and leave it alone.