- Excel calculates and analyzes. A database adds enforced rules, linked records, safe editing by many people, permissions and a history of every change.
- The warning signs: several people editing one file, copies of copies, lookups that stitch tabs together, and numbers that change when nobody meant them to.
- Excel can quietly change values as you type or paste, such as part numbers and codes that look like dates. Newer versions let you switch some of this off.
- The options run from a better-structured workbook, to SharePoint lists or Airtable, to a real database with screens built for your process.
- Keep Excel for analysis. Connect it to the new system instead of banning it.
What a database does that a spreadsheet doesn't
A spreadsheet is a grid where any cell can hold anything. That freedom is why people love it, and why it struggles as a system of record. A database trades some freedom for guarantees.
| What you need | In a spreadsheet | In a database |
|---|---|---|
| Rules on data | Data validation helps, but it's easy to paste around | Rules are checked on every save, however the data arrives |
| Linked records | Lookups that copy values between tabs and break when rows move | One customer, many orders, linked by design |
| Many people editing | Co-authoring shows others' edits, but nothing stops conflicting changes to the same order | Changes are saved one at a time, with rules that prevent conflicts |
| Permissions | Mostly file-level: if you can open it, you can usually see all of it | Access by role, down to which records and fields each person sees |
| History | Versions of the whole file | Who changed which field, when, from what to what |
| Size | 1,048,576 rows by 16,384 columns per sheet, and slow well before that | Built to search and summarize millions of records quickly |
None of this makes Excel bad. It makes Excel the wrong shape for a shared record that the business runs on.
Signs Excel has become your database
- The file is the only place a piece of business data exists, like open orders, stock or job status.
- People ask "is anyone in the file?" before making changes.
- There are copies with names like "FINAL v3" and nobody is sure which is current.
- Lookups stitch tabs or files together to answer simple questions.
- Someone spends hours each week reconciling it against another system.
- Macros or formulas only one person understands.
- The file is emailed to people outside the team, or outside the company.
- When a number looks wrong, nobody can say who changed it or when.
One or two of these is normal. Three or more, on a file that drives money or customer commitments, means the spreadsheet has become a system without any of a system's protections.
The quiet ways Excel changes your data
Most spreadsheet errors come from people: a formula dragged one row too far, a paste that overwrote a formula. Those risks are well documented, and our guide to replacing spreadsheets covers the famous cases. Excel also changes some data on its own, which matters when a sheet holds codes rather than numbers.
By default, Excel can strip leading zeros, so part number 00123 becomes 123. It keeps only the first 15 digits of long numbers, which damages account and tracking numbers. And it turns some letter-and-number codes into dates, so a code like JAN1 becomes the first of January.
The best-known victim is genetics. A 2016 study in Genome Biology scanned leading journals and found that roughly one-fifth of papers with Excel gene lists in their supplementary files contained gene names that had been converted by mistake. In 2020 the committee that names human genes changed several gene symbols to stop it, renaming the SEPT genes to SEPTIN and the MARCH genes to MARCHF.
Microsoft has since added settings to turn off these conversions in Excel for Microsoft 365 and Excel 2024. That helps, but the setting lives in each person's copy of Excel, and Microsoft notes that entries with spaces or dashes, such as "JAN-1", may still become dates. A database avoids the problem by storing each field as a declared type: a part number column holds text, so it stays exactly as entered.
Your options, from smallest step to largest
A better workbook
Turn ranges into Excel tables, separate inputs from calculations, use dropdowns for status fields and lock formula cells. Pull data in with Power Query from the source system instead of pasting it. Name one owner for the structure. This is the right answer when one team owns the data and the stakes are modest.
SharePoint lists or Microsoft Lists
If you run on Microsoft 365, a list gives you rows with typed columns, forms, permissions and version history, often with no new license. Lists can hold up to 30 million items, but Microsoft applies a list view threshold of about 5,000 items to a single operation, so large lists need indexed columns and filtered views. They suit trackers and simple request logs better than processes with many linked records.
Airtable or Smartsheet
These add linked records, richer views and simple automations, and teams can set them up without developers. They work well for one team with a clear process. Our guide to Airtable and Smartsheet versus a custom tool covers where they fit and where their plan limits start to bite.
A real database with an app built for your process
A database such as PostgreSQL or SQL Server holds the data, with screens designed around how the work actually runs, rules checked on every save, permissions by role and a full change history. It connects to your accounting system, ERP or CRM so nobody retypes between them. This earns its cost when many people and roles share the data, money moves on it, or several systems need to agree. Low-code builders sit between this rung and the last; our low-code guide covers the trade-offs.
Questions that pick the right step
Five questions usually point to the right option:
- How many people edit it? One owner and a few readers can live in a good workbook. Several editors at once need something built for it.
- Do records link to each other? Customers with many orders, jobs with many visits. Linked records are where spreadsheets strain first.
- Who should see what? If some people shouldn't see costs, salaries or other customers' data, file-level sharing won't do.
- Does another system need this data? If someone retypes it into accounting or the ERP, the move should include a connection.
- What happens if it's wrong for a day? The higher the cost of a mistake, the more you need enforced rules and a change history.
Moving without breaking the month
A spreadsheet that runs the business can't be switched off on a Friday. A few principles make the move safe:
- Freeze the structure first. Agree what each column means before anything moves. This step alone usually finds surprises.
- Choose one home for each piece of data. If stock lives in the new system, it stops being edited in the sheet on a set date.
- Move the history you need. Archive the rest as a read-only file rather than cleaning a decade of records.
- Run side by side for one cycle. A week or a month-end close, depending on the work. Differences between old and new show you the rules nobody wrote down.
- Keep an export. People who analyze in Excel should still be able to, from a live connection or a clean download.
How long each step takes depends on how many people touch the data and how tangled the formulas are. That's worth finding out before you commit to a date.
What most guides miss
The spreadsheet is your specification. Formulas, color codes, hidden columns and comments hold business rules that exist nowhere else. Read them carefully before you design anything. A cell that turns red when margin drops below a threshold is a rule the new system needs.
Don't ban Excel. Analysts will keep using it, and they should. The goal is to stop Excel being the record, not to stop people using it. Give them a live connection so they stop keeping private copies.
The file's keeper is your most important ally. The person who built and maintains the spreadsheet knows every exception. They can also feel the project is a judgment on their work. Involve them early and give them a real role in the new system.
Cleaning the data is its own job. Duplicates, free-text fields and inconsistent names don't fix themselves on import. Budget time for it, or the new system starts life with the old system's mess.
How we do it
We start by reading the spreadsheet with the person who keeps it, so the new system captures the rules it holds. Then we pick the smallest step that solves the problem, whether that's a better workbook, a list or a custom tool connected to your other systems. We also stay on after launch to support and extend the system as your team and processes change. Tell us about the spreadsheet that runs your business and we'll tell you what it needs.
Sources
- Microsoft Support: Excel specifications and limits
- Microsoft Support: Set automatic data conversions
- Ziemann, Eren and El-Osta: Gene name errors are widespread in the scientific literature (Genome Biology, 2016)
- Tweedie et al.: Genenames.org, the HGNC and VGNC resources in 2021 (Nucleic Acids Research)
- Microsoft Support: List View Threshold for large lists and libraries
