Using Excel as a database: where it works, where it breaks

When Excel works fine as a database

• One person, or one person at a time, maintains it.
• Each kind of record lives in one Excel Table (Home → Format as Table): one row per record, one header row, no blank rows, no merged cells.
• Lists that should only hold certain values use Data Validation.
• It is tens of thousands of rows, not millions, and lookups are XLOOKUP or a PivotTable away.

If that's you, the most useful change is usually structural, not a new tool: convert ranges to Tables, add validation, and keep one record per row.

Five signs it has stopped working

1. Several people edit it at once

Co-authoring helps, but a spreadsheet has no record locking and no real permissions per row. Conflicting edits and "who changed this" questions are the first sign. See what Excel's change tracking does and doesn't record.

2. Related lists are kept in step by copy and paste

Customers on one sheet, orders on another, the customer's name typed again on every order. When a name changes in one place and not the other, the data has drifted. Relationships are what databases are for.

3. It is getting big

Recalculation slows long before the hard limit of 1,048,576 rows per sheet — what the limit is and what happens past it.

4. You need to know what it said last month

Each month's file overwrites or replaces the last. Answering "what was the balance in March" means finding March's file, if anyone kept it.

5. Bad values get in

Validation doesn't stop a pasted range. A number stored as text, a date in the wrong format or a duplicated ID goes in silently and shows up later as a wrong total.

Where to go, by what broke

Moving a sheet into SQL? Our free Excel to SQL tool turns a sheet into CREATE TABLE and INSERT statements for PostgreSQL, MySQL, SQLite or SQL Server, in the browser.

When the "database" is really a pile of monthly files

Many spreadsheets that get called a database are something else: every month a bank export, a sales report per store or a timesheet arrives, gets checked, matched or combined, and summarized for someone. The workbook grows because every month is pasted under the last. A database would hold the rows, but it wouldn't do the monthly checking and matching, and someone would have to build that.

TableDI 2 is a desktop app for file work you redo every period: it keeps your sources, rules and delivery as a job, so next month you drop in the new files and run it again. Everything runs on your own machine.

Your source files stay as they are. Each period, a preflight checks the new files against last period's — renamed or missing columns, duplicate or blank keys, the row count — before the same rules run. Past Excel's 1,048,576-row limit, filtering and export still work, on the free edition too. In the lifetime edition, History keeps every row of every period with the file and rules version it came from, so "what was it in March" is a filter, not a search through old files.

It is not a database: there is no data entry, no forms, no SQL, no sharing with other people. If your problem is signs 1 or 2, look at the first two rows of the table above.