Short version: spreadsheets fail in six predictable ways when they run a workforce — broken formulas, duplicate records, competing versions, stale data, open editing and no history. Good habits reduce each one, and they’re worth building. But habits scale with one person’s attention, and past a certain number of workers and sites the durable fix is removing the mechanism behind the error, not asking someone to catch it.
This guide covers where spreadsheet errors come from in rosters, ticket tracking and timesheets, the practical controls that genuinely prevent them, and where those controls run out. If you’re weighing whether to move off spreadsheets and group chats altogether, our comparison of spreadsheets, group chats and whiteboards versus workforce software covers that decision in full.
Why spreadsheets break so easily
Spreadsheets are flexible by design, which is exactly why they break. Nothing stops text landing in a date column, nothing guarantees two people are editing the same version, and nothing tells you whether today’s number is right or just plausible. That flexibility is great for a one-off analysis. It’s a liability for a record several people rely on every day to know who’s compliant, who’s rostered and what’s been worked.
The six failure modes
1. Formula errors
A formula that worked when the sheet was small breaks when a row is inserted above it, a column moves, or someone copies a cell without checking what it references. The output still looks like a number — it just isn’t the right one, and nothing on screen tells you.
2. Duplicate and inconsistent records
The same worker entered twice with slightly different details; one row with an old phone number and another with the current one; a ticket expiry recorded in one tab and a different date for the same ticket in another. After a few people have edited a sheet over a few months, there’s rarely a clean way to tell which entry is current.
3. Version conflicts
Two people edit separate copies of what’s meant to be the same roster, and whichever is saved last silently overwrites the other. No merge, no warning, no record of what was lost. An updated availability or a corrected ticket date entered that morning can simply vanish.
4. Missing or stale information
A cell that was never filled in, or a value that was right when typed and has since gone stale — an expiry date now in the past, an availability note from three weeks ago. A spreadsheet has no idea something used to be true. It holds whatever was typed until someone notices.
5. No access control
Anyone with the file open can usually edit anything in it. Unless individual cells are protected — which most day-to-day sheets aren’t — there’s nothing stopping someone altering a compliance record they shouldn’t touch.
6. No history
When a dispute comes up — who worked this shift, why was this worker sent to that site, when did this date change — a spreadsheet shows the current state and nothing else. Reconstructing a decision after the fact usually means guessing.
How those errors become compliance breaches
None of this stays theoretical in a business that runs people. A formula error double-books a shift or leaves it empty. A version conflict overwrites an updated ticket expiry with an older copy, so a worker whose licence has lapsed still shows as current. A stale cell lets an expired credential sit unnoticed until the worker is already on site. In every case the spreadsheet holds the wrong information as confidently as it would hold the right information.
How to prevent spreadsheet errors: the controls that work
“Be more careful” is true and not much use. These controls build prevention into the sheet instead of relying on someone to remember.
- Use data validation. Force dates to be dates, and use dropdown lists for site names, ticket types and roles instead of free text. It won’t catch everything, but it stops a typo turning a date into text, or one site being spelled three ways.
- Lock your formulas. Once a calculation works, protect those cells so nobody types over them or drags a row out of place.
- Keep one master copy. Nominate a single file everyone edits. The moment two versions exist, “current” becomes a guess.
- Build a daily check. Five minutes at the start or end of each day scanning for blank cells, odd dates and entries that don’t match what you expect catches a surprising amount while it’s still a quick fix.
- Keep a change log. A changelog tab, a cell comment, or a dated backup before major edits gives you at least a trail to follow when something looks wrong.
Three checks that matter most for compliance
- Compare expiry dates against today, not memory. A ticket that was current last time anyone looked isn’t necessarily current now.
- Reconcile the roster against actual attendance regularly — not just when a client asks. A roster says who was meant to be there; it doesn’t prove who was.
- Search before you add a new worker or site. Duplicates usually arrive one entry at a time under a slightly different spelling.
Where manual controls hit their limit
All of the above is worth doing. It’s also honest to say where it stops being enough.
Manual checks scale with attention, not with the business. A five-minute daily scan works for thirty workers on two sites. Add a new client with forty casuals across three more sites and the same scan needs to become forty minutes to cover the same ground with the same care.
They depend on one person. A prevention system that lives in one coordinator’s discipline stops quietly when that person is sick, on leave or having a flat-out week — and nobody necessarily notices.
They only catch what someone thinks to look for. A structural fix — one record per worker, no second copy — removes the error mode instead of relying on someone spotting it afterwards.
| Failure mode | Manual control | Structural fix |
|---|---|---|
| Formula errors | Lock formula cells | Calculations run by the system, not rebuilt in cells |
| Duplicates | Search before adding | One record per worker that everything reads from |
| Version conflicts | One master file | A single live record, no second copy to conflict with |
| Stale expiry dates | Daily check against today | Expiry status recalculated automatically, with reminders |
| Open editing | Protect cells | Role-based access for office, supervisors and workers |
| No history | Changelog tab, dated backups | Key actions logged as they happen |
Where OnCrew removes the error mode
For rosters, ticket tracking and timesheets specifically, OnCrew replaces the spreadsheet mechanisms behind these errors rather than putting a nicer screen on the same manual process.
- One record per worker. Tickets, licences and inductions sit on the worker’s profile, entered once — often by the worker themselves through phone onboarding — so there’s no second row to drift out of date.
- Expiry watched for you. Each credential’s status is recalculated from its expiry date automatically, and workers can be texted before a ticket lapses. See compliance and ticket tracking.
- A gate instead of a cell. Each site’s required tickets can be set to block allocation, so a worker whose required ticket is missing or expired can’t be placed there — the stale-entry failure can’t let them through.
- One live roster. Shifts and assignments run from a single shared record, so there’s no copy to overwrite another.
- Hours captured, not typed. Clock-ins are checked against the site’s location and recorded to the minute, so the roster can be checked against what actually happened.
- A history of key actions. Approvals, assignments and compliance changes are logged, so “what changed and when” has an answer.
The manual habits above don’t become worthless once you use a system like this — they’re still good practice for whatever still lives in a spreadsheet. The difference is that the structural fixes remove whole categories of error rather than asking someone to catch them.
