Excel as a database: when the spreadsheet is no longer enough

Excel is the most widely used tool in any business. A report, a costing or a list for the next trade fair is done in no time. No request to IT, no vendor, no project.

The question of replacing it rarely comes up because Excel is bad. It comes up because the sheet has grown: the costing for one order became the order list of the whole sales team, one fitter’s schedule became the job planning for everyone. The spreadsheet has turned into a database without anyone deciding that it should.

What Excel is good at

• Calculating: costings, price lists, commissions
• Reporting: pulling figures from the ERP, sorting, filtering, charting them
• One-off lists: the visitors to an in-house trade fair, the addresses for a mailing
• Trying things out: testing an idea before anyone builds software for it

That is exactly what Excel was built for: one person, one file, one question.

Where the spreadsheet reaches its limits

The trouble starts when the one person becomes several and the question becomes a workflow.

• Several editors: two people open the file and one saves over the other. Or the file is locked, and a colleague waits.
• Versions: “Orders_final_v3_new.xlsx” on the server, a copy in a mailbox, another on a laptop. Which one is right?
• Permissions: whoever can open the file sees everything and can change everything - purchase prices and formulas included.
• History: nobody knows who changed the delivery date or what it said before.
• Formulas: the sheet calculates correctly as long as nobody deletes a row. What the formula in column AH does is known only to the person who wrote it - and they are on holiday.
• Links between sheets: customers, items and orders sit on separate tabs, joined by VLOOKUP. One typo in a customer name and the order is left hanging.

How to tell your list has become a system

• Several staff enter data regularly, not just one person.
• Other workflows depend on it: without the list nobody can write invoices or plan routes.
• New staff are shown how to use the list as part of their induction.
• The same data also sits in the ERP, the inventory system or the shop and is maintained there a second time.
• Somebody has started writing macros so the sheet can do more.
• If the file were damaged, a whole department would stand still.

If three of these apply, the list is no longer a list. It is the software of a department - just without the qualities software needs.

The difference at a glance

Three ways forward

Tidy up and keep it

Not every sheet that has grown needs replacing. Often it is enough to have one file instead of five copies, one storage location, input validation on the columns, sheet protection on the formulas and a tab explaining what the columns mean. If the file lives in Microsoft 365, several people can work in it at the same time and the version history shows who changed what. That is enough as long as the list stays a list.

Move it into a small application

Once you need several editors, permissions and a history, a small application is the more honest route: an input form instead of rows, required fields, a customer master that every order refers to, permissions per role, and every change logged. That need not be a big project. An application that replaces a spreadsheet usually has a handful of screens.

We build this kind of thing on ElbDesk, the foundation we build on: sign-in, permissions, collaboration and documents come with ElbDesk, and only the workflow hidden in your spreadsheet is built. If you have an ERP, it stays - accounts and warehouse stay out of it. If your list is in fact an Access database, the same applies: replacing an Access database.

Connect it to the ERP

Sometimes the spreadsheet contains nothing the ERP does not already know. It exists because the report in the ERP is awkward or because a field is missing there. Then the answer is not a new application but a report from the ERP or an extra field in it. And where the list holds data that is also maintained in the ERP, it is worth looking at double data entry - it is usually the real reason the list exists.

What becomes of the list

The old sheet is the best template for whatever replaces it. Its columns are the fields, its tabs are the areas, its colours are the statuses. Whoever builds an application takes it as the starting point and asks of every column whether it is still needed. The data is carried over; cleaning up duplicates and inconsistent spellings is usually the biggest part of the work. Whether ready-made software covers the workflow is best checked beforehand: custom or off-the-shelf software.

Questions before you decide

• How many people work in the list?
• Which workflows stop if the file is unavailable?
• Which of its data also sits in the ERP or the inventory system?
• Who understands the formulas, and is that written down?
• Does it need to be traceable who changed what?
• Is there off-the-shelf software that covers exactly this workflow?

The answers usually show quickly which of the three routes fits. And the list itself is the best starting point for the conversation - it shows how the department really works today.