They are load-bearing more often than admitted
Plenty of profitable businesses run a core process in a shared workbook. Automating around it — reading from it, writing to it, validating it — is frequently more useful than replacing it.
That approach requires nobody to change how they work, which is why it is adopted where a replacement would not be.
What makes them awkward
- Merged cells that break row alignment
- Several tables on one sheet with blank rows between
- Headers repeated partway down
- Numbers stored as text, or with stray characters
- Dates in several formats within one column
Code that assumes a clean rectangular table with one header row will work on the sample file and fail on the real one. Every time.
Validate as you read
- Check the expected columns exist, by name rather than position
- Check types — a number column containing text is a signal
- Check ranges — dates and amounts within plausible bounds
- Check the row count against expectation
- Report anything unexpected rather than processing it
Writing back
Writing to a workbook people also edit is where problems arise: someone has it open, formulas get overwritten, formatting is lost.
Prefer writing to a separate output file, or to a dedicated sheet nobody edits by hand. Overwriting a workbook someone is working in is a good way to lose a morning's work.
When to move on from spreadsheets
| Signal | Meaning |
|---|---|
| Several people editing simultaneously | You need a database |
| Versions with different totals | No single source of truth |
| Formulas nobody understands | Accumulated risk |
| Over about 20,000 rows | Performance and errors will rise |
| Everyone sees everything | A permissions problem |
Two or more of those and a proper system is worth pricing. Fewer, and automating around the spreadsheet is the cheaper answer.