A shared rota in Google Sheets™ that people can't break (free template)
Rotas are the most-shared and most-broken sheets in existence: many editors, weekly changes, and formulas sitting next to cells everyone types in. Five rules make a rota that survives contact with its users.
The five rules
- One row per shift, one column per fact. Date, person, role, start, end. No merged cells, no "see note below".
- Dropdowns for names and statuses. Data → Data validation with Reject the input, so "Sma" never becomes a new team member. Teach paste-values-only so pasting does not wipe the dropdowns.
- Lock the formula columns. Totals, hours and counts get Data → Protect sheets and ranges, keepers only.
- Filter views per person. Nobody sorts the shared view to find their own shifts: saved views do it without moving anyone else's rows.
- Freeze the header row (View → Freeze → 1 row) so the rota stays readable at week 40.
Start from a template
App Garden™ publishes free rota, sign-up sheet, stock list and tracker templates built on these rules: /shared-sheet-guardrails/rota. Make a copy and the guardrails come with it.
Guard your own rota
Shared Sheet Guardrails™ checks any Google Sheets™ rota for typed-over formulas, lost validation and the rest, and sets guardrails up in one go. Checking any sheet is free.
Install from the Google Workspace Marketplace™
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. App Garden™ is not affiliated with or endorsed by Google.