When a Spreadsheet Has Become a Database and What to Do
How to tell that an Excel file now behaves as a database, choose SQLite, Access or PostgreSQL, design tables, migrate safely and keep Excel as a reader.
Nobody sets out to build a database in a spreadsheet. A person keeps a list. The list gets a second sheet for prices, a third for customers, a macro to email a report. Colleagues start adding rows. A finance process begins to read it. Years later, an important part of the business runs on a file with a name like master_v9_FINAL, maintained by two people who have never discussed what happens if either leaves.
This guide is for the moment someone notices. It covers how to tell whether a spreadsheet has become a database, what the real risks are, how to choose between SQLite, Access and PostgreSQL, how to design tables from a messy sheet, how to migrate without losing data and how to keep Excel in the picture. It is a decision guide first: sometimes the right answer is to fix the spreadsheet and leave it alone. The spokes linked throughout cover each step in detail.
What makes a spreadsheet a database
A database is not a product. It is a role: a shared, structured collection of records that people and programs rely on, that changes over time and that must stay consistent. A spreadsheet can play that role, and it often does it badly, because it was designed to calculate for one person.
Look at what the file does, not at what it is called. Does more than one person write to it? Do rows refer to other rows, by name or by lookup? Would anyone be harmed if it were wrong, lost or reverted last week? Do other systems, forms or reports read from it? If the answers are yes, it is a database in practice, and it has none of a database's protections. It has no transactions, so two saves can collide. It has no constraints, so anything can be typed anywhere. It has no permissions, so anyone with the file can see and change everything. It has no history, so nobody can say who changed what.
None of that matters for a personal budget. It matters a great deal for a customer list, an order book, an asset register or a price list.
The ten signs
Ten signs your Excel file needs to become a database lists them with a test for each. In short: several writers, lost edits, copies that multiply, rows referring to other rows, formulas that break on insert, a need for history, a slow file, other systems reading it, rules the sheet cannot enforce, and access that should differ by person.
One sign alone does not justify a migration. Three or more usually do, and the weight of each differs. A lost edit, a question from an auditor and a need to restrict who sees what are heavier than a slow file. A quick way to decide is to ask what the worst realistic failure would cost: a week of re-entered work, a wrong invoice, a breach of privacy. If the answer is large, move; if it is a nuisance, improve the spreadsheet.
Run the audit with the people who use the file. They know about the copies, the workarounds and the rows nobody trusts. The audit usually turns up more than the developer expected, and it produces the list of requirements for the replacement.
The problems that matter most
Two problems explain most of the harm.
Lost updates. Two people open the file, each changes something, the second save overwrites the first. Excel's locking on file shares prevents this by allowing one editor at a time, and co-authoring merges edits at cell level and fails to protect two people editing the same cell or related cells. Copies make it worse, since each copy becomes a divergent history (shared Excel files and the lost update problem). A database makes the conflict visible or impossible, with transactions and version checks.
No history. When someone asks who changed a value, why and from what, the spreadsheet usually cannot answer. Version history stores whole files, and Show Changes is limited in depth. An audit table written by triggers, or a temporal table, records every change with the user and time and can be queried (audit trails and history that Excel cannot give).
A third problem, the absence of rules, is quieter. A column of dates holds a date, a text note and a blank. A status column has eleven spellings. A price is negative. Each is a small error that costs time later. Constraints in a database prevent them at the door.
Choosing where the data should live
The target depends on a few facts about your situation, not on taste.
| Situation | Reasonable target |
|---|---|
| One writer, or a program that writes, a few readers | SQLite |
| A few Windows users who need forms and reports, no developer | Access, ideally with a split design |
| Several people write at once, or accounts and permissions matter | PostgreSQL or another server |
| Sensitive data, audit requirements | A server database with roles and an audit trail |
| A prototype that may grow | SQLite first, standard SQL, move later |
Access versus SQLite versus PostgreSQL for a small app compares them in detail. The operating question is who will keep the thing running. A PostgreSQL server needs installation, patches, backups and monitoring; a managed service removes most of that for a fee. SQLite needs a file and a backup. A database with no owner is worse than a spreadsheet with one, so settle ownership before the technology.
Remember what a database is missing: an interface. Excel gave people a grid, formulas and charts. A database gives them tables and SQL. Plan how people will see and change the data, because a migration that ignores this fails on usability even if the data is perfect. The usual answer combines a form or small application for entering data with Excel for reading it.
Understand and clean before you move
Most of the work of a migration is not the move. It is understanding what the sheet holds and getting it into a shape a database will accept. Skipping this step produces a database that reproduces the spreadsheet's problems.
Start by freezing a copy as a baseline. Then profile the data: row counts, blanks per column, distinct values, ranges. Find the key that identifies a record, if there is one, and check whether it is unique. Clean the structure (headers, merged cells, subtotal rows, blank rows), the types (numbers stored as text, dates in several formats) and the text (spaces, case, spelling variants) (cleaning Excel data before analysis is the full procedure). Keep the cleaning in a script so that it can be rerun on the final data at cutover.
Write down the rules the data should obey: required fields, allowed values, valid ranges, relationships. This is the specification for the constraints, and writing it exposes disagreements that nobody noticed, such as two departments defining "active customer" differently. Resolve them now, with the data's owners.
Design the tables
A sheet is a flat grid; a database is several tables with relationships. The design step turns the first into the second. Designing database tables from a messy Excel sheet gives the method with an example. The core moves are:
Name the things. A loans sheet describes borrowers, items and loans. Each becomes a table.
Choose keys. A surrogate id for each table, with natural keys such as an email or an asset tag as unique constraints.
Move repeated values out. The same address on two hundred rows becomes one row in a customers table.
Replace repeating columns with child tables.
Phone 1,Phone 2,Phone 3become a phones table with one row per number (turning repeating columns into a proper child table).Drop calculated columns. Totals, ages and days outstanding are computed by queries or views.
Add the rules.
NOT NULL,CHECK,REFERENCESandUNIQUEcarry the rules you wrote down.
Test the design by rebuilding the sheet with a join and by walking through the things people do: add, change, delete, look up. Show the tables to the users in plain terms ("a customer has many orders; an order has many lines") and let them correct you. The design is a model of the business, and they know the business.
Do not over-engineer. Aim for each fact stored once and each table about one thing, then stop.
The migration itself
A migration that loses nothing follows a sequence.
Stage. Load the sheet unchanged into a staging table of text columns. This is the raw evidence.
Clean in SQL. Derive clean data from staging with queries or views. Never edit the raw copy.
Load. Insert into the real tables in dependency order, in a transaction, with casts and constraints. A failure aborts and names the value.
Verify. Compare row counts and control totals with the workbook. Check orphans and foreign keys. Spot-check records by eye.
Secure. Create roles for the loader, the writers and the readers, with the minimum rights each needs.
Back up. Take a dump, store it elsewhere and test a restore.
Cut over. Freeze the workbook, load the final state, verify again and announce the date.
The two target-specific guides carry the commands: moving from Excel to SQLite for a small team and moving from Excel to PostgreSQL step by step. For the loading mechanics and the pitfalls with types and dates, see getting Excel data into a database and validating row counts after an Excel import.
Run the old and new in parallel for a cycle or two. Enter the same changes in both, compare outputs, and investigate every difference. Most differences turn out to be errors in the old workbook, and finding them builds trust in the new system. Keep the workbook as a read-only archive afterwards.
Keep Excel for what it is good at
The change should not feel like losing a tool. Excel remains an excellent reader and analysis environment. A connection to the database, through Get Data or ODBC with a read-only account, lets people refresh pivots and charts from live data. Point workbooks at views, so the shape they depend on is stable while the tables change.
Writing is the part to design carefully. Editing live tables from a worksheet bypasses the rules that justified the move. Safer paths are a form or small application that validates, a staging workbook that a script checks and loads, or procedures that apply the rules and write the audit record (keeping Excel as the front end to a database). With these in place, people keep the grid and the pivots, and the database keeps the data safe.
For the details of connecting, see connecting Excel directly to a database, and for read-only accounts giving Excel users a read-only database account.
Governance after the move
A database needs an owner. Name one person and a deputy, and write down who may change what. Agree a process for changes to the schema: add a column, rename a category, retire a status. Without it, people will ask for direct access and fix things by hand, and the database will drift into the state of the spreadsheet.
Document the model: a one-page description of each table and column, the relationships and the rules. Keep the schema in version control, with migrations as files, so every change is reviewed and reversible. Monitor the basics: backups succeeding, disk space, slow queries. Review access twice a year, and remove people who have left.
Plan for the question of history. If audit matters, build it now, because history that was not recorded cannot be recovered.
Costs and trade-offs, honestly
A migration costs time and attention, and it changes how people work. Entering data through a form is slower for some tasks than typing in a grid. People lose the freedom to put anything anywhere, which is the same as gaining consistency, and will feel like a loss. The first weeks bring requests for things the spreadsheet allowed. There is ongoing cost too: someone to run the server, to fix the form, to answer questions.
Against that, count what the spreadsheet costs now: the hours spent reconciling copies, the errors found late, the risk of a key person leaving, the audit that cannot be answered. For a file that matters, the comparison favors the database. For a file that does not, it does not. Be honest about which one you have.
A short case
A small distributor kept orders in one workbook edited by five people. Month-end took two days of reconciling copies. Three signs applied immediately: several writers, copies, and a finance report reading the file. The team ran the audit in an afternoon, found four entities in the sheet and chose PostgreSQL on a managed service, because five people wrote at once and the data was commercially sensitive.
Cleaning took longest: customer names in six spellings, dates in two formats, prices stored as text. The load itself ran in minutes. Counts matched and totals matched to the cent. A simple web form handled order entry, and Excel pivots on a read-only view replaced the month-end reconciliation. The audit table answered the first question from finance within a week. Two staff disliked the form at first. A month later, nobody wanted the workbook back.
The lesson is typical. The technology was the easy part. The cleaning, the design conversations and the entry form decided whether the change worked.
Planning the cutover week
The week you switch is where plans meet people, so plan it as a small project with named jobs. On the Friday before, announce the freeze: no more edits to the workbook after a stated hour. On the freeze, one person takes the final copy, and a second person checks that the file saved is the file loaded. The loader runs the same script that was rehearsed, against the final data, and produces the verification report. Nobody guesses; the report either matches or it does not.
If the numbers match, the database becomes the system of record from that moment. Tell people which door to use for changes: the form, the staging workbook or the person who owns the data. If something is urgent over the weekend, there must be a named person who can make a change by hand, with the change written down. If the numbers do not match, do not go live. Find the difference, which is usually a row the cleaning script treated differently from the original file, and rerun. A delay of a day is cheap compared with a month of wrong figures.
Keep the old workbook for a defined period, read-only, in a folder everyone can find. Put the date and the verification report beside it. When someone asks in six months why a figure differs from the old file, the answer is one document away.
Training and the first month
The technology change is small next to the habit change. People who typed anything anywhere now meet a form that refuses an empty required field, a list that does not contain their customer, a date that must look a certain way. Each refusal feels like friction on day one and prevents a clean-up on day sixty. Explain the reasons in one sentence each, and make the refusals say what to do next: "choose a department from the list; ask the data owner to add a new one".
Hold a short session with the heaviest users, and sit with them while they do real work. Watch where they hesitate. The places they hesitate are the places the form needs work. Collect requests in a list and handle them in batches, so that the first weeks produce improvements and not a stream of emergency changes.
Expect a few people to keep a private spreadsheet of the data. Do not fight it. Give them a way to refresh it from the database, so their copy is a view and not a rival, and over time most will stop needing it.
Measuring whether it worked
A migration is a project with outcomes, and you should check them. Pick a handful of measures before you start and look at them after a month and after a quarter.
Time spent on reconciliation: if month-end took two days and now takes an hour, say so. Errors found after publication: count wrong invoices, corrected reports and disputes before and after. Questions answered from history: the first time someone asks who changed a value and the answer arrives in a minute, note it. Incidents: lost edits, data entered in the wrong place, copies discovered. Satisfaction of the people who use it, in a sentence or two each.
Measures also protect the project. If a manager asks whether the change was worth it, you have numbers. If something is not improving, you find out early and fix the form, the training or the rules, instead of letting a quiet return to private spreadsheets undo the work.
Mistakes seen again and again
A few errors repeat across migrations. Copying the sheet's layout into one big table. The database then has the same duplication and the same inconsistency, with a server on top. Skipping the clean-up. Dirty text and mixed types go in, constraints fail or are dropped, and the database fills with the same mess. Loosening constraints to make the load pass. A constraint that rejects a row has found a real problem; the fix is in the data. Giving everyone write access to everything. The convenience recreates the free-for-all. Forgetting the front end. A database nobody can comfortably use loses to the spreadsheet it replaced. Moving without a rollback plan. Keep the workbook and a dump of the first load until the new system has survived a full cycle. No owner. The database drifts, and a year later it needs another rescue.
Naming these in advance makes them easier to avoid. Write the list on the project page and revisit it at each stage.
Questions to ask the data owners
Before design starts, sit with the people who own the data and ask questions that the sheet cannot answer. What is a customer, and is a customer who changes name the same customer? What counts as an order: a quote, a confirmed purchase, a shipped one? When a price changes, do old orders keep the old price? What should happen to a record that is no longer used: stay, archive or be deleted? Who may see the margin columns? How long must records be kept, and does any law require it?
The answers decide the schema. If old orders keep their old price, the line table needs its own price column. If records are never deleted, you want a status or a soft delete, and no cascade. If margin is sensitive, it moves to a table with different permissions. These are business rules, and a developer who guesses them will be wrong in a way that costs money later.
Write the answers down in a page, with the names of the people who gave them and the date. The page becomes the first draft of the data dictionary, and it is the thing to consult when a dispute arises about what a field was meant to hold.
Reporting during and after the move
Reports are the first thing people notice when a data source changes. List the reports and workbooks that read the old file before the cutover, and for each decide whether it is rebuilt on the database, retired or left alone. Rebuild the important ones first and compare their figures with the old versions, line by line, for at least one full period.
Where a report depends on the spreadsheet's quirks, such as a total that has always excluded a certain region by habit, the rebuild will show the difference. Treat each difference as a question for the report's owner: was the quirk intended? Some were policy, and others were mistakes that nobody caught. Capture the answer as a rule in the view that feeds the report.
When the spreadsheet should stay as it is
It is one person's tool. A personal tracker, a model or a budget has no need of a server.
It is a calculation. Scenarios, forecasts and what-if analysis are what spreadsheets are for. Move the inputs to a database if they are shared; keep the model in Excel.
The data is small, stable and owned. A reference list of twenty codes maintained by one person does not need migrating.
Nobody can run a database. Choose a managed service or a simpler option before choosing nothing, but do not install a server that no one will maintain.
The real problem is process. Sometimes the sheet is fine and the trouble is that nobody owns it. Appoint an owner, remove the copies, add validation, and see whether the problem remains.
Where to go next
Start with ten signs your Excel file needs to become a database to run the audit. Read Access versus SQLite versus PostgreSQL for a small app to choose. Use designing database tables from a messy Excel sheet for the model. Then follow the move guide for your target, and read audit trails and history that Excel cannot give before you go live if history matters. For the Excel side once the data has moved, connecting Excel directly to a database is the next guide.
The references are SQLite's page on appropriate uses, the PostgreSQL documentation, Microsoft's Excel specifications and limits and its guide to database normalization. To convert a small extract to JSON, use the CSV to JSON converter.
