Ten Signs Your Excel File Needs to Become a Database
Ten concrete signs that an Excel file has become a database, from several editors to lost updates and broken lookups, with a quick test for each.
Every database that matters began as a spreadsheet nobody planned to keep. Someone started a list of customers, orders, equipment or tasks. It grew a second sheet, then a third, then a macro. Now six people edit it, a finance report depends on it, and nobody is sure which copy is current. At no point did anyone decide that it was a database, and at no point was it designed as one. This post lists ten signs that the file has become one, how to test for each in a few minutes, and what to do first. It is a spoke of when a spreadsheet has become a database.
The signs
1. More than one person writes to it
One editor is a document. Several editors is a shared database. The symptom is the question "who has the file open", a lock message when you try to save, or a habit of asking colleagues to stay out while you work. The test is to ask how many people changed it last month. If the answer is more than two, the file is already behaving as a multi-user system without the protections of one. See shared Excel files and the lost update problem.
2. Edits get lost
Two people open the file, both change a row, both save, and one change vanishes. Or someone restores an older copy and a week of work disappears. The test is simple: find one case in the last three months where someone said "I already entered that". One case is enough. A database makes a lost update impossible or visible; a file makes it silent.
3. Copies multiply
orders.xlsx, orders_final.xlsx, orders_final_v2.xlsx, orders_JM_edits.xlsx. Copies exist because people are afraid to edit the real one, or because editing it blocks others. The test is to count the files in the folder with the name in them. More than three means there is no single source of truth, and every report built on one of them is a guess about which is right.
4. Rows refer to other rows
A column of customer names that repeats a customer in two hundred rows, with the address copied into each, has a relationship that the sheet does not know about. Change the address in one row and the others disagree. A lookup formula pulling a price from another sheet is the same relationship in disguise. The test is to look for repeated groups of values that describe the same real-world thing. Repeats are a sign that a second table wants to exist (designing tables from a messy Excel sheet).
5. Formulas break when you insert a row
Formulas with hard-coded ranges, VLOOKUP over A2:F500 and a total that stops at row 500, mean the structure is fragile and depends on layout. The test is to insert a row in the middle of the data and see which numbers change. In a database the structure does not depend on where a row sits.
6. History matters
Someone asks who changed a price on the third of March, what the old value was and why. A spreadsheet can answer only if somebody wrote it down. Version history on SharePoint shows whole-file versions, not cell-level reasons. If a regulator, an auditor or a customer may ask such questions, you need an audit trail (audit trails and history that Excel cannot give).
7. The file is slow
Opening takes a minute, saving takes longer, filters pause, and the file is tens of megabytes. The test is the open time with calculation set to manual: if it is still slow, the size is the issue, not the formulas. Files that large carry data a database holds more cheaply (Excel files that are too big or too slow).
8. Other things read from it
A website form, a report, another team's workbook, a script that reads the file every night. Once other systems depend on the file, its layout becomes an interface. Rename a column and something breaks elsewhere, with no warning. The test is to ask who reads it. If the list includes programs, the file is a data source and should have a contract, which a database provides in the form of a schema.
9. Data needs rules
Prices must be positive. Every order needs a customer. Status is one of five values. In a spreadsheet, rules are conventions that data validation partly enforces and anyone can bypass by pasting. The test is to filter each column for values outside the allowed ones. If you find some, the sheet cannot hold the rules by itself. A database enforces them with constraints.
10. Access should differ by person
Some people should see every row and others only their region. Some may edit prices and others only add notes. The test is to ask whether anyone has been shown data they should not have. Spreadsheet protection is a guard rail, not access control, and splitting the file into per-person copies loses the single source (protecting a generated workbook from accidental edits).
How to score it
Count the signs that apply. A rough guide:
| Signs | What it means |
|---|---|
| 0 to 2 | A spreadsheet. Fix the specific problem and carry on. |
| 3 to 5 | A database in disguise. Plan the move, and start by cleaning and documenting the data. |
| 6 or more | Overdue. The risks already exist; the question is how soon. |
The signs are not equally heavy. Lost edits, a regulator's questions and a need for access control carry more weight than a slow file. Weigh them by what an error would cost, not by count alone.
What the first step is
Do not start by choosing a database. Start by understanding the data, because most of the work of moving is clean-up.
Freeze a copy as the baseline and note the date.
List the entities. What are the things in the sheet: customers, orders, products? One sheet often holds three or four.
Find the keys. What uniquely identifies a customer? Is there an id, or only a name that varies in spelling?
Clean. Trim, normalize, fix types, remove duplicates (cleaning Excel data before analysis).
Write down the rules. Allowed values, required fields, valid ranges.
Choose the target. For a small team, SQLite may be enough, and for several writers a server such as PostgreSQL (Access versus SQLite versus PostgreSQL for a small app).
Steps 2 to 5 are the same whatever the target is, and the output of them, a documented, clean data set, has value even if you decide to stay in Excel.
Keep Excel in the picture
Moving the data does not mean taking away the tool people know. A database can feed Excel through a connection, with pivots and charts that refresh. Staff who like the grid can keep using it to read and analyze. The change is where the data lives and who may write to it, not what people look at (keeping Excel as the front end to a database). Presenting the move that way avoids most of the resistance.
A quick audit you can run today
Open the file and answer these in ten minutes. How many sheets hold raw data and how many hold copies of it? How many columns repeat information that could be looked up? How many formulas refer to a fixed range ending before the end of the data? Filter each text column for variant spellings of the same thing. Check the last modified date and user in the file properties. Ask the three heaviest users what they would lose if the file vanished. The answers show how much of the file is data and how much is process, and they feed straight into the plan.
When the spreadsheet should stay a spreadsheet
One person, one purpose. A personal budget with ten rows does not need a server.
It is a calculation, not a record. A model with assumptions and scenarios is what Excel is for. The inputs may belong in a database, but the model belongs in the sheet.
The data is small and changes rarely. A lookup list of twenty codes is fine in a sheet if one person owns it.
There is no one to run a database. A database with no owner is worse than a spreadsheet with one. If you cannot name who will keep it running, start with a managed service or a simpler file-based option.
Microsoft's page on Excel specifications and limits lists what a worksheet can hold, and SQLite's guide on appropriate uses explains where a file database is enough. To inspect a small extract as JSON, use the CSV to JSON converter.
