A spreadsheet is a grid that anyone with the file can edit freely. A database stores structured records, with rules about what each record may contain, relationships between one list and another, and many people working on the data at once. It is time to move when a spreadsheet has stopped being somewhere you work things out and become the place where the business keeps its records.
The real difference between a spreadsheet and a database
The strength of a spreadsheet is that it has no rules. Any cell can hold a number, a date, a note or a formula, and you can add a column the moment you think of one. That freedom makes it very good for analysis and one-off modelling, and poor at keeping records consistent over years.
A database starts from the other end. You decide first what a record is: a customer, a job, a stock item. Each has named fields of a set type, and the database rejects anything that does not fit. A job has to belong to a customer that exists. The structure takes effort to design and to change, and that stiffness is what keeps ten thousand rows consistent when a dozen people are adding to them.
A database on its own has no screens. People reach it through something else: an application, a low-code tool or Excel itself.
Spreadsheet and database compared
| Spreadsheet | Database | |
|---|---|---|
| Number of users at once | A few, with care. Nothing governs who changes what. | Many. People work on individual records, not on one shared file. |
| Data rules and validation | Optional and easy to bypass. A formula can be overwritten with a typed number. | Enforced. Wrong types, missing values and duplicates are rejected. |
| Audit trail | Earlier versions of the whole file, at best. | Can record who changed each record and when, if the system is built to do so. |
| Relationships between lists | Held together by lookup formulas, which fail quietly when a name is retyped or a range is not extended. | Built in. A job is linked to its customer, and the link cannot be broken by accident. |
| Reporting | Very good for one-off analysis, charts and pivot tables. | Dependable for repeated reports. One-off analysis is often still done in Excel. |
| Cost and effort to set up | Almost none. | Design work first, plus something for people to use it through. |
| Who can change the structure | Anyone with the file, at any time. | Someone with the skills and the permission. |
Signs a spreadsheet has become a database in disguise
- Each row is a thing, not a calculation. A customer, an order, a job or a piece of equipment.
- Tabs depend on each other. Lookups pull customer or price details from one sheet into another.
- Meaning is carried by convention. An amber cell means a job is waiting for parts, and people have to remember that.
- Several people need it at the same moment. Someone is always waiting for a colleague to finish.
- There are copies. More than one file claims to be the current one.
If most of these describe your workbook, it is doing a database’s job, and our page on replacing spreadsheets looks at the business side of that decision. If one person uses the spreadsheet for analysis, leave it where it is.
The options for moving
Keep Excel as the front end for reports
The records move into a database such as SQL Server, and Excel connects to it to pull the data into pivot tables and charts.
People keep the analysis tool they know, and there is one master copy of the data. The limit is that this covers reading the data, not entering it. You still need screens for adding and editing records, so it is usually combined with one of the options below.
Use a low-code tool
Products such as Microsoft Power Apps or Airtable let a capable member of staff build forms and lists over shared data without much programming.
They are quick to start with and need no development project. Complicated pricing or scheduling rules are harder to express in them, and you remain dependent on whoever built it.
Microsoft Access is an older version of the same idea, with limits of its own that we describe under replacing an Access database.
Buy a packaged product
For common jobs such as customer records, quoting, job management and stock control, there are many products sold by subscription.
If one matches how you work, it is usually the cheapest route, and somebody else maintains it. The cost is fit: you adapt your process to the product. Test it against your awkward cases before committing, and check how you would get your data out again.
Have a small web application built
A bespoke application is built around your own rules, with a database underneath.
It reproduces what the spreadsheet does and adds what it lacks: individual logins, permissions, a record of changes and access from outside the office. It costs more at the start than the other routes, and it needs looking after once it is live.
The steps of a move
Whichever option you choose, the steps are much the same.
- List the sheets and what each row represents. For every tab, write one sentence, such as “each row is one job”. Tabs where you cannot do that are usually reports or workings, not data. Note what any macros do as well, because VBA macros tend to hold business rules.
- Clean the data. Look for duplicates, the same customer spelled several ways, dates typed as text and merged cells. Allow more time for this than seems necessary.
- Decide the rules. Which fields are required, which values are allowed and who may change what. In a spreadsheet these were habits. A database needs them stated.
- Import. Load the cleaned data and check it by comparing row counts, totals and a sample of records.
- Run in parallel. For a short period, put the same work through both and compare the results. A difference means a mistake in the new system or one that has sat unnoticed in the spreadsheet.
- Switch. Pick a date, do a final import and make the workbook read-only, so nobody carries on using it out of habit.
How we can help
CodeFirst builds new software at a fixed price, and our spreadsheet to web application package applies that to this kind of move. If you are not sure the workbook needs replacing at all, we are happy to talk it through.