When Has Your Business Outgrown Spreadsheets?

When Has Your Business Outgrown Spreadsheets?

Your business has outgrown spreadsheets when a file has turned into a shared system that several people depend on, rather than a tool one person uses to calculate something. Typical signs: several versions of the same file, the same data typed into two places, people waiting for a colleague to close the file, no record of who changed what, and staff or customers who should see only part of the data. One of these on its own can often be fixed inside the spreadsheet. Several at once usually mean it is time for a database with a proper interface – an off-the-shelf product or custom web-based software.

What spreadsheets do well

Spreadsheets are not the problem. They are flexible, nearly everyone can use them, and they cost little or nothing. For calculations, one-off analysis, budgets, price lists kept by one person, or trying out a new process before you know its final shape, a spreadsheet is often the right tool.

Size is rarely the first limit. Microsoft documents that an Excel worksheet holds 1,048,576 rows by 16,384 columns, and Google allows up to 20 million cells in a Google Sheets file. Most small businesses never come close. The trouble starts earlier, when a spreadsheet stops being a tool and becomes the system that daily work runs on.

Ten signs you have outgrown them

  1. Several versions of the truth. "Orders final", "Orders final 2" and a copy on someone's laptop. Nobody is sure which is current.
  2. The same data typed twice. An order goes into the spreadsheet, then into the invoicing software, then into an email to the warehouse. Every retyping is a chance for a mistake.
  3. Waiting and overwriting. People wait for a colleague to close the file, or two people edit a copy at the same time and one person's changes are lost.
  4. Nobody knows who changed what. A price or a delivery date has changed, and you cannot tell who changed it, when or why.
  5. Some people should see only part of the data. A salesperson should see only their own clients, a partner only their own orders. Spreadsheets handle this poorly. Microsoft's own documentation says worksheet protection "isn't intended as a security feature"; it only prevents changes to locked cells.
  6. Hidden errors. A formula copied one row short, a number typed over a formula. Raymond Panko's review of fifteen years of research reports that studies "have concluded unanimously that spreadsheet errors are both common and non-trivial". The more formulas and editors a file has, the more places such an error can hide.
  7. The file depends on one person. Only one colleague understands the macros or the structure, and work slows down when they are away.
  8. Customers or partners need access. You send them extracts by email and then copy their replies back in by hand.
  9. Personal data is involved. The European Data Protection Board's guide for small businesses says users "should only have access to data on the basis of the needs to know", recommends a unique identifier for each user and advises against accounts shared by several people. A customer list in a file everyone can open and copy makes that hard to achieve.
  10. Reporting takes days. Every month someone merges files and repairs formulas before anyone sees the numbers.

Cheaper fixes to try first

If you recognise only one or two of these signs, improve the spreadsheet before you replace it:

  • keep one file in a shared cloud location instead of copies sent by email, and use its version history;
  • give each kind of record its own sheet, with one row per record and one column per field – customers, orders and products separately;
  • use data validation and drop-down lists so people choose values instead of typing them;
  • lock formula cells, knowing that this protects against accidents, not against people;
  • name one owner of the structure, who decides when a new column is added.

If the process you run is a common one – invoicing, a sales pipeline, bookings, stock – an off-the-shelf product may be enough and is often the cheaper start. Custom software is worth considering when your process is specific, when you would need several products glued together, or when customers and partners need their own access.

What replacing a spreadsheet actually gives you

The replacement is usually a web application with a database behind it. Compared with a shared file, it gives you:

  • one copy of the data that many people can edit at the same time without overwriting each other;
  • personal accounts and roles, with each user seeing only what they need;
  • a change log showing who changed what and when;
  • rules that stop invalid data at entry, such as a missing VAT number or a delivery date in the past;
  • automatic connections to other systems instead of retyping – the website, invoicing, couriers;
  • reports that are always up to date;
  • export to Excel for anyone who still wants to analyse the data in a spreadsheet.

Data that already exists in electronic form should be imported, not retyped. On our product search engine gega.bg, about 2.7 million products come in from affiliate feeds rather than manual entry. That is a much larger scale than a typical business, but the principle is the same: if a supplier or another system can deliver the data, connect to it. We describe that project in how a product search engine with millions of products works.

How to move without disrupting work

  1. List the files. Note every spreadsheet involved, who edits it, who reads it and which decisions depend on it.
  2. Find the real records. Behind most spreadsheets are a few kinds of records – customers, orders, products, jobs – and the links between them. These become the structure of the database.
  3. Write down the rules. Formulas and unwritten habits such as "new customers pay before we ship" are your business logic. They have to be captured, not lost in the move.
  4. Clean the data. Duplicates, inconsistent spellings and empty fields must be resolved before or during the import.
  5. Start with one process. Replace the spreadsheet that causes the most trouble first, not everything at once.
  6. Run both briefly. For a short period, compare results between the old file and the new system. Then make the file read-only, so nobody keeps updating it out of habit.

A quick self-check

QuestionA spreadsheet is fineTime to consider software
How many people edit the data?One or twoSeveral, often at the same time
Is the same data typed in more than one place?NoYes, regularly
Should some users see only part of it?NoYes – staff, partners or customers
Do you need to know who changed what?RarelyYes, for money, stock or deadlines
Does it contain personal data?Little or noneCustomer or employee records
What happens if the file breaks?An annoyanceWork stops

If most of your answers are in the middle column, keep the spreadsheet and make it tidier. If several are in the right column, it is worth getting a proposal – and comparing it with an existing product before deciding.

We build web-based software that takes over from shared spreadsheets: a database with an admin panel, user roles, imports and exports, and connections to your other systems. If your process is standard and an existing product would do the job, we will tell you. Contact us with a short description of the files you work with today.

Sources

← All articles

Have an idea for a website, app or system?

Tell us briefly what you need – we will get back to you for a short call and a clear quote.